[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
debug_info := 'Select from ap_invoice_distributions';
select count(1)
into dummy
from ap_invoice_distributions
where (invoice_id = X_INVOICE_ID AND
invoice_line_number = X_INVOICE_LINE_NUMBER AND
distribution_line_number = X_DISTRIBUTION_LINE_NUMBER)
and ((X_ROWID is null) or (rowid <> X_ROWID));
select decode(column_name, 'SEGMENT1', segment1,
'SEGMENT2', segment2,
'SEGMENT3', segment3,
'SEGMENT4', segment4,
'SEGMENT5', segment5,
'SEGMENT6', segment6,
'SEGMENT7', segment7,
'SEGMENT8', segment8,
'SEGMENT9', segment9,
'SEGMENT10', segment10,
'SEGMENT11', segment11,
'SEGMENT12', segment12,
'SEGMENT13', segment13,
'SEGMENT14', segment14,
'SEGMENT15', segment15,
'SEGMENT16', segment16,
'SEGMENT17', segment17,
'SEGMENT18', segment18,
'SEGMENT19', segment19,
'SEGMENT20', segment20,
'SEGMENT21', segment21,
'SEGMENT22', segment22,
'SEGMENT23', segment23,
'SEGMENT24', segment24,
'SEGMENT25', segment25,
'SEGMENT26', segment26,
'SEGMENT27', segment27,
'SEGMENT28', segment28,
'SEGMENT29', segment29,
'SEGMENT30', segment30)
into segment_val
from gl_code_combinations
where code_combination_id = X_CCID;
select unit_of_measure
into uom
from gl_stat_account_uom
where account_segment_value = segment_val
and chart_of_accounts_id = X_Ch_Of_Accts_Id ;
SELECT NVL(SLA_LEDGER_CASH_BASIS_FLAG, 'N')
INTO l_cash_basis
FROM ap_system_parameters_all ASP,
gl_sets_of_books SOB
WHERE asp.org_id = x_org_id
AND asp.set_of_books_id = sob.set_of_books_id;
PROCEDURE Select_Summary(X_Invoice_Id IN NUMBER,
X_Total IN OUT NOCOPY NUMBER,
X_Total_Rtot_DB IN OUT NOCOPY NUMBER,
X_LINE_NUMBER IN NUMBER, --Bug4539547
X_Calling_Sequence IN VARCHAR2)
IS
current_calling_sequence VARCHAR2(2000);
'AP_INVOICE_DISTRIBUTIONS_PKG.Select_Summary<-'||X_Calling_Sequence;
debug_info := 'Select from ap_invoice_distributions';
SELECT NVL(SUM(aid.amount), 0)
INTO X_Total
FROM ap_invoice_distributions_all aid,
ap_invoice_lines_all ail
WHERE ail.invoice_id = X_Invoice_Id
AND ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND ((aid.line_type_lookup_code NOT IN ('PREPAY', 'AWT', 'RETAINAGE')
AND aid.prepay_distribution_id IS NULL)
OR (ail.line_type_lookup_code = 'RETAINAGE RELEASE'
AND aid.line_type_lookup_code = 'RETAINAGE')
OR NVL(ail.invoice_includes_prepay_flag,'N') = 'Y');
SELECT NVL(SUM(aid.amount), 0)
INTO X_Total
FROM ap_invoice_distributions_all aid,
ap_invoice_lines_all ail
WHERE ail.invoice_id = X_Invoice_Id
AND ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND ail.line_number = X_LINE_NUMBER
-- bug 11851968 start
AND ((ail.line_type_lookup_code = 'RETAINAGE RELEASE'
AND aid.line_type_lookup_code = 'RETAINAGE')
OR (ail.line_type_lookup_code = 'PREPAY'
AND aid.prepay_distribution_id is not null)
OR (aid.line_type_lookup_code NOT IN ('PREPAY',
'RETAINAGE')));
END Select_Summary;
debug_info := 'Select from ap_invoice_distributions';
select decode(count(distinct(packet_id)),1,max(packet_id),'')
into X_Packet_Id
from ap_invoice_distributions
where invoice_id = X_Invoice_Id
and packet_id is not null;
debug_info := 'Select from ap_invoice_distributions';
select 'TRUE'
into dummy
from ap_invoice_distributions
where rowid = X_Rowid
and NVL(packet_id, -1) <> NVL(X_Packet_Id, -1);
debug_info := 'Select from sys.dual';
select 'There are encumbered dists'
into dummy
from sys.dual
where not exists (select 'There are other unencumbered dists'
from ap_invoice_distributions
where invoice_id = X_Invoice_Id
and NVL(match_status_flag, 'N') <> 'A'
and rowid <> X_Rowid);
SELECT 'Has posted payment with no corresponding void'
FROM ap_invoice_payments p1
WHERE p1.invoice_id = X_Invoice_Id
AND nvl(p1.cash_posted_flag, 'N') = 'Y'
AND NOT EXISTS (SELECT 'This is the void partner'
FROM ap_invoice_payments p2
WHERE p2.invoice_id = p1.invoice_id
AND p2.check_id = p1.check_id
AND p2.payment_num = p1.payment_num
AND p2.amount = ( -1 * p1.amount));
SELECT 'Has unposted payment that is linked to a voided check'
FROM ap_invoice_payments p, ap_checks c
WHERE p.invoice_id = X_Invoice_Id
AND nvl(p.cash_posted_flag,'N') <> 'Y'
AND p.check_id = c.check_id
AND c.void_date IS NOT NULL;
debug_info := 'Select accounting method from SOB';
SELECT NVL(sla_ledger_cash_basis_flag, 'N')
INTO l_cash_basis
FROM ap_system_parameters ASP,
gl_sets_of_books SOB,
ap_invoices AI
WHERE AI.invoice_id = x_invoice_id
AND AI.org_id = ASP.org_id
AND asp.set_of_books_id = sob.set_of_books_id;
debug_info := 'Select from ap_invoice_payments';
debug_info := 'Select from ap_invoice_payments and ap_checks';
l_debug_info := 'Call the PO_AP_INVOICE_MATCH_GRP to update the Po Distributions and Po Line Locations';
PO_AP_INVOICE_MATCH_GRP.Update_Document_Ap_Values(
P_Api_Version => 1.0,
P_Line_Loc_Changes_Rec => l_po_ap_line_loc_rec,
P_Dist_Changes_Rec => l_po_ap_dist_rec,
X_Return_Status => l_return_status,
X_Msg_Data => l_msg_data);
debug_info := 'Select from sys.dual';
select SUBSTRB(X_String, X_Start, X_End)
into result_str
from sys.dual;
FUNCTION Insert_From_Dist_Set(
X_batch_id IN NUMBER,
X_invoice_id IN NUMBER,
X_line_number IN NUMBER,
X_dist_tab IN AP_INVOICE_LINES_PKG.dist_tab_type,
X_Generate_Permanent IN VARCHAR2 DEFAULT 'N',
X_Debug_Info OUT NOCOPY VARCHAR2,
X_Debug_Context OUT NOCOPY VARCHAR2,
X_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
IS
l_distribution_class AP_INVOICE_DISTRIBUTIONS.DISTRIBUTION_CLASS%TYPE;
l_last_update_login
AP_INVOICE_DISTRIBUTIONS.LAST_UPDATE_LOGIN%TYPE;
l_dist_updated NUMBER;
'AP_INVOICE_DISTRIBUTIONS_PKG.insert_from_dist_set<-'||
X_calling_sequence;
SELECT line_type_lookup_code
INTO l_line_type_lookup_code
FROM ap_invoice_lines_all
WHERE invoice_id = X_invoice_id
AND line_number = X_line_number;
SELECT count(*)
INTO l_existing_distributions
FROM ap_invoice_distributions
WHERE invoice_id = X_invoice_id
AND invoice_line_number = X_line_number
AND rownum=1; --13507856 added the rownum condition to improve the performance
select org_id,set_of_books_id --added set_of_books_id for bug#10241241
into l_org_id,l_set_of_books_id
from ap_invoices
where invoice_id = X_invoice_id;
SELECT JG_ZZ_SHARED_PKG.GET_COUNTRY(l_org_id, null)
INTO l_country_code
FROM DUAL;
select chart_of_accounts_id
into l_chart_of_accounts_id
from gl_sets_of_books
where set_of_books_id = l_set_of_books_id;
l_dist_updated := 0;
SELECT ap_invoice_distributions_s.nextval INTO l_inv_dist_id FROM DUAL;
INSERT INTO ap_invoice_distributions (
batch_id,
invoice_id,
invoice_line_number,
invoice_distribution_id,
distribution_line_number,
line_type_lookup_code,
distribution_class,
description,
dist_match_type,
org_id,
dist_code_combination_id,
accounting_date,
period_name,
accrual_posted_flag,
cash_posted_flag,
amount_to_post,
base_amount_to_post,
posted_amount,
posted_base_amount,
posted_flag,
accounting_event_id,
upgrade_posted_amt,
upgrade_base_posted_amt,
set_of_books_id,
amount,
base_amount,
rounding_amt,
quantity_variance,
base_quantity_variance,
match_status_flag,
encumbered_flag,
packet_id,
reversal_flag,
parent_reversal_id,
cancellation_flag,
income_tax_region,
type_1099,
stat_amount,
charge_applicable_to_dist_id,
prepay_amount_remaining,
prepay_distribution_id,
parent_invoice_id,
corrected_invoice_dist_id,
corrected_quantity,
other_invoice_id,
po_distribution_id,
rcv_transaction_id,
unit_price,
matched_uom_lookup_code,
quantity_invoiced,
final_match_flag,
related_id,
assets_addition_flag,
assets_tracking_flag,
asset_book_type_code,
asset_category_id,
project_id,
task_id,
expenditure_type,
expenditure_item_date,
expenditure_organization_id,
project_accounting_context,
pa_quantity,
pa_addition_flag,
award_id,
gms_burdenable_raw_cost,
awt_flag,
awt_group_id,
awt_tax_rate_id,
awt_gross_amount,
awt_invoice_id,
awt_origin_group_id,
awt_invoice_payment_id,
awt_withheld_amt,
inventory_transfer_status,
reference_1,
reference_2,
receipt_verified_flag,
receipt_required_flag,
receipt_missing_flag,
justification,
expense_group,
start_expense_date,
end_expense_date,
receipt_currency_code,
receipt_conversion_rate,
receipt_currency_amount,
daily_amount,
web_parameter_id,
adjustment_reason,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
country_of_supply,
credit_card_trx_id,
company_prepaid_invoice_id,
cc_reversal_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
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,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
--ETAX: Invwkb
intended_use,
--Freight and Special Charges
rcv_charge_addition_flag,
---added for 7022001
pay_awt_group_id)
VALUES (
X_batch_id, -- batch_id
X_invoice_id, -- invoice_id
X_line_number, -- invoice_line_number
l_inv_dist_id, -- invoice_distribution_id
X_dist_tab(i).dist_line_num, -- distribution_line_number
NVL(l_line_type_lookup_code, 'ITEM'), -- line_type_lookup_code --Bug16324208
l_distribution_class, -- distribution_class
X_dist_tab(i).description, -- description
'NOT_MATCHED', -- dist_match_type
X_dist_tab(i).org_id, -- l_org_id
X_dist_tab(i).dist_ccid, -- dist_code_combination_id
X_dist_tab(i).accounting_date, -- accounting_date
X_dist_tab(i).period_name, -- period_name
'N', -- accrual_posted_flag
'N', -- cash_posted_flag
NULL, -- amount_to_post
NULL, -- base_amount_to_post
NULL, -- posted_amount
NULL, -- posted_base_amount
'N', -- posted_flag
NULL, -- accounting_event_id
NULL, -- upgrade_posted_amt
NULL, -- upgrade_base_posted_amt
X_dist_tab(i).set_of_books_id, -- set_of_books_id
X_dist_tab(i).amount, -- amount
X_dist_tab(i).base_amount, -- base_amount
X_dist_tab(i).rounding_amt, -- rounding_amt
NULL, -- quantity_variance
NULL, -- base_quantity_variance
--Invoice Lines: Distributions, changed match_status_flag
--to NULL from 'N'.
NULL, -- match_status_flag
'N', -- encumbered_flag
NULL, -- packet_id
'N', -- reversal_flag
NULL, -- parent_reversal_id
'N', -- cancellation_flag
X_dist_tab(i).income_tax_region, -- income_tax_region
X_dist_tab(i).type_1099, -- type_1099
NULL, --stat_amount
NULL, -- charge_applicable_to_dist_id
NULL, -- prepay_amount_remaining
NULL, -- prepay_distribution_id
NULL, -- parent_invoice_id
NULL, -- corrected_inv_dist_id
NULL, -- corrected_quantity
NULL, -- other_invoice_id
NULL, -- po_distribution_id
NULL, -- rcv_transaction_id
NULL, -- unit_price
NULL, -- matched_uom_lookup_code
NULL, -- quantity_invoiced
NULL, -- final_match_flag
NULL, -- related_id
'U', -- assets_addition_flag
X_dist_tab(i).assets_tracking_flag,-- assets_tracking_flag
X_dist_tab(i).asset_book_type_code,-- asset_book_type_code
X_dist_tab(i).asset_category_id, -- asset_category_id
X_dist_tab(i).project_id, -- project_id
X_dist_tab(i).task_id, -- task_id
X_dist_tab(i).expenditure_type,-- expenditure_type
X_dist_tab(i).expenditure_item_date, -- expenditure_item_date
X_dist_tab(i).expenditure_organization_id, -- expenditure_organization_id
X_dist_tab(i).project_accounting_context, --project_accounting_context
X_dist_tab(i).pa_quantity, -- pa_quantity
X_dist_tab(i).pa_addition_flag,-- pa_addition_flag
X_dist_tab(i).award_id, -- award_id
NULL, -- gms_burdenable_raw_cost
NULL, -- awt_flag
X_dist_tab(i).awt_group_id, -- awt_group_id
NULL, -- awt_tax_rate_id
NULL, -- awt_gross_amount
NULL, -- awt_invoice_id
NULL, -- awt_origin_group_id
NULL, -- awt_invoice_payment_id
NULL, -- awt_withheld_amt
'N', -- inventory_transfer_status
--Bug9296445
X_dist_tab(i).reference_1, --NULL, -- reference_1
X_dist_tab(i).reference_2, --NULL, -- reference_2
NULL, -- receipt_verified_flag
NULL, -- receipt_required_flag
NULL, -- receipt_missing_flag
NULL, -- justification
NULL, -- expense_group
NULL, -- start_expense_date
NULL, -- end_expense_date
NULL, -- receipt_currency_code
NULL, -- receipt_conversion_rate
NULL, -- receipt_currency_amount
NULL, -- daily_amount
NULL, -- web_parameter_id
NULL, -- adjustment_reason
NULL, -- merchant_document_number
NULL, -- merchant_name
NULL, -- merchant_reference
NULL, -- merchant_tax_reg_number
NULL, -- merchant_taxpayer_id
NULL, -- country_of_supply
NULL, -- credit_card_trx_id
NULL, -- company_prepaid_invoice_id
NULL, -- cc_reversal_flag
X_dist_tab(i).attribute_category, -- attribute_category
X_dist_tab(i).attribute1, -- attribute1
X_dist_tab(i).attribute2, -- attribute2
X_dist_tab(i).attribute3, -- attribute3
X_dist_tab(i).attribute4, -- attribute4
X_dist_tab(i).attribute5, -- attribute5
X_dist_tab(i).attribute6, -- attribute6
X_dist_tab(i).attribute7, -- attribute7
X_dist_tab(i).attribute8, -- attribute8
X_dist_tab(i).attribute9, -- attribute9
X_dist_tab(i).attribute10, -- attribute10
X_dist_tab(i).attribute11, -- attribute11
X_dist_tab(i).attribute12, -- attribute12
X_dist_tab(i).attribute13, -- attribute13
X_dist_tab(i).attribute14, -- attribute14
X_dist_tab(i).attribute15, -- attribute15
NULL, -- global_attribute_category
NULL, -- global_attribute1
NULL, -- global_attribute2
--bugfix:4674194
Decode(AP_EXTENDED_WITHHOLDING_PKG.AP_EXTENDED_WITHHOLDING_OPTION,
'Y',X_dist_tab(i).global_attribute3,''), --global_attribute3
NULL, -- global_attribute4
NULL, -- global_attribute5
NULL, -- global_attribute6
NULL, -- global_attribute7
NULL, -- global_attribute8
NULL, -- global_attribute9
NULL, -- global_attribute10
NULL, -- global_attribute11
NULL, -- global_attribute12
NULL, -- global_attribute13
NULL, -- global_attribute14
NULL, -- global_attribute15
NULL, -- global_attribute16
NULL, -- global_attribute17
NULL, -- global_attribute18
NULL, -- global_attribute19
NULL, -- global_attribute20
FND_GLOBAL.user_id, -- created_by
SYSDATE, -- creation_date
FND_GLOBAL.user_id, -- last_updated_by
SYSDATE, -- last_update_date
FND_GLOBAL.login_id, -- last_update_login
NULL, -- program_application_id
NULL, -- program_id
NULL, -- program_update_date
NULL, -- request_id
X_Dist_Tab(i).intended_use, -- intended_use
'N', -- rcv_charge_addition_flag
X_dist_tab(i).pay_awt_group_id -- added for pay_awt_group_id for 7022001
);
IF (l_dist_updated = 0 and
x_dist_tab(i).stat_amount is not null and
GET_UOM(X_dist_tab(i).dist_ccid,l_chart_of_accounts_id) is not null) THEN
Update ap_invoice_distributions
set stat_amount = X_dist_tab(i).stat_amount
where invoice_id = X_invoice_id
and invoice_line_number= X_line_number
and distribution_line_number = X_dist_tab(i).dist_line_num;
l_dist_updated := 1;
SELECT invoice_distribution_id
BULK COLLECT INTO l_dbi_key_value_list
FROM ap_invoice_distributions
WHERE invoice_id = X_invoice_id;
UPDATE AP_INVOICE_LINES
SET GENERATE_DISTS = 'D'
WHERE invoice_id = X_invoice_id
AND line_number = X_line_number;
END insert_from_dist_set;
PROCEDURE update_distributions (
X_invoice_id IN number,
X_line_number IN number,
X_type_1099 IN varchar2,
X_income_tax_region IN varchar2,
X_vendor_changed_flag IN OUT NOCOPY varchar2,
X_update_base IN OUT NOCOPY varchar2,
X_reset_match_status IN OUT NOCOPY varchar2,
X_update_occurred IN OUT NOCOPY varchar2,
X_calling_sequence IN varchar2)
IS
l_purch_encumbrance_flag varchar2(10);
l_last_update_login
ap_invoices.last_update_login%TYPE;
l_last_updated_by
ap_invoices.last_updated_by%TYPE;
l_last_update_date
ap_invoices.last_update_date%TYPE;
SELECT exchange_rate,
exchange_rate_type,
exchange_date,
AI.invoice_currency_code,
invoice_type_lookup_code,
invoice_date,
DECODE(x_line_number, null,
AI.last_update_login,
AIL.last_update_login ),
DECODE(x_line_number, null,
AI.last_updated_by,
AIL.last_updated_by ),
DECODE(x_line_number, null,
AI.last_update_date,
AIL.last_update_date ),
AIL.line_number,
AI.org_id
FROM ap_invoices AI,
ap_invoice_lines AIL
WHERE AI.invoice_id = X_invoice_id
AND AIL.invoice_id = AI.invoice_id
AND AIL.line_number = NVL(X_line_number, AIL.line_number);
SELECT AID.invoice_distribution_id,
AID.project_id,
AID.task_id,
AID.award_id, -- OGM_0.0 changes
AID.expenditure_item_date,
AID.expenditure_type,
PV.employee_id,
AID.pa_quantity,
AID.amount,
AID.base_amount,
AID.expenditure_organization_id,
AI.vendor_id,
AI.vendor_site_id,
AID.tax_recoverable_flag,
PD.line_location_id,
PD.accrue_on_receipt_flag,
AID.po_distribution_id,
AID.attribute_category,
AID.attribute1,
AID.attribute2,
AID.attribute3,
AID.attribute4,
AID.attribute5,
AID.attribute6,
AID.attribute7,
AID.attribute8,
AID.attribute9,
AID.attribute10,
AID.attribute11,
AID.attribute12,
AID.attribute13,
AID.attribute14,
AID.attribute15,
NVL(AID.posted_flag,'N'),
NVL(AID.reversal_flag,'N'),
AI.attribute_category,
AI.attribute1,
AI.attribute2,
AI.attribute3,
AI.attribute4,
AI.attribute5,
AI.attribute6,
AI.attribute7,
AI.attribute8,
AI.attribute9,
AI.attribute10,
AI.attribute11,
AI.attribute12,
AI.attribute13,
AI.attribute14,
AI.attribute15,
gsob.chart_of_accounts_id
FROM ap_invoice_distributions AID,
ap_invoices AI,
ap_suppliers PV,
po_distributions PD,
ap_system_parameters ap,
gl_sets_of_books gsob
WHERE AI.invoice_id = X_invoice_id
AND AID.invoice_id = AI.invoice_id
AND AID.invoice_line_number =
NVL(X_line_number, invoice_line_number)
AND AID.project_id IS NOT NULL
AND AI.vendor_id = PV.vendor_id
AND AID.po_distribution_id = PD.po_distribution_id (+)
AND AID.last_update_login = -3
AND ap.set_of_books_id = gsob.set_of_books_id
AND ap.set_of_books_id = AID.set_of_books_id
AND ap.org_id = ai.org_id;
'AP_INVOICE_DISTRIBUTIONS_PKG.update_distributions<-'||
X_calling_sequence;
| First, assume that an update to AP_INVOICE_DISTRIBUTIONS |
| will not occur. We will override the this default value |
| if an update does occur. |
+-----------------------------------------------------------------*/
X_update_occurred := 'N';
debug_info := 'select AP_INVOICES info';
| Fetch the invoice information that we need in order to update |
| the distribution base amounts |
+-----------------------------------------------------------------*/
OPEN invoice_line_cursor;
l_last_update_login,
l_last_updated_by,
l_last_update_date,
l_line_number,
l_org_id;
debug_info := 'Select from ap_system_parameters and
financials_system_parameters';
SELECT FSP.purch_encumbrance_flag,
SP.multi_currency_flag,
SP.base_currency_code
INTO l_purch_encumbrance_flag,
l_multi_currency_flag,
l_base_currency_code
FROM financials_system_parameters FSP,
ap_system_parameters SP
WHERE sp.org_id = l_org_id
AND sp.org_id = fsp.org_id;
debug_info := 'Update AP_INVOICE_DISTRIBUTIONS tax info';
| Update 1099 type and income tax region for each distribution |
| line if the vendor changed (test performed in PRE-UPDATE) |
+-----------------------------------------------------------------*/
IF (nvl(X_vendor_changed_flag,'N') = 'Y') THEN
UPDATE ap_invoice_distributions
SET type_1099 = X_type_1099,
income_tax_region = X_income_tax_region
WHERE invoice_id = X_invoice_id
AND invoice_line_number = NVL(X_line_number, invoice_line_number);
X_update_occurred := 'Y';
debug_info := 'Update AP_INVOICE_DISTRIBUTIONS match status';
UPDATE ap_invoice_distributions
SET match_status_flag = 'N'
WHERE invoice_id = X_invoice_id
AND invoice_line_number = NVL( x_line_number, invoice_line_number)
--Bug 5003892 AND l_purch_encumbrance_flag <> 'Y'
AND nvl(X_reset_match_status,'N') = 'Y'
AND NVL(match_status_flag,'N') <> 'N'
-- Bug 9945411 Begin
AND NVL( posted_flag, 'N' ) = 'N'
AND NVL( cash_posted_flag, 'N' ) = 'N'
AND NVL( encumbered_flag, 'X' ) <> 'Y'
-- Bug 9945411 End
RETURNING invoice_distribution_id
BULK COLLECT INTO l_dbi_key_value_list;
X_update_occurred := 'Y';
nvl(X_update_base,'N') = 'Y') THEN
-- Update the distributions if this is a foreign currency invoice
-- and there is an invoice exchange rate as something has been changed
-- Check that the base amounts sum to the invoice base amount
-- Fire for functional currency invoices if multi-curr is enabled
-- as it may have just been changed to functional so we want to clear
-- all the base columns
-- Set the exchange rate info, base amount and WHO columns
-- for the distributions
--Bugfix:4644053 changed ap_invoice_distributions
--to ap_invoice_distributions_all
debug_info := 'Update AP_INVOICE_DISTRIBUTIONS base amounts';
UPDATE AP_INVOICE_DISTRIBUTIONS_ALL
SET base_amount =
DECODE(l_base_currency_code,
l_invoice_currency_code, NULL,
ap_utilities_pkg.ap_round_currency(
amount * l_exchange_rate,
l_base_currency_code)),
last_update_login = l_last_update_login,
last_updated_by = l_last_updated_by,
last_update_date = SYSDATE
WHERE invoice_id = X_invoice_id
AND invoice_line_number = nvl( x_line_number, invoice_line_number)
AND NVL(posted_flag,'N') = 'N'
AND ( ( base_amount is null AND
DECODE(l_base_currency_code,
l_invoice_currency_code, NULL,
ap_utilities_pkg.ap_round_currency(
amount * l_exchange_rate,
l_base_currency_code)) is not null)
OR
(NVL(base_amount,0) <>
DECODE(l_base_currency_code,
l_invoice_currency_code, NULL,
ap_utilities_pkg.ap_round_currency(
amount * l_exchange_rate,
l_base_currency_code))
AND NVL(reversal_flag,'N') <> 'Y' --Bug 8347194
))
AND line_type_lookup_code NOT IN ('NONREC_TAX','REC_TAX','TRV','TERV','TIPV','IPV', 'ERV') -- Bug 11654030 --Bug 12691958: Excluded 'IPV' and 'ERV' lines
AND related_id IS NULL -- Bug 13864403
RETURNING invoice_distribution_id
BULK COLLECT INTO l_key_value_list;
X_update_occurred := 'Y';
X_update_base := 'N';
-- If not update the biggest distribution
-- Only do this if the distributions add up in the entered currency
-----------------------------------------------------------------
debug_info := 'Call API to check if rounding amount is existing';
select base_amount
INTO l_base_amt
FROM AP_INVOICE_DISTRIBUTIONS
WHERE invoice_id = X_invoice_id
AND invoice_line_number = l_line_number
AND invoice_distribution_id = l_round_dist_id_list(i);
UPDATE AP_INVOICE_DISTRIBUTIONS
SET base_amount = l_base_amt,
rounding_amt = ABS( l_modified_dist_rounding_amt ),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE invoice_distribution_id = l_round_dist_id_list(i);
X_update_occurred := 'Y';
| X_update_base being set to 'Y' so we use this same variable to |
| determine whether to call the PA flexbuilder routine again or |
| not |
+-----------------------------------------------------------------*/
/* ------------------------------------------------
debug_info := 'Call PA flex builder procedures';
| update invoice_distributions with l_dist_code_combination_id |
+-----------------------------------------------------------------*/
/* IF (l_dist_posted_flag = 'N') THEN
IF (l_dist_reversal_flag <> 'Y') THEN
UPDATE ap_invoice_distributions
SET dist_code_combination_id = l_dist_code_combination_id
WHERE invoice_distribution_id = l_invoice_distribution_id;
X_update_occurred := 'Y';
UPDATE AP_INVOICE_DISTRIBUTIONS
SET last_update_login = l_last_update_login
WHERE invoice_id = X_invoice_id
AND last_update_login = -3;
END IF; -- check l_multi_currency_flag = y and x_update_base=y
||', X_update_base = ' ||X_update_base
||', X_reset_match_status = ' ||X_reset_match_status
||', X_update_occurred = ' ||X_update_occurred
);
END Update_Distributions;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Invoice_Id NUMBER,
-- Invoice Lines Project Stage 1
X_Invoice_Line_Number NUMBER,
X_Distribution_Class VARCHAR2,
X_Invoice_Distribution_Id IN OUT NOCOPY NUMBER,
X_Dist_Code_Combination_Id NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Accounting_Date DATE,
X_Period_Name VARCHAR2,
X_Set_Of_Books_Id NUMBER,
X_Amount NUMBER,
X_Description VARCHAR2,
X_Type_1099 VARCHAR2,
X_Posted_Flag VARCHAR2,
X_Batch_Id NUMBER,
X_Quantity_Invoiced NUMBER,
X_Unit_Price NUMBER,
X_Match_Status_Flag VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Prepay_Amount_Remaining NUMBER,
X_Assets_Addition_Flag VARCHAR2,
X_Assets_Tracking_Flag VARCHAR2,
X_Distribution_Line_Number NUMBER,
X_Line_Type_Lookup_Code VARCHAR2,
X_Po_Distribution_Id NUMBER,
X_Base_Amount NUMBER,
X_Pa_Addition_Flag VARCHAR2,
X_Posted_Amount NUMBER,
X_Posted_Base_Amount NUMBER,
X_Encumbered_Flag VARCHAR2,
X_Accrual_Posted_Flag VARCHAR2,
X_Cash_Posted_Flag VARCHAR2,
X_Last_Update_Login NUMBER,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Stat_Amount NUMBER,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Accts_Pay_Code_Comb_Id NUMBER,
X_Reversal_Flag VARCHAR2,
X_Parent_Invoice_Id NUMBER,
X_Income_Tax_Region VARCHAR2,
X_Final_Match_Flag VARCHAR2,
X_Expenditure_Item_Date DATE,
X_Expenditure_Organization_Id NUMBER,
X_Expenditure_Type VARCHAR2,
X_Pa_Quantity NUMBER,
X_Project_Id NUMBER,
X_Task_Id NUMBER,
X_Quantity_Variance NUMBER,
X_Base_Quantity_Variance NUMBER,
X_Packet_Id NUMBER,
X_Awt_Flag VARCHAR2,
X_Awt_Group_Id NUMBER,
X_Pay_Awt_Group_Id NUMBER,--bug6639866
X_Awt_Tax_Rate_Id NUMBER,
X_Awt_Gross_Amount NUMBER,
X_Reference_1 VARCHAR2,
X_Reference_2 VARCHAR2,
X_Org_Id NUMBER,
X_Other_Invoice_Id NUMBER,
X_Awt_Invoice_Id NUMBER,
X_Awt_Origin_Group_Id NUMBER,
X_Program_Application_Id NUMBER,
X_Program_Id NUMBER,
X_Program_Update_Date DATE,
X_Request_Id NUMBER,
X_Tax_Recoverable_Flag VARCHAR2,
X_Award_Id NUMBER,
X_Start_Expense_Date DATE,
X_Merchant_Document_Number VARCHAR2,
X_Merchant_Name VARCHAR2,
X_Merchant_Reference VARCHAR2,
X_Merchant_Tax_Reg_Number VARCHAR2,
X_Merchant_Taxpayer_Id VARCHAR2,
X_Country_Of_Supply VARCHAR2,
X_Parent_Reversal_id NUMBER,
X_rcv_transaction_id NUMBER,
X_matched_uom_lookup_code VARCHAR2,
X_global_attribute_category VARCHAR2 DEFAULT NULL,
X_global_attribute1 VARCHAR2 DEFAULT NULL,
X_global_attribute2 VARCHAR2 DEFAULT NULL,
X_global_attribute3 VARCHAR2 DEFAULT NULL,
X_global_attribute4 VARCHAR2 DEFAULT NULL,
X_global_attribute5 VARCHAR2 DEFAULT NULL,
X_global_attribute6 VARCHAR2 DEFAULT NULL,
X_global_attribute7 VARCHAR2 DEFAULT NULL,
X_global_attribute8 VARCHAR2 DEFAULT NULL,
X_global_attribute9 VARCHAR2 DEFAULT NULL,
X_global_attribute10 VARCHAR2 DEFAULT NULL,
X_global_attribute11 VARCHAR2 DEFAULT NULL,
X_global_attribute12 VARCHAR2 DEFAULT NULL,
X_global_attribute13 VARCHAR2 DEFAULT NULL,
X_global_attribute14 VARCHAR2 DEFAULT NULL,
X_global_attribute15 VARCHAR2 DEFAULT NULL,
X_global_attribute16 VARCHAR2 DEFAULT NULL,
X_global_attribute17 VARCHAR2 DEFAULT NULL,
X_global_attribute18 VARCHAR2 DEFAULT NULL,
X_global_attribute19 VARCHAR2 DEFAULT NULL,
X_global_attribute20 VARCHAR2 DEFAULT NULL,
-- Invoice Lines Project Stage 1
X_rounding_amt NUMBER DEFAULT NULL,
X_charge_applicable_to_dist_id NUMBER DEFAULT NULL,
X_corrected_invoice_dist_id NUMBER DEFAULT NULL,
X_related_id NUMBER DEFAULT NULL,
X_asset_book_type_code VARCHAR2 DEFAULT NULL,
X_asset_category_id NUMBER DEFAULT NULL ,
X_Intended_Use VARCHAR2 DEFAULT NULL,
x_calling_sequence VARCHAR2
) IS
current_calling_sequence VARCHAR2(2000);
'AP_INVOICE_DISTRIBUTIONS_PKG.Insert_Row<-'||X_Calling_Sequence;
AP_AID_TABLE_HANDLER_PKG.Insert_Row
(X_Rowid,
X_Invoice_Id,
-- Invoice Lines Project Stage 1
X_Invoice_Line_Number,
X_Distribution_Class,
X_Invoice_Distribution_Id,
X_Dist_Code_Combination_Id,
X_Last_Update_Date,
X_Last_Updated_By,
X_Accounting_Date,
X_Period_Name,
X_Set_Of_Books_Id,
X_Amount,
X_Description,
X_Type_1099,
X_Posted_Flag,
X_Batch_Id,
X_Quantity_Invoiced,
X_Unit_Price,
X_Match_Status_Flag,
X_Attribute_Category,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Prepay_Amount_Remaining,
X_Assets_Addition_Flag,
X_Assets_Tracking_Flag,
X_Distribution_Line_Number,
X_Line_Type_Lookup_Code,
X_Po_Distribution_Id,
X_Base_Amount,
X_Pa_Addition_Flag,
X_Posted_Amount,
X_Posted_Base_Amount,
X_Encumbered_Flag,
X_Accrual_Posted_Flag,
X_Cash_Posted_Flag,
X_Last_Update_Login,
X_Creation_Date,
X_Created_By,
X_Stat_Amount,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute15,
X_Accts_Pay_Code_Comb_Id,
X_Reversal_Flag,
X_Parent_Invoice_Id,
X_Income_Tax_Region,
X_Final_Match_Flag,
X_Expenditure_Item_Date,
X_Expenditure_Organization_Id,
X_Expenditure_Type,
X_Pa_Quantity,
X_Project_Id,
X_Task_Id,
X_Quantity_Variance,
X_Base_Quantity_Variance,
X_Packet_Id,
X_Awt_Flag,
X_Awt_Group_Id,
X_Pay_Awt_Group_Id,--bug6639866
X_Awt_Tax_Rate_Id,
X_Awt_Gross_Amount,
X_Reference_1,
X_Reference_2,
X_Org_Id,
X_Other_Invoice_Id,
X_Awt_Invoice_Id,
X_Awt_Origin_Group_Id,
X_Program_Application_Id,
X_Program_Id,
X_Program_Update_Date,
X_Request_Id,
X_Tax_Recoverable_Flag,
X_Award_Id,
X_Start_Expense_Date,
X_Merchant_Document_Number,
X_Merchant_Name,
X_Merchant_Tax_Reg_Number,
X_Merchant_Taxpayer_Id,
X_Country_Of_Supply,
X_Merchant_Reference,
X_Parent_Reversal_Id,
X_rcv_transaction_id,
X_matched_uom_lookup_code,
X_global_attribute_category,
X_global_attribute1,
X_global_attribute2,
X_global_attribute3,
X_global_attribute4,
X_global_attribute5,
X_global_attribute6,
X_global_attribute7,
X_global_attribute8,
X_global_attribute9,
X_global_attribute10,
X_global_attribute11,
X_global_attribute12,
X_global_attribute13,
X_global_attribute14,
X_global_attribute15,
X_global_attribute16,
X_global_attribute17,
X_global_attribute18,
X_global_attribute19,
X_global_attribute20,
current_calling_sequence,
-- Added by iyas cuz params don't exist in ap_aid_table_handlers_pkg
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
-- Invoice Lines Project Stage 1
X_rounding_amt,
X_charge_applicable_to_dist_id,
X_corrected_invoice_dist_id,
X_related_id,
X_asset_book_type_code,
X_asset_category_id,
X_Intended_Use
);
END Insert_Row;
X_Program_Update_Date DATE,
X_Request_Id NUMBER,
X_Tax_Recoverable_Flag VARCHAR2,
X_Award_Id NUMBER,
X_Start_Expense_Date DATE,
X_Merchant_Document_Number VARCHAR2,
X_Merchant_Name VARCHAR2,
X_Merchant_Reference VARCHAR2,
X_Merchant_Tax_Reg_Number VARCHAR2,
X_Merchant_Taxpayer_Id VARCHAR2,
X_Country_Of_Supply VARCHAR2,
X_global_attribute_category VARCHAR2 DEFAULT NULL,
X_global_attribute1 VARCHAR2 DEFAULT NULL,
X_global_attribute2 VARCHAR2 DEFAULT NULL,
X_global_attribute3 VARCHAR2 DEFAULT NULL,
X_global_attribute4 VARCHAR2 DEFAULT NULL,
X_global_attribute5 VARCHAR2 DEFAULT NULL,
X_global_attribute6 VARCHAR2 DEFAULT NULL,
X_global_attribute7 VARCHAR2 DEFAULT NULL,
X_global_attribute8 VARCHAR2 DEFAULT NULL,
X_global_attribute9 VARCHAR2 DEFAULT NULL,
X_global_attribute10 VARCHAR2 DEFAULT NULL,
X_global_attribute11 VARCHAR2 DEFAULT NULL,
X_global_attribute12 VARCHAR2 DEFAULT NULL,
X_global_attribute13 VARCHAR2 DEFAULT NULL,
X_global_attribute14 VARCHAR2 DEFAULT NULL,
X_global_attribute15 VARCHAR2 DEFAULT NULL,
X_global_attribute16 VARCHAR2 DEFAULT NULL,
X_global_attribute17 VARCHAR2 DEFAULT NULL,
X_global_attribute18 VARCHAR2 DEFAULT NULL,
X_global_attribute19 VARCHAR2 DEFAULT NULL,
X_global_attribute20 VARCHAR2 DEFAULT NULL,
-- Invoice Lines Project Stage 1
X_rounding_amt NUMBER DEFAULT NULL,
X_charge_applicable_to_dist_id NUMBER DEFAULT NULL,
X_corrected_invoice_dist_id NUMBER DEFAULT NULL,
X_related_id NUMBER DEFAULT NULL,
X_asset_book_type_code VARCHAR2 DEFAULT NULL,
X_asset_category_id NUMBER DEFAULT NULL,
--ETAX: Invoice Workbench
X_Intended_Use VARCHAR2 DEFAULT NULL,
X_Calling_Sequence VARCHAR2
) IS
current_calling_sequence VARCHAR2(2000);
SELECT
PREPAY_DISTRIBUTION_ID,
ACCOUNTING_EVENT_ID, -- Bug 9385883
ACCOUNTING_DATE,
ACCRUAL_POSTED_FLAG,
ASSETS_ADDITION_FLAG,
ASSETS_TRACKING_FLAG,
CASH_POSTED_FLAG,
DISTRIBUTION_LINE_NUMBER,
DIST_CODE_COMBINATION_ID,
INVOICE_ID,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LINE_TYPE_LOOKUP_CODE,
PERIOD_NAME,
SET_OF_BOOKS_ID,
ACCTS_PAY_CODE_COMBINATION_ID,
AMOUNT,
BASE_AMOUNT,
BATCH_ID,
CREATED_BY,
CREATION_DATE,
DESCRIPTION,
FINAL_MATCH_FLAG,
INCOME_TAX_REGION,
LAST_UPDATE_LOGIN,
MATCH_STATUS_FLAG,
POSTED_FLAG,
PO_DISTRIBUTION_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
QUANTITY_INVOICED,
REQUEST_ID,
REVERSAL_FLAG,
TYPE_1099,
UNIT_PRICE,
--AMOUNT_ENCUMBERED,
--BASE_AMOUNT_ENCUMBERED,
ENCUMBERED_FLAG,
--PRICE_ADJUSTMENT_FLAG,
--QUANTITY_UNENCUMBERED,
STAT_AMOUNT,
--AMOUNT_TO_POST,
ATTRIBUTE1,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE_CATEGORY,
--BASE_AMOUNT_TO_POST,
EXPENDITURE_ITEM_DATE,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_TYPE,
PARENT_INVOICE_ID,
PA_ADDITION_FLAG,
PA_QUANTITY,
POSTED_AMOUNT,
POSTED_BASE_AMOUNT,
PREPAY_AMOUNT_REMAINING,
PROJECT_ID,
TASK_ID,
--EARLIEST_SETTLEMENT_DATE,
--REQ_DISTRIBUTION_ID,
QUANTITY_VARIANCE,
BASE_QUANTITY_VARIANCE,
PACKET_ID,
AWT_FLAG,
AWT_GROUP_ID,
PAY_AWT_GROUP_ID,--bug6639866
AWT_TAX_RATE_ID,
AWT_GROSS_AMOUNT,
AWT_INVOICE_ID,
AWT_ORIGIN_GROUP_ID,
REFERENCE_1,
REFERENCE_2,
ORG_ID,
OTHER_INVOICE_ID,
--AWT_INVOICE_PAYMENT_ID,
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,
--LINE_GROUP_NUMBER,
--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,
AWARD_ID,
--MRC_DIST_CODE_COMBINATION_ID,
--MRC_BASE_AMOUNT,
--MRC_BASE_INV_PRICE_VARIANCE,
--MRC_EXCHANGE_RATE_VARIANCE,
--MRC_RATE_VAR_CCID,
--MRC_EXCHANGE_DATE,
--MRC_EXCHANGE_RATE,
--MRC_EXCHANGE_RATE_TYPE,
--MRC_RECEIPT_CONVERSION_RATE,
DIST_MATCH_TYPE,
RCV_TRANSACTION_ID,
-- Invoice Lines Project Stage 1
INVOICE_LINE_NUMBER,
DISTRIBUTION_CLASS,
INVOICE_DISTRIBUTION_ID,
PARENT_REVERSAL_ID,
TAX_RECOVERABLE_FLAG,
--PA_CC_AR_INVOICE_ID,
--PA_CC_AR_INVOICE_LINE_NUM,
--PA_CC_PROCESSED_CODE,
MERCHANT_DOCUMENT_NUMBER,
MERCHANT_NAME,
MERCHANT_REFERENCE,
MERCHANT_TAX_REG_NUMBER,
MERCHANT_TAXPAYER_ID,
COUNTRY_OF_SUPPLY,
MATCHED_UOM_LOOKUP_CODE,
GMS_BURDENABLE_RAW_COST,
--CREDIT_CARD_TRX_ID,
--UPGRADE_POSTED_AMT,
--UPGRADE_BASE_POSTED_AMT,
-- Invoice Lines Project Stage 1
ROUNDING_AMT,
CHARGE_APPLICABLE_TO_DIST_ID,
CORRECTED_INVOICE_DIST_ID,
RELATED_ID,
ASSET_BOOK_TYPE_CODE,
ASSET_CATEGORY_ID,
INTENDED_USE
FROM AP_INVOICE_DISTRIBUTIONS
WHERE rowid = X_Rowid
FOR UPDATE of Invoice_Id NOWAIT;
SELECT 'Locked'
FROM xla_events xe
WHERE xe.event_id = l_event_id
AND xe.application_id= 200
FOR UPDATE OF event_id NOWAIT;
debug_info := 'Select from ap_invoice_distributions';
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
debug_info := 'Select from xla_events';
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
AND ( (Recinfo.program_update_date = X_Program_Update_Date)
OR ( (Recinfo.program_update_date IS NULL)
AND (X_Program_Update_Date IS NULL)))
AND ( (Recinfo.request_id = X_Request_Id)
OR ( (Recinfo.request_id IS NULL)
AND (X_Request_Id IS NULL)))
AND ( (Recinfo.tax_recoverable_flag = X_Tax_Recoverable_Flag)
OR ( (Recinfo.tax_recoverable_flag IS NULL)
AND (X_Tax_Recoverable_Flag IS NULL)))
AND ( (Recinfo.award_id = X_Award_Id)
OR ( (Recinfo.award_id IS NULL)
AND (X_Award_Id IS NULL)))
AND ( (Recinfo.start_expense_date = X_Start_Expense_Date)
OR ( (Recinfo.start_expense_date IS NULL)
AND (X_Start_Expense_Date IS NULL)))
AND ( (Recinfo.merchant_document_number = X_Merchant_Document_Number)
OR ( (Recinfo.merchant_document_number IS NULL)
AND (X_Merchant_Document_Number IS NULL)))
AND ( (Recinfo.merchant_name = X_Merchant_Name)
OR ( (Recinfo.merchant_name IS NULL)
AND (X_Merchant_Name IS NULL)))
AND ( (Recinfo.merchant_tax_reg_number = X_Merchant_Tax_Reg_Number)
OR ( (Recinfo.merchant_tax_reg_number IS NULL)
AND (X_Merchant_Tax_Reg_Number IS NULL)))
AND ( (Recinfo.merchant_taxpayer_id = X_Merchant_Taxpayer_Id)
OR ( (Recinfo.merchant_taxpayer_id IS NULL)
AND (X_Merchant_Taxpayer_Id IS NULL)))
AND ( (Recinfo.merchant_reference = X_Merchant_Reference)
OR ( (Recinfo.merchant_reference IS NULL)
AND (X_Merchant_Reference IS NULL)))
AND ( (Recinfo.country_of_supply = X_Country_Of_Supply)
OR ( (Recinfo.country_of_supply IS NULL)
AND (X_Country_Of_Supply IS NULL)))
) then
null;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Invoice_Id NUMBER,
-- Invoice Lines Project Stage 1
X_Invoice_Line_Number NUMBER,
X_Distribution_Class VARCHAR2,
X_Dist_Code_Combination_Id NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Accounting_Date DATE,
X_Period_Name VARCHAR2,
X_Set_Of_Books_Id NUMBER,
X_Amount NUMBER,
X_Description VARCHAR2,
X_Type_1099 VARCHAR2,
X_Posted_Flag VARCHAR2,
X_Batch_Id NUMBER,
X_Quantity_Invoiced NUMBER,
X_Unit_Price NUMBER,
X_Match_Status_Flag VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Prepay_Amount_Remaining NUMBER,
X_Assets_Addition_Flag VARCHAR2,
X_Assets_Tracking_Flag VARCHAR2,
X_Distribution_Line_Number NUMBER,
X_Line_Type_Lookup_Code VARCHAR2,
X_Po_Distribution_Id NUMBER,
X_Base_Amount NUMBER,
X_Pa_Addition_Flag VARCHAR2,
X_Posted_Amount NUMBER,
X_Posted_Base_Amount NUMBER,
X_Encumbered_Flag VARCHAR2,
X_Accrual_Posted_Flag VARCHAR2,
X_Cash_Posted_Flag VARCHAR2,
X_Last_Update_Login NUMBER,
X_Stat_Amount NUMBER,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Accts_Pay_Code_Comb_Id NUMBER,
X_Reversal_Flag VARCHAR2,
X_Parent_Invoice_Id NUMBER,
X_Income_Tax_Region VARCHAR2,
X_Final_Match_Flag VARCHAR2,
X_Expenditure_Item_Date DATE,
X_Expenditure_Organization_Id NUMBER,
X_Expenditure_Type VARCHAR2,
X_Pa_Quantity NUMBER,
X_Project_Id NUMBER,
X_Task_Id NUMBER,
X_Quantity_Variance NUMBER,
X_Base_Quantity_Variance NUMBER,
X_Packet_Id NUMBER,
X_Awt_Flag VARCHAR2,
X_Awt_Group_Id NUMBER,
X_Pay_Awt_Group_Id NUMBER,--bug6639866
X_Awt_Tax_Rate_Id NUMBER,
X_Awt_Gross_Amount NUMBER,
X_Reference_1 VARCHAR2,
X_Reference_2 VARCHAR2,
X_Org_Id NUMBER,
X_Other_Invoice_Id NUMBER,
X_Awt_Invoice_Id NUMBER,
X_Awt_Origin_Group_Id NUMBER,
X_Program_Application_Id NUMBER,
X_Program_Id NUMBER,
X_Program_Update_Date DATE,
X_Request_Id NUMBER,
X_Tax_Recoverable_Flag VARCHAR2,
X_Award_Id NUMBER,
X_Start_Expense_Date DATE,
X_Merchant_Document_Number VARCHAR2,
X_Merchant_Name VARCHAR2,
X_Merchant_Tax_Reg_Number VARCHAR2,
X_Merchant_Taxpayer_Id VARCHAR2,
X_Country_Of_Supply VARCHAR2,
X_Merchant_Reference VARCHAR2,
X_global_attribute_category VARCHAR2 DEFAULT NULL,
X_global_attribute1 VARCHAR2 DEFAULT NULL,
X_global_attribute2 VARCHAR2 DEFAULT NULL,
X_global_attribute3 VARCHAR2 DEFAULT NULL,
X_global_attribute4 VARCHAR2 DEFAULT NULL,
X_global_attribute5 VARCHAR2 DEFAULT NULL,
X_global_attribute6 VARCHAR2 DEFAULT NULL,
X_global_attribute7 VARCHAR2 DEFAULT NULL,
X_global_attribute8 VARCHAR2 DEFAULT NULL,
X_global_attribute9 VARCHAR2 DEFAULT NULL,
X_global_attribute10 VARCHAR2 DEFAULT NULL,
X_global_attribute11 VARCHAR2 DEFAULT NULL,
X_global_attribute12 VARCHAR2 DEFAULT NULL,
X_global_attribute13 VARCHAR2 DEFAULT NULL,
X_global_attribute14 VARCHAR2 DEFAULT NULL,
X_global_attribute15 VARCHAR2 DEFAULT NULL,
X_global_attribute16 VARCHAR2 DEFAULT NULL,
X_global_attribute17 VARCHAR2 DEFAULT NULL,
X_global_attribute18 VARCHAR2 DEFAULT NULL,
X_global_attribute19 VARCHAR2 DEFAULT NULL,
X_global_attribute20 VARCHAR2 DEFAULT NULL,
X_Calling_Sequence VARCHAR2,
-- Invoice Lines Project Stage 1
X_rounding_amt NUMBER DEFAULT NULL,
X_charge_applicable_to_dist_id NUMBER DEFAULT NULL,
X_corrected_invoice_dist_id NUMBER DEFAULT NULL,
X_related_id NUMBER DEFAULT NULL,
X_asset_book_type_code VARCHAR2 DEFAULT NULL,
X_asset_category_id NUMBER DEFAULT NULL,
X_intended_use VARCHAR2 DEFAULT NULL
) IS
current_calling_sequence VARCHAR2(2000);
l_api_name CONSTANT VARCHAR2(100) := 'Update_Row';
'AP_INVOICE_DISTRIBUTIONS_PKG.Update_Row<-'||X_Calling_Sequence;
AP_AID_TABLE_HANDLER_PKG.Update_Row
(X_Rowid,
X_Invoice_Id,
-- Invoice Lines Project Stage 1
X_Invoice_Line_number,
X_Distribution_Class,
X_Dist_Code_Combination_Id,
X_Last_Update_Date,
X_Last_Updated_By,
X_Accounting_Date,
X_Period_Name,
X_Set_Of_Books_Id,
X_Amount,
X_Description,
X_Type_1099,
X_Posted_Flag,
X_Batch_Id,
X_Quantity_Invoiced,
X_Unit_Price,
X_Match_Status_Flag,
X_Attribute_Category,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Prepay_Amount_Remaining,
X_Assets_Addition_Flag,
X_Assets_Tracking_Flag,
X_Distribution_Line_Number,
X_Line_Type_Lookup_Code,
X_Po_Distribution_Id,
X_Base_Amount,
X_Pa_Addition_Flag,
X_Posted_Amount,
X_Posted_Base_Amount,
X_Encumbered_Flag,
X_Accrual_Posted_Flag,
X_Cash_Posted_Flag,
X_Last_Update_Login,
X_Stat_Amount,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute15,
X_Accts_Pay_Code_Comb_Id,
X_Reversal_Flag,
X_Parent_Invoice_Id,
X_Income_Tax_Region,
X_Final_Match_Flag,
X_Expenditure_Item_Date,
X_Expenditure_Organization_Id,
X_Expenditure_Type,
X_Pa_Quantity,
X_Project_Id,
X_Task_Id,
X_Quantity_Variance,
X_Base_Quantity_Variance,
X_Packet_Id,
X_Awt_Flag,
X_Awt_Group_Id,
X_Pay_Awt_Group_Id,--bug6639866
X_Awt_Tax_Rate_Id,
X_Awt_Gross_Amount,
X_Reference_1,
X_Reference_2,
X_Org_Id,
X_Other_Invoice_Id,
X_Awt_Invoice_Id,
X_Awt_Origin_Group_Id,
X_Program_Application_Id,
X_Program_Id,
X_Program_Update_Date,
X_Request_Id,
X_Tax_Recoverable_Flag,
X_Award_Id,
X_Start_Expense_Date,
X_Merchant_Document_Number,
X_Merchant_Name,
X_Merchant_Tax_Reg_Number,
X_Merchant_Taxpayer_Id,
X_Country_Of_Supply,
X_Merchant_Reference,
X_global_attribute_category,
X_global_attribute1,
X_global_attribute2,
X_global_attribute3,
X_global_attribute4,
X_global_attribute5,
X_global_attribute6,
X_global_attribute7,
X_global_attribute8,
X_global_attribute9,
X_global_attribute10,
X_global_attribute11,
X_global_attribute12,
X_global_attribute13,
X_global_attribute14,
X_global_attribute15,
X_global_attribute16,
X_global_attribute17,
X_global_attribute18,
X_global_attribute19,
X_global_attribute20,
current_calling_sequence,
-- Invoice Lines Project Stage 1
X_rounding_amt,
X_charge_applicable_to_dist_id,
X_corrected_invoice_dist_id,
X_related_id,
X_asset_book_type_code,
X_asset_category_id,
X_intended_use
);
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2,
X_Calling_Sequence VARCHAR2) IS
current_calling_sequence VARCHAR2(2000);
'AP_INVOICE_DISTRIBUTIONS_PKG.Delete_Row<-'||X_Calling_Sequence;
AP_AID_TABLE_HANDLER_PKG.Delete_Row(
X_Rowid,
current_calling_sequence);
END Delete_Row;
SELECT unit_of_measure
INTO l_uom
FROM gl_stat_account_uom
WHERE account_segment_value = l_segments(l_account_segment_num)
AND chart_of_accounts_id = X_Ch_Of_Accts_Id;
SELECT sum(amount)
INTO l_dist_amount
FROM ap_invoice_distributions
WHERE invoice_id = X_Invoice_Id
AND line_type_lookup_code IN ('ITEM','PREPAY')
AND nvl(reversal_flag,'N') <> 'Y'
AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
dist_code_combination_id, X_Sob_Id) =
AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
X_Prepay_Dist_CCID, X_Sob_Id);
SELECT sum(nvl(prepay_amount_remaining,amount)),
AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
aip.dist_code_combination_id, X_Sob_Id)
FROM ap_invoice_distributions aip
WHERE aip.invoice_id = X_Prepay_Id
--bugfix:3881673
AND aip.line_type_lookup_code in ('ITEM','ACCRUAL')
AND nvl(aip.reversal_flag,'N') <> 'Y'
AND nvl(aip.prepay_amount_remaining,amount) > 0
AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
aip.dist_code_combination_id, X_Sob_Id) IN
(SELECT AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
aid.dist_code_combination_id, X_Sob_Id)
FROM ap_invoice_distributions aid
WHERE aid.invoice_id = X_Invoice_ID)
GROUP BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
aip.dist_code_combination_id, X_Sob_Id)
ORDER BY AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
aip.dist_code_combination_id, X_Sob_Id);
SELECT sum(amount)
INTO l_invoice_amount
FROM ap_invoice_distributions
WHERE invoice_id = X_Invoice_ID
AND line_type_lookup_code IN ('ITEM','PREPAY')
AND nvl(reversal_flag,'N') <> 'Y'
AND AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
dist_code_combination_id, X_Sob_Id)
= l_bal_segment;
SELECT count(distinct(
AP_INVOICE_DISTRIBUTIONS_PKG.get_balancing_segment_value(
dist_code_combination_id, X_Sob_Id)) )
INTO l_dist_count
FROM ap_invoice_distributions
WHERE invoice_id = X_Invoice_Id;
SELECT chart_of_accounts_id
INTO l_coa_id
FROM gl_sets_of_books
WHERE set_of_books_id = X_Sob_Id;
SELECT chart_of_accounts_id
INTO l_coa_id
FROM gl_sets_of_books
WHERE set_of_books_id = X_Sob_Id;
UPDATE ap_invoice_distributions d1
SET base_amount =
(SELECT DECODE(SIGN(SUM(d2.base_amount) -
DECODE(max(f.minimum_accountable_unit), NULL,
ROUND((SUM(d2.amount)
* i.exchange_rate),MAX(f.precision)),
ROUND((SUM(d2.amount) * i.exchange_rate)
/ MAX(f.minimum_accountable_unit)) *
MAX(f.minimum_accountable_unit))),
1, d1.base_amount
- (SUM(d2.base_amount) -
DECODE(MAX(f.minimum_accountable_unit), NULL,
ROUND((SUM(d2.amount)
* i.exchange_rate),
MAX(f.precision)),
ROUND((SUM(d2.amount) * i.exchange_rate)
/ MAX(f.minimum_accountable_unit))*
MAX(f.minimum_accountable_unit))),
-1, d1.base_amount
- (SUM(d2.base_amount) -
DECODE(MAX(f.minimum_accountable_unit), NULL,
ROUND((SUM(d2.amount) *
i.exchange_rate),
MAX(f.precision)),
ROUND((SUM(d2.amount) * i.exchange_rate)
/MAX(f.minimum_accountable_unit))*
MAX(f.minimum_accountable_unit))),
d1.base_amount)
FROM ap_invoices i, ap_invoice_distributions d2, fnd_currencies F
WHERE d1.invoice_id = i.invoice_id
AND d1.invoice_id = d2.invoice_id
AND f.currency_code = X_Base_Currency_Code
GROUP BY i.exchange_rate)
WHERE d1.invoice_id = X_Invoice_Id
AND d1.posted_flag = 'N'
AND (d1.invoice_id, d1.distribution_line_number) IN
(SELECT d5.invoice_id, MAX(d5.distribution_line_number)
FROM ap_invoice_distributions_all d5
WHERE (d5.invoice_id, abs(d5.amount)) IN
(SELECT i1.invoice_id , MAX(ABS(d3.amount))
FROM ap_invoices_all i1, ap_invoice_distributions_all d3
WHERE i1.invoice_id = X_Invoice_Id
AND i1.invoice_id = d3.invoice_id
AND d3.line_type_lookup_code NOT IN ('NONREC_TAX','REC_TAX','TRV','TERV','TIPV','IPV', 'ERV') -- Bug 11654030 <> 'TAX', Bug 12691958: Added excluding 'IPV' and 'ERV' lines
AND d3.related_id IS NULL -- Bug 13864403
AND NOT EXISTS
(SELECT d4.invoice_id
FROM ap_invoice_distributions_all d4
WHERE d4.invoice_id = X_Invoice_Id
AND (1 = DECODE(X_Base_Currency_Code,
i1.invoice_currency_code,1,
DECODE(d4.exchange_rate,null,1,0))
OR 1 = DECODE(X_Base_Currency_Code,
i1.invoice_currency_code,
1,DECODE(d4.base_amount,null,1,0))))
GROUP BY i1.invoice_id, i1.invoice_amount
HAVING nvl(i1.invoice_amount,0) = nvl(sum(d3.amount),0))
GROUP BY d5.invoice_id);
FUNCTION Insert_Single_Dist_From_Line(
X_batch_id IN AP_INVOICES.BATCH_ID%TYPE,
X_invoice_id IN NUMBER,
X_invoice_date IN AP_INVOICES.INVOICE_DATE%TYPE,
X_vendor_id IN AP_INVOICES.VENDOR_ID%TYPE,
X_invoice_currency IN AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE,
X_exchange_rate IN AP_INVOICES.EXCHANGE_RATE%TYPE,
X_exchange_rate_type IN AP_INVOICES.EXCHANGE_RATE_TYPE%TYPE,
X_exchange_date IN AP_INVOICES.EXCHANGE_DATE%TYPE,
X_line_number IN NUMBER,
X_invoice_lines_rec IN AP_INVOICES_PKG.r_invoice_line_rec,
X_line_source IN VARCHAR2,
X_Generate_Permanent IN VARCHAR2 DEFAULT 'N',
X_Validate_Info IN BOOLEAN DEFAULT TRUE,
X_Error_Code OUT NOCOPY VARCHAR2,
X_Debug_Info OUT NOCOPY VARCHAR2,
X_Debug_Context OUT NOCOPY VARCHAR2,
X_Msg_Application OUT NOCOPY VARCHAR2,
X_Msg_Data OUT NOCOPY VARCHAR2,
X_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
IS
CURSOR line_rec IS
SELECT 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,
DISCARDED_FLAG,
ORIGINAL_AMOUNT,
ORIGINAL_BASE_AMOUNT,
ORIGINAL_ROUNDING_AMT,
CANCELLED_FLAG,
INCOME_TAX_REGION,
TYPE_1099,
STAT_AMOUNT,
PREPAY_INVOICE_ID,
PREPAY_LINE_NUMBER,
INVOICE_INCLUDES_PREPAY_FLAG,
CORRECTED_INV_ID,
CORRECTED_LINE_NUMBER,
PO_HEADER_ID,
PO_LINE_ID,
PO_RELEASE_ID,
PO_LINE_LOCATION_ID,
PO_DISTRIBUTION_ID,
RCV_TRANSACTION_ID,
FINAL_MATCH_FLAG,
ASSETS_TRACKING_FLAG,
ASSET_BOOK_TYPE_CODE,
ASSET_CATEGORY_ID,
PROJECT_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
EXPENDITURE_ORGANIZATION_ID,
PA_QUANTITY,
PA_CC_AR_INVOICE_ID,
PA_CC_AR_INVOICE_LINE_NUM ,
PA_CC_PROCESSED_CODE,
AWARD_ID,
AWT_GROUP_ID,
REFERENCE_1,
REFERENCE_2,
RECEIPT_VERIFIED_FLAG,
RECEIPT_REQUIRED_FLAG,
RECEIPT_MISSING_FLAG,
JUSTIFICATION,
EXPENSE_GROUP,
START_EXPENSE_DATE,
END_EXPENSE_DATE,
RECEIPT_CURRENCY_CODE,
RECEIPT_CONVERSION_RATE,
RECEIPT_CURRENCY_AMOUNT,
DAILY_AMOUNT,
WEB_PARAMETER_ID,
ADJUSTMENT_REASON,
MERCHANT_DOCUMENT_NUMBER,
MERCHANT_NAME,
MERCHANT_REFERENCE,
MERCHANT_TAX_REG_NUMBER,
MERCHANT_TAXPAYER_ID,
COUNTRY_OF_SUPPLY,
CREDIT_CARD_TRX_ID,
COMPANY_PREPAID_INVOICE_ID,
CC_REVERSAL_FLAG,
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,
--ETAX: Invwkb, added included_tax_amount as modified the def
--of ap_invoices_pkg.r_invoice_line_rec
INCLUDED_TAX_AMOUNT,
PRIMARY_INTENDED_USE,
APPLICATION_ID,
PRODUCT_TABLE,
REFERENCE_KEY1,
REFERENCE_KEY2,
REFERENCE_KEY3,
REFERENCE_KEY4,
REFERENCE_KEY5,
--bugfix:4674194
SHIP_TO_LOCATION_ID,
--bug7022001
PAY_AWT_GROUP_ID
FROM ap_invoice_lines_all
WHERE invoice_id = X_invoice_id
AND line_number = X_line_number;
'AP_INVOICE_DISTRIBUTIONS_PKG.insert_single_dist_from_line <-'||
X_calling_sequence;
SELECT count(*)
INTO l_existing_distributions
FROM ap_invoice_distributions
WHERE invoice_id = l_invoice_line_rec.invoice_id
AND invoice_line_number = l_invoice_line_rec.line_number
AND rownum=1; --13507856 added the rownum condition to improve the performance
select attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
into l_invoice_attribute_rec.attribute_category,
l_invoice_attribute_rec.attribute1,
l_invoice_attribute_rec.attribute2,
l_invoice_attribute_rec.attribute3,
l_invoice_attribute_rec.attribute4,
l_invoice_attribute_rec.attribute5,
l_invoice_attribute_rec.attribute6,
l_invoice_attribute_rec.attribute7,
l_invoice_attribute_rec.attribute8,
l_invoice_attribute_rec.attribute9,
l_invoice_attribute_rec.attribute10,
l_invoice_attribute_rec.attribute11,
l_invoice_attribute_rec.attribute12,
l_invoice_attribute_rec.attribute13,
l_invoice_attribute_rec.attribute14,
l_invoice_attribute_rec.attribute15
from ap_invoices
where invoice_id = l_invoice_line_rec.invoice_id;
SELECT gsob.chart_of_accounts_id, ap.base_currency_code
INTO l_chart_of_accounts_id, l_base_currency_code
FROM ap_system_parameters ap, gl_sets_of_books gsob
WHERE ap.set_of_books_id = gsob.set_of_books_id
AND ap.set_of_books_id = l_invoice_line_rec.set_of_books_id
AND ap.org_id = l_invoice_line_rec.org_id; -- Bug 13858818.
SELECT employee_id
INTO l_employee_id
FROM ap_suppliers /* bUg 4718054 */
WHERE DECODE(SIGN(TO_DATE(TO_CHAR(START_DATE_ACTIVE,'DD-MM-YYYY'),
'DD-MM-YYYY') - TO_DATE(TO_CHAR(SYSDATE,'DD-MM-YYYY'),'DD-MM-YYYY')),
1, 'N', DECODE(SIGN(TO_DATE(TO_CHAR(END_DATE_ACTIVE ,'DD-MM-YYYY'),
'DD-MM-YYYY') - TO_DATE(TO_CHAR(SYSDATE,'DD-MM-YYYY'),'DD-MM-YYYY')),
-1, 'N', 0, 'N', 'Y')) = 'Y'
AND enabled_flag = 'Y'
AND vendor_id = X_vendor_id;
select default_code_comb_id
into l_employee_ccid
from PER_ASSIGNMENTS_F
where person_id = l_employee_id
and set_of_books_id = l_invoice_line_rec.set_of_books_id
and trunc(sysdate) BETWEEN trunc(effective_start_date)
and nvl(trunc(effective_end_date), trunc(sysdate));
select WEB_PARAMETER_ID
into l_web_parameter_id
from ap_invoice_lines
where invoice_id = X_invoice_id
AND line_number = X_line_number;
SELECT invoice_type_lookup_code
INTO l_invoice_type_lookup_code
FROM ap_invoices_all
WHERE invoice_id = X_invoice_id;
p_default_last_updated_by => FND_GLOBAL.user_id,
p_default_last_update_login => FND_GLOBAL.login_id,
p_pa_default_dist_ccid => l_dist_ccid,
p_pa_concatenated_segments =>
l_dist_code_concat, --OUT NOCOPY
p_debug_Info => debug_Info, --OUT NOCOPY
p_debug_Context => debug_Context, --OUT NOCOPY
p_calling_sequence =>
'Get_Proj_And_Acc_For_Chrg_Dist' ,
p_default_dist_ccid => l_invoice_line_rec.default_dist_ccid --IN bug 5386396
))) THEN
X_error_code := debug_Info; --Bug7598450
SELECT account_type
INTO l_account_type
FROM gl_code_combinations
WHERE code_combination_id = l_dist_ccid;
INSERT INTO ap_invoice_distributions(
batch_id,
invoice_id,
invoice_line_number,
invoice_distribution_id,
distribution_line_number,
line_type_lookup_code,
distribution_class,
description,
dist_match_type,
org_id,
dist_code_combination_id,
accounting_date,
period_name,
accrual_posted_flag,
cash_posted_flag,
amount_to_post,
base_amount_to_post,
posted_amount,
posted_base_amount,
posted_flag,
accounting_event_id,
upgrade_posted_amt,
upgrade_base_posted_amt,
set_of_books_id,
amount,
base_amount,
rounding_amt,
quantity_variance,
base_quantity_variance,
match_status_flag,
encumbered_flag,
packet_id,
reversal_flag,
parent_reversal_id,
cancellation_flag,
income_tax_region,
type_1099,
stat_amount,
charge_applicable_to_dist_id,
prepay_amount_remaining,
prepay_distribution_id,
parent_invoice_id,
corrected_invoice_dist_id,
corrected_quantity,
other_invoice_id,
po_distribution_id,
rcv_transaction_id,
unit_price,
matched_uom_lookup_code,
quantity_invoiced,
final_match_flag,
related_id,
assets_addition_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_addition_flag,
award_id,
gms_burdenable_raw_cost,
awt_flag,
awt_group_id,
awt_tax_rate_id,
awt_gross_amount,
awt_invoice_id,
awt_origin_group_id,
awt_invoice_payment_id,
awt_withheld_amt,
inventory_transfer_status,
reference_1,
reference_2,
receipt_verified_flag,
receipt_required_flag,
receipt_missing_flag,
justification,
expense_group,
start_expense_date,
end_expense_date,
receipt_currency_code,
receipt_conversion_rate,
receipt_currency_amount,
daily_amount,
web_parameter_id,
adjustment_reason,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
country_of_supply,
credit_card_trx_id,
company_prepaid_invoice_id,
cc_reversal_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
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,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
--ETAX: Invwkb
intended_use,
--Freight and Special Charges
rcv_charge_addition_flag,
--bug7022001
pay_awt_group_id)
VALUES (X_batch_id, -- batch_id
l_invoice_line_rec.invoice_id, -- invoice_id
l_invoice_line_rec.line_number, -- invoice_line_number
ap_invoice_distributions_s.nextval, -- invoice_distribution_id
1, -- distribution_line_number
l_invoice_line_rec.line_type_lookup_code, -- line_type_lookup_code
l_distribution_class, -- distribution_class
l_invoice_line_rec.description, -- description
'NOT_MATCHED', -- dist_match_type
l_invoice_line_rec.org_id, -- l_org_id
l_dist_ccid, -- dist_code_combination_id
l_open_gl_date, -- accounting_date
l_open_period_name, -- period_name
'N', -- accrual_posted_flag
'N', -- cash_posted_flag
NULL, -- amount_to_post
NULL, -- base_amount_to_post
NULL, -- posted_amount
NULL, -- posted_base_amount
'N', -- posted_flag
NULL, -- accounting_event_id
NULL, -- upgrade_posted_amt
NULL, -- upgrade_base_posted_amt
l_invoice_line_rec.set_of_books_id, -- set_of_books_id
l_invoice_line_rec.amount, -- amount
l_invoice_line_rec.base_amount, -- base_amount
l_invoice_line_rec.rounding_amt, -- rounding_amt
NULL, -- quantity_variance
NULL, -- base_quantity_variance
--Invoice Lines: Distributions, changed match_status_flag
--to NULL from 'N'.
NULl, -- match_status_flag
'N', -- encumbered_flag
NULL, -- packet_id
-- decode(l_invoice_line_rec.line_type_lookup_code,
'N', -- reversal_flag
NULL, -- parent_reversal_id
'N', -- cancellation_flag
decode(l_invoice_line_rec.type_1099,null,null,
l_invoice_line_rec.income_tax_region), -- income_tax_region
l_invoice_line_rec.type_1099, -- type_1099
--Introduce below value for bug#10241241
DECODE(GET_UOM(l_dist_ccid,l_chart_of_accounts_id),
NULL,NULL,
l_invoice_line_rec.stat_amount), --stat_amount
NULL, -- charge_applicable_to_dist_id
NULL, -- prepay_amount_remaining
NULL, -- prepay_distribution_id
NULL, -- parent_invoice_id
NULL, -- corrected_inv_dist_id
NULL, -- corrected_quantity
NULL, -- other_invoice_id
NULL, -- po_distribution_id
NULL, -- rcv_transaction_id
NULL, -- unit_price
NULL, -- matched_uom_lookup_code
NULL, -- quantity_invoiced
NULL, -- final_match_flag
NULL, -- related_id
'U', -- assets_addition_flag
l_assets_tracking_flag, -- assets_tracking_flag
decode(l_assets_tracking_flag,'Y',
l_invoice_line_rec.asset_book_type_code, NULL),-- asset_book_type_code
decode(l_assets_tracking_flag,'Y',
l_invoice_line_rec.asset_category_id, NULL),-- asset_category_id
l_invoice_line_rec.project_id, -- project_id
l_invoice_line_rec.task_id, -- task_id
l_invoice_line_rec.expenditure_type, -- expenditure_type
l_invoice_line_rec.expenditure_item_date, -- expenditure_item_date
l_invoice_line_rec.expenditure_organization_id, -- expenditure_organization_id
l_invoice_line_rec.pa_quantity, -- pa_quantity
decode(l_invoice_line_rec.project_id,NULL,'E', 'N'), -- pa_addition_flag
l_invoice_line_rec.award_id, -- award_id
NULL, -- gms_burdenable_raw_cost
/*Added the following decode for bug#7695497 Start */
decode(l_invoice_line_rec.line_type_lookup_code,
'AWT', decode(l_invoice_line_rec.line_source ,'MANUAL LINE ENTRY','M', NULL),
NULL), -- awt_flag
/*Added the above decode for bug#7695497 End */
l_invoice_line_rec.awt_group_id, -- awt_group_id
NULL, -- awt_tax_rate_id
NULL, -- awt_gross_amount
NULL, -- awt_invoice_id
NULL, -- awt_origin_group_id
NULL, -- awt_invoice_payment_id
NULL, -- awt_withheld_amt
'N', -- inventory_transfer_status
--Bug9296445
l_invoice_line_rec.reference_1, --NULL, -- reference_1
l_invoice_line_rec.reference_2, --NULL, -- reference_2
NULL, -- receipt_verified_flag
NULL, -- receipt_required_flag
NULL, -- receipt_missing_flag
NULL, -- justification
NULL, -- expense_group
NULL, -- start_expense_date
NULL, -- end_expense_date
NULL, -- receipt_currency_code
NULL, -- receipt_conversion_rate
NULL, -- receipt_currency_amount
NULL, -- daily_amount
NULL, -- web_parameter_id
NULL, -- adjustment_reason
l_invoice_line_rec.merchant_document_number, -- merchant_document_number --bug14335065 added merchant fields
l_invoice_line_rec.merchant_name, -- merchant_name
l_invoice_line_rec.merchant_reference, -- merchant_reference
l_invoice_line_rec.merchant_tax_reg_number, -- merchant_tax_reg_number
l_invoice_line_rec.merchant_taxpayer_id, -- merchant_taxpayer_id
NULL, -- country_of_supply
NULL, -- credit_card_trx_id
NULL, -- company_prepaid_invoice_id
NULL, -- cc_reversal_flag
-- Bug 6837035 Start
l_invoice_line_rec.attribute_category,-- attribute_category
l_invoice_line_rec.attribute1, -- attribute1
l_invoice_line_rec.attribute2, -- attribute2
l_invoice_line_rec.attribute3, -- attribute3
l_invoice_line_rec.attribute4, -- attribute4
l_invoice_line_rec.attribute5, -- attribute5
l_invoice_line_rec.attribute6, -- attribute6
l_invoice_line_rec.attribute7, -- attribute7
l_invoice_line_rec.attribute8, -- attribute8
l_invoice_line_rec.attribute9, -- attribute9
l_invoice_line_rec.attribute10, -- attribute10
l_invoice_line_rec.attribute11, -- attribute11
l_invoice_line_rec.attribute12, -- attribute12
l_invoice_line_rec.attribute13, -- attribute13
l_invoice_line_rec.attribute14, -- attribute14
l_invoice_line_rec.attribute15, -- attribute15
-- Bug 6837035 End
/* bug 8788072 changed from Null to decode */
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute_category, NULL), -- global_attribute_category
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute1, NULL), -- global_attribute1
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute2, NULL), -- global_attribute2
--bugfix:4674194
Decode(AP_EXTENDED_WITHHOLDING_PKG.AP_EXTENDED_WITHHOLDING_OPTION,
'Y',l_invoice_line_rec.ship_to_location_id,
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute3, NULL)), --global_attribute3
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute4, NULL), -- global_attribute4
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute5, NULL), -- global_attribute5
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute6, NULL), -- global_attribute6
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute7, NULL), -- global_attribute7
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute8, NULL), -- global_attribute8
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute9, NULL), -- global_attribute9
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute10, NULL), -- global_attribute10
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute11, NULL), -- global_attribute11
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute12, NULL), -- global_attribute12
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute13, NULL), -- global_attribute13
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute14, NULL), -- global_attribute14
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute15, NULL), -- global_attribute15
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute16, NULL), -- global_attribute16
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute17, NULL), -- global_attribute17
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute18, NULL), -- global_attribute18
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute19, NULL), -- global_attribute19
DECODE(l_copy_line_gdff_flag, 'Y', l_invoice_line_rec.global_attribute20, NULL), -- global_attribute20
/* bug 8788072 */
FND_GLOBAL.user_id, -- created_by
SYSDATE, -- creation_date
0, -- last_updated_by
SYSDATE, -- last_update_date
FND_GLOBAL.login_id, -- last_update_login
NULL, -- program_application_id
NULL, -- program_id
NULL, -- program_update_date
NULL, -- request_id
l_invoice_line_rec.primary_intended_use, -- intended_use
'N', -- rcv_charge_addition_flag
l_invoice_line_rec.pay_awt_group_id --pay_awt_group_id --bug7022001
) returning invoice_distribution_id into l_invoice_distribution_id;
SELECT JG_ZZ_SHARED_PKG.GET_COUNTRY(l_invoice_line_rec.org_id, null)
INTO l_country_code
FROM DUAL;
X_debug_info := debug_info || ': Error encountered during dist insert';
UPDATE AP_INVOICE_LINES
SET GENERATE_DISTS = 'D'
WHERE invoice_id = X_invoice_id
AND line_number = l_invoice_line_rec.line_number;
END insert_single_dist_from_line;
FUNCTION Insert_AWT_Dist_From_Line(
X_batch_id IN AP_INVOICES.BATCH_ID%TYPE,
X_invoice_id IN NUMBER,
X_invoice_date IN AP_INVOICES.INVOICE_DATE%TYPE,
X_vendor_id IN AP_INVOICES.VENDOR_ID%TYPE,
X_invoice_currency IN AP_INVOICES.INVOICE_CURRENCY_CODE%TYPE,
X_exchange_rate IN AP_INVOICES.EXCHANGE_RATE%TYPE,
X_exchange_rate_type IN AP_INVOICES.EXCHANGE_RATE_TYPE%TYPE,
X_exchange_date IN AP_INVOICES.EXCHANGE_DATE%TYPE,
X_line_number IN NUMBER,
X_invoice_lines_rec IN AP_INVOICES_PKG.r_invoice_line_rec,
X_line_source IN VARCHAR2,
X_Generate_Permanent IN VARCHAR2 DEFAULT 'N',
X_Validate_Info IN BOOLEAN DEFAULT TRUE,
X_Error_Code OUT NOCOPY VARCHAR2,
X_Debug_Info OUT NOCOPY VARCHAR2,
X_Debug_Context OUT NOCOPY VARCHAR2,
X_Msg_Application OUT NOCOPY VARCHAR2,
X_Msg_Data OUT NOCOPY VARCHAR2,
X_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
IS
CURSOR line_rec IS
SELECT 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
, DISCARDED_FLAG
, ORIGINAL_AMOUNT
, ORIGINAL_BASE_AMOUNT
, ORIGINAL_ROUNDING_AMT
, CANCELLED_FLAG
, INCOME_TAX_REGION
, TYPE_1099
, STAT_AMOUNT
, PREPAY_INVOICE_ID
, PREPAY_LINE_NUMBER
, INVOICE_INCLUDES_PREPAY_FLAG
, CORRECTED_INV_ID
, CORRECTED_LINE_NUMBER
, PO_HEADER_ID
, PO_LINE_ID
, PO_RELEASE_ID
, PO_LINE_LOCATION_ID
, PO_DISTRIBUTION_ID
, RCV_TRANSACTION_ID
, FINAL_MATCH_FLAG
, ASSETS_TRACKING_FLAG
, ASSET_BOOK_TYPE_CODE
, ASSET_CATEGORY_ID
, PROJECT_ID
, TASK_ID
, EXPENDITURE_TYPE
, EXPENDITURE_ITEM_DATE
, EXPENDITURE_ORGANIZATION_ID
, PA_QUANTITY
, PA_CC_AR_INVOICE_ID
, PA_CC_AR_INVOICE_LINE_NUM
, PA_CC_PROCESSED_CODE
, AWARD_ID
, AWT_GROUP_ID
, REFERENCE_1
, REFERENCE_2
, RECEIPT_VERIFIED_FLAG
, RECEIPT_REQUIRED_FLAG
, RECEIPT_MISSING_FLAG
, JUSTIFICATION
, EXPENSE_GROUP
, START_EXPENSE_DATE
, END_EXPENSE_DATE
, RECEIPT_CURRENCY_CODE
, RECEIPT_CONVERSION_RATE
, RECEIPT_CURRENCY_AMOUNT
, DAILY_AMOUNT
, WEB_PARAMETER_ID
, ADJUSTMENT_REASON
, MERCHANT_DOCUMENT_NUMBER
, MERCHANT_NAME
, MERCHANT_REFERENCE
, MERCHANT_TAX_REG_NUMBER
, MERCHANT_TAXPAYER_ID
, COUNTRY_OF_SUPPLY
, CREDIT_CARD_TRX_ID
, COMPANY_PREPAID_INVOICE_ID
, CC_REVERSAL_FLAG
, 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
, INCLUDED_TAX_AMOUNT
, PRIMARY_INTENDED_USE
, APPLICATION_ID
, PRODUCT_TABLE
, REFERENCE_KEY1
, REFERENCE_KEY2
, REFERENCE_KEY3
, REFERENCE_KEY4
, REFERENCE_KEY5
, SHIP_TO_LOCATION_ID
, PAY_AWT_GROUP_ID
FROM ap_invoice_lines_all
WHERE invoice_id = X_invoice_id
AND line_number = X_line_number;
select distinct accounting_event_id
from ap_invoice_distributions_all
where invoice_id = p_invoice_id
and invoice_line_number = p_invoice_line_num
and posted_flag <> 'Y';
'AP_INVOICE_DISTRIBUTIONS_PKG.insert_single_dist_from_line <-'||
X_calling_sequence;
SELECT DECODE(awt_include_tax_amt,'Y',1,'N',0,1)
INTO l_Inc_AWT_For_Tax_Flag
FROM ap_system_parameters_all
WHERE org_id=l_invoice_line_rec.org_id
AND rownum=1;
SELECT count(*)
INTO l_existing_distributions
FROM ap_invoice_distributions
WHERE invoice_id = l_invoice_line_rec.invoice_id
AND invoice_line_number = l_invoice_line_rec.line_number
AND nvl(posted_flag,'N') <> 'N'
AND rownum=1; --13507856 added the rownum condition to improve the performance
SELECT gsob.chart_of_accounts_id, ap.base_currency_code
INTO l_chart_of_accounts_id, l_base_currency_code
FROM ap_system_parameters ap, gl_sets_of_books gsob
WHERE ap.set_of_books_id = gsob.set_of_books_id
AND ap.set_of_books_id = l_invoice_line_rec.set_of_books_id
AND ap.org_id = l_invoice_line_rec.org_id; -- Bug 13858818
SELECT account_type
INTO l_account_type
FROM gl_code_combinations
WHERE code_combination_id = l_dist_ccid;
SELECT SUM(AMOUNT)
INTO l_dist_total
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = l_invoice_line_rec.invoice_id
AND line_type_lookup_code NOT IN('AWT' , 'PREPAY','ERV','TERV')
AND NOT (aid.line_type_lookup_code IN ('REC_TAX' , 'NONREC_TAX', 'TIPV', 'TRV')
AND aid.prepay_distribution_id is not null) /*Bug11937214*/
AND(l_Inc_AWT_For_Tax_Flag = 1
OR (aid.line_type_lookup_code NOT IN('REC_TAX' , 'NONREC_TAX' , 'TIPV' , 'TRV'))
) ;
select count(*)
into l_dists_exists
from ap_invoice_distributions_all
where invoice_id = l_invoice_line_rec.invoice_id
and accounting_event_id is NOT NULL
and line_type_lookup_code = 'AWT'
and awt_flag = 'M'
and nvl(posted_flag,'N') <> 'Y';
SELECT nvl(max(aid.accounting_event_id),-1)
INTO l_accounting_event_id
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = l_invoice_line_rec.invoice_id
AND aid.line_type_lookup_code = 'AWT'
AND aid.awt_flag = 'M'
AND NVL(posted_flag , 'N') <> 'Y';
DELETE FROM ap_invoice_distributions_all aid
WHERE invoice_id = l_invoice_line_rec.invoice_id
AND invoice_line_number = l_invoice_line_rec.line_number
AND NVL(posted_flag , 'N') <> 'Y';
SELECT count(*)
INTO l_no_dists_exists
FROM xla_events xe
WHERE xe.event_id = l_accounting_event_id_list(i)
AND xe.event_status_code <> 'P'
AND xe.application_id = 200
AND NOT EXISTS (SELECT 'No self assessed tax rows exists for this event'
FROM ap_self_assessed_tax_dist_all asatd
WHERE asatd.invoice_id = l_invoice_line_rec.invoice_id
AND asatd.accounting_event_id = xe.event_id)
AND NOT EXISTS (SELECT 'No Invoice rows exist for this event'
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = l_invoice_line_rec.invoice_id
AND aid.accounting_event_id = xe.event_id)
AND NOT EXISTS (SELECT 'No payment rows exist for this event'
FROM ap_invoice_payments_all aip,
ap_payment_history_all aph
WHERE aip.invoice_id = l_invoice_line_rec.invoice_id
AND aip.check_id = aph.check_id
AND aph.accounting_event_id = xe.event_id)
AND NOT EXISTS (SELECT 'No prepayment rows exist for this event'
FROM ap_prepay_history_all aprh
WHERE aprh.invoice_id = l_invoice_line_rec.invoice_id
AND aprh.accounting_event_id = xe.event_id);
AP_ACCOUNTING_EVENTS_PKG.delete_invoice_event(l_accounting_event_id_list(i),
l_invoice_line_rec.invoice_id,
current_calling_sequence);
debug_info := ' Before Insert AwT Distributions ';
INSERT INTO ap_invoice_distributions_all
( batch_id
, invoice_id
, invoice_line_number
, invoice_distribution_id
, distribution_line_number
, line_type_lookup_code
, distribution_class
, description
, dist_match_type
, org_id
, dist_code_combination_id
, accounting_date
, period_name
, accrual_posted_flag
, cash_posted_flag
, amount_to_post
, base_amount_to_post
, posted_amount
, posted_base_amount
, posted_flag
, accounting_event_id
, upgrade_posted_amt
, upgrade_base_posted_amt
, set_of_books_id
, amount
, base_amount
, rounding_amt
, quantity_variance
, base_quantity_variance
, match_status_flag
, encumbered_flag
, packet_id
, reversal_flag
, parent_reversal_id
, cancellation_flag
, income_tax_region
, type_1099
, stat_amount
, charge_applicable_to_dist_id
, prepay_amount_remaining
, prepay_distribution_id
, parent_invoice_id
, corrected_invoice_dist_id
, corrected_quantity
, other_invoice_id
, po_distribution_id
, rcv_transaction_id
, unit_price
, matched_uom_lookup_code
, quantity_invoiced
, final_match_flag
, related_id
, assets_addition_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_addition_flag
, award_id
, gms_burdenable_raw_cost
, awt_flag
, awt_group_id
, awt_tax_rate_id
, awt_gross_amount
, awt_invoice_id
, awt_origin_group_id
, awt_invoice_payment_id
, awt_withheld_amt
, inventory_transfer_status
, reference_1
, reference_2
, receipt_verified_flag
, receipt_required_flag
, receipt_missing_flag
, justification
, expense_group
, start_expense_date
, end_expense_date
, receipt_currency_code
, receipt_conversion_rate
, receipt_currency_amount
, daily_amount
, web_parameter_id
, adjustment_reason
, merchant_document_number
, merchant_name
, merchant_reference
, merchant_tax_reg_number
, merchant_taxpayer_id
, country_of_supply
, credit_card_trx_id
, company_prepaid_invoice_id
, cc_reversal_flag
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, 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
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, program_application_id
, program_id
, program_update_date
, request_id
, intended_use
, rcv_charge_addition_flag
, pay_awt_group_id
, awt_related_id
)
SELECT X_batch_id batch_id
, l_invoice_line_rec.invoice_id invoice_id
, l_invoice_line_rec.line_number invoice_line_number
, ap_invoice_distributions_s.nextval invoice_distribution_id
, rownum distribution_line_number
, 'AWT' line_type_lookup_code
, l_distribution_class distribution_class
, l_invoice_line_rec.description description
, 'NOT_MATCHED' dist_match_type
, l_invoice_line_rec.org_id l_org_id
, l_dist_ccid dist_code_combination_id
, l_open_gl_date accounting_date
, l_open_period_name period_name
, 'N' accrual_posted_flag
, 'N' cash_posted_flag
, NULL amount_to_post
, NULL base_amount_to_post
, NULL posted_amount
, NULL posted_base_amount
, 'N' posted_flag
/* bug 10201001
, DECODE(l_accounting_event_id,-1,NULL,
l_accounting_event_id) accounting_event_id bug 9216708 */
, NULL accounting_event_id
, NULL upgrade_posted_amt
, NULL upgrade_base_posted_amt
, l_invoice_line_rec.set_of_books_id set_of_books_id
, ap_utilities_pkg.ap_round_currency
(l_invoice_line_rec.amount * aid.amount / l_dist_total
, X_invoice_currency) amount
, DECODE(X_exchange_rate_type
, NULL , NULL
, ap_utilities_pkg.ap_round_currency(
(l_invoice_line_rec.amount * aid.amount / l_dist_total) * X_exchange_rate
, X_invoice_currency)) base_amount
, 0 rounding_amt
, NULL quantity_variance
, NULL base_quantity_variance
, NULL match_status_flag
, 'N' encumbered_flag
, NULL packet_id
, 'N' reversal_flag
, NULL parent_reversal_id
, 'N' cancellation_flag
, DECODE(l_invoice_line_rec.type_1099
, NULL , NULL
, l_invoice_line_rec.income_tax_region) income_tax_region
, l_invoice_line_rec.type_1099 type_1099
, NULL stat_amount
, NULL charge_applicable_to_dist_id
, NULL prepay_amount_remaining
, NULL prepay_distribution_id
, NULL parent_invoice_id
, NULL corrected_inv_dist_id
, NULL corrected_quantity
, NULL other_invoice_id
, NULL po_distribution_id
, NULL rcv_transaction_id
, NULL unit_price
, NULL matched_uom_lookup_code
, NULL quantity_invoiced
, NULL final_match_flag
, NULL related_id
, 'U' assets_addition_flag
, l_assets_tracking_flag assets_tracking_flag
, DECODE(l_assets_tracking_flag
, 'Y' , l_invoice_line_rec.asset_book_type_code
, NULL) asset_book_type_code
, DECODE(l_assets_tracking_flag
, 'Y' , l_invoice_line_rec.asset_category_id
, NULL) asset_category_id
, l_invoice_line_rec.project_id project_id
, l_invoice_line_rec.task_id task_id
, l_invoice_line_rec.expenditure_type expenditure_type
, l_invoice_line_rec.expenditure_item_date expenditure_item_date
, l_invoice_line_rec.expenditure_organization_id expenditure_organization_id
, l_invoice_line_rec.pa_quantity pa_quantity
, DECODE(l_invoice_line_rec.project_id , NULL , 'E' , 'N') pa_addition_flag
, l_invoice_line_rec.award_id award_id
, NULL gms_burdenable_raw_cost
, 'M' awt_flag
, l_invoice_line_rec.awt_group_id awt_group_id
, NULL awt_tax_rate_id
, NULL awt_gross_amount
, NULL awt_invoice_id
, NULL awt_origin_group_id
, NULL awt_invoice_payment_id
, NULL awt_withheld_amt
, 'N' inventory_transfer_status
, l_invoice_line_rec.reference_1 reference_1 --NULL Bug9296445
, l_invoice_line_rec.reference_2 reference_2 --NULL Bug9296445,
, NULL receipt_verified_flag
, NULL receipt_required_flag
, NULL receipt_missing_flag
, NULL justification
, NULL expense_group
, NULL start_expense_date
, NULL end_expense_date
, NULL receipt_currency_code
, NULL receipt_conversion_rate
, NULL receipt_currency_amount
, NULL daily_amount
, NULL web_parameter_id
, NULL adjustment_reason
, NULL merchant_document_number
, NULL merchant_name
, NULL merchant_reference
, NULL merchant_tax_reg_number
, NULL merchant_taxpayer_id
, NULL country_of_supply
, NULL credit_card_trx_id
, NULL company_prepaid_invoice_id
, NULL cc_reversal_flag
, l_invoice_line_rec.attribute_category attribute_category
, l_invoice_line_rec.attribute1 attribute1
, l_invoice_line_rec.attribute2 attribute2
, l_invoice_line_rec.attribute3 attribute3
, l_invoice_line_rec.attribute4 attribute4
, l_invoice_line_rec.attribute5 attribute5
, l_invoice_line_rec.attribute6 attribute6
, l_invoice_line_rec.attribute7 attribute7
, l_invoice_line_rec.attribute8 attribute8
, l_invoice_line_rec.attribute9 attribute9
, l_invoice_line_rec.attribute10 attribute10
, l_invoice_line_rec.attribute11 attribute11
, l_invoice_line_rec.attribute12 attribute12
, l_invoice_line_rec.attribute13 attribute13
, l_invoice_line_rec.attribute14 attribute14
, l_invoice_line_rec.attribute15 attribute15
, NULL global_attribute_category
, NULL global_attribute1
, NULL global_attribute2
, DECODE(AP_EXTENDED_WITHHOLDING_PKG.AP_EXTENDED_WITHHOLDING_OPTION
, 'Y' , l_invoice_line_rec.ship_to_location_id
, '') global_attribute3
, NULL global_attribute4
, NULL global_attribute5
, NULL global_attribute6
, NULL global_attribute7
, NULL global_attribute8
, NULL global_attribute9
, NULL global_attribute10
, NULL global_attribute11
, NULL global_attribute12
, NULL global_attribute13
, NULL global_attribute14
, NULL global_attribute15
, NULL global_attribute16
, NULL global_attribute17
, NULL global_attribute18
, NULL global_attribute19
, NULL global_attribute20
, FND_GLOBAL.user_id created_by
, SYSDATE creation_date
, 0 last_updated_by
, SYSDATE last_update_date
, FND_GLOBAL.login_id last_update_login
, NULL program_application_id
, NULL program_id
, NULL program_update_date
, NULL request_id
, l_invoice_line_rec.primary_intended_use intended_use
, 'N' rcv_charge_addition_flag
, l_invoice_line_rec.pay_awt_group_id pay_awt_group_id
, aid.invoice_distribution_id awt_related_id
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = l_invoice_line_rec.invoice_id
AND aid.line_type_lookup_code NOT IN('AWT' , 'PREPAY','ERV','TERV')
AND NOT (aid.line_type_lookup_code IN ('REC_TAX' , 'NONREC_TAX', 'TIPV', 'TRV')
AND aid.prepay_distribution_id is not null) /*Bug11937214*/
AND ( l_Inc_AWT_For_Tax_Flag = 1
OR ( aid.line_type_lookup_code NOT IN('REC_TAX' , 'NONREC_TAX' , 'TIPV' , 'TRV'))
) ;
debug_info := ' After Insert AwT Distributions ';
UPDATE ap_invoice_distributions_all aid
SET aid.amount = aid.amount -
((
SELECT SUM(aid1.amount)
FROM ap_invoice_distributions_all aid1
WHERE aid1.invoice_id = aid.invoice_id
AND aid1.invoice_line_number = aid.invoice_line_number
)- l_invoice_line_rec.amount)
WHERE aid.invoice_id = l_invoice_line_rec.invoice_id
AND aid.invoice_line_number = l_invoice_line_rec.line_number
AND ABS(aid.amount) =
( SELECT MAX(ABS(aid1.amount))
FROM ap_invoice_distributions_all aid1
WHERE aid1.invoice_id = aid.invoice_id
AND aid1.invoice_line_number = aid.invoice_line_number)
AND rownum = 1;
UPDATE ap_invoice_distributions_all aid
SET aid.base_amount = aid.base_amount -
((
SELECT SUM(aid1.base_amount)
FROM ap_invoice_distributions_all aid1
WHERE aid1.invoice_id = aid.invoice_id
AND aid1.invoice_line_number = aid.invoice_line_number
)- l_invoice_line_rec.base_amount)
WHERE aid.invoice_id = l_invoice_line_rec.invoice_id
AND aid.invoice_line_number = l_invoice_line_rec.line_number
AND ABS(aid.base_amount) =
( SELECT MAX(ABS(aid1.base_amount))
FROM ap_invoice_distributions_all aid1
WHERE aid1.invoice_id = aid.invoice_id
AND aid1.invoice_line_number = aid.invoice_line_number)
AND rownum = 1;
X_debug_info := debug_info || ': Error encountered during dist insert';
UPDATE AP_INVOICE_LINES
SET GENERATE_DISTS = 'D'
WHERE invoice_id = X_invoice_id
AND line_number = l_invoice_line_rec.line_number;
END Insert_AWT_Dist_From_Line;
SELECT employee_id
INTO l_employee_id
FROM ap_suppliers /* bUg 4718054 */
WHERE DECODE(SIGN(TO_DATE(TO_CHAR(START_DATE_ACTIVE,'DD-MM-YYYY'),
'DD-MM-YYYY') - TO_DATE(TO_CHAR(SYSDATE,'DD-MM-YYYY'),'DD-MM-YYYY')),
1, 'N', DECODE(SIGN(TO_DATE(TO_CHAR(END_DATE_ACTIVE ,'DD-MM-YYYY'),
'DD-MM-YYYY') - TO_DATE(TO_CHAR(SYSDATE,'DD-MM-YYYY'),'DD-MM-YYYY')),
-1, 'N', 0, 'N', 'Y')) = 'Y'
AND enabled_flag = 'Y'
AND vendor_id = l_vendor_id;
select default_code_comb_id
into l_employee_ccid
from PER_ASSIGNMENTS_F
where person_id = l_employee_id
and set_of_books_id = x_sob_id
and trunc(sysdate) BETWEEN trunc(effective_start_date)
and nvl(trunc(effective_end_date), trunc(sysdate));
select WEB_PARAMETER_ID, /* Bug 8713737 - added DFF columns*/
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
into l_web_parameter_id,
l_invoice_attribute_rec.line_attribute_category,
l_invoice_attribute_rec.line_attribute1 ,
l_invoice_attribute_rec.line_attribute2 ,
l_invoice_attribute_rec.line_attribute3 ,
l_invoice_attribute_rec.line_attribute4 ,
l_invoice_attribute_rec.line_attribute5 ,
l_invoice_attribute_rec.line_attribute6 ,
l_invoice_attribute_rec.line_attribute7 ,
l_invoice_attribute_rec.line_attribute8 ,
l_invoice_attribute_rec.line_attribute9 ,
l_invoice_attribute_rec.line_attribute10,
l_invoice_attribute_rec.line_attribute11,
l_invoice_attribute_rec.line_attribute12,
l_invoice_attribute_rec.line_attribute13,
l_invoice_attribute_rec.line_attribute14,
l_invoice_attribute_rec.line_attribute15
from ap_invoice_lines
where invoice_id = X_invoice_id
AND line_number = X_line_number;
SELECT invoice_type_lookup_code, /* Bug 8713737 - added DFF columns*/
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
INTO l_invoice_type_lookup_code,
l_invoice_attribute_rec.attribute_category,
l_invoice_attribute_rec.attribute1,
l_invoice_attribute_rec.attribute2,
l_invoice_attribute_rec.attribute3,
l_invoice_attribute_rec.attribute4,
l_invoice_attribute_rec.attribute5,
l_invoice_attribute_rec.attribute6,
l_invoice_attribute_rec.attribute7,
l_invoice_attribute_rec.attribute8,
l_invoice_attribute_rec.attribute9,
l_invoice_attribute_rec.attribute10,
l_invoice_attribute_rec.attribute11,
l_invoice_attribute_rec.attribute12,
l_invoice_attribute_rec.attribute13,
l_invoice_attribute_rec.attribute14,
l_invoice_attribute_rec.attribute15
FROM ap_invoices_all
WHERE invoice_id = X_invoice_id;
p_default_last_updated_by => FND_GLOBAL.user_id,
p_default_last_update_login => FND_GLOBAL.login_id,
p_pa_default_dist_ccid => l_dist_ccid,
p_pa_concatenated_segments => l_dist_code_concat, --OUT NOCOPY
p_debug_Info => debug_Info, -- OUT NOCOPY
p_debug_Context => debug_Context, --OUT NOCOPY
p_calling_sequence => 'Get_Proj_And_Acc_For_Chrg_Dist' ,
p_default_dist_ccid => X_default_dist_ccid --IN bug 5386396
))) THEN
debug_info := debug_info || ': Error encountered';
SELECT code_combination_id,
accrue_on_receipt_flag
INTO l_po_ccid,
l_po_accrue_on_receipt_flag
FROM po_distributions
WHERE po_distribution_id = X_item_po_dist_id;
SELECT account_type
INTO l_account_type
FROM gl_code_combinations
WHERE code_combination_id = l_dist_ccid;
FUNCTION Insert_Charge_From_Alloc(
X_invoice_id IN NUMBER,
X_line_number IN NUMBER,
X_Generate_Permanent IN VARCHAR2 DEFAULT 'N',
X_Validate_Info IN BOOLEAN DEFAULT TRUE,
X_Error_Code OUT NOCOPY VARCHAR2,
X_Debug_Info OUT NOCOPY VARCHAR2,
X_Debug_Context OUT NOCOPY VARCHAR2,
X_Msg_Application OUT NOCOPY VARCHAR2,
X_Msg_Data OUT NOCOPY VARCHAR2,
X_Calling_Sequence IN VARCHAR2) RETURN BOOLEAN
IS
CURSOR alloc_rule_lines_cur IS
SELECT ARL.to_invoice_line_number,
ARL.percentage,
ARL.amount,
Sum(AID.amount)
FROM AP_Allocation_Rule_Lines ARL,
AP_invoice_distributions_all AID
WHERE ARL.invoice_id = X_invoice_id
AND AID.invoice_id = X_invoice_id
AND ARL.chrg_invoice_line_number = X_line_number
AND AID.invoice_line_number = ARL.to_invoice_line_number
AND AID.line_type_lookup_code in ('ITEM', 'ACCRUAL', 'IPV', 'ERV')
--Invoice Lines: Distributions, Added the ARL.amount, ARL.percentage
--to the GROUP BY , without those cursor is INVALID
GROUP BY ARL.to_invoice_line_number, ARL.amount,ARL.percentage
ORDER BY ARL.to_invoice_line_number;
SELECT AIL.line_number,
AIL.amount,
sum(AID.amount)
FROM AP_invoice_lines AIL,
AP_invoice_distributions_all AID
WHERE AIL.invoice_id = X_invoice_id
AND nvl(AIL.discarded_flag, 'N') = 'N'
AND nvl(AIL.cancelled_flag, 'N') = 'N'
AND AIL.amount <> 0
AND AIL.line_type_lookup_code = 'ITEM'
AND nvl(AIL.match_type,'NOT_MATCHED') NOT IN
('PRICE_CORRECTION', 'QTY_CORRECTION','LINE_CORRECTION','AMOUNT_CORRECTION')
AND AID.invoice_line_number = AIL.line_number
AND AID.invoice_id = X_invoice_id
AND AID.line_type_lookup_code IN ('ITEM', 'ACCRUAL', 'IPV', 'ERV')
--Invoice Lines: Distributions, added AIL.amount to the GROUPBY clause
GROUP BY AIL.line_number,AIL.amount
ORDER BY AIL.line_number;
SELECT AID.invoice_distribution_id,
AID.po_distribution_id,
AID.rcv_transaction_id,
AID.project_id,
AID.task_id,
AID.pa_quantity, -- bug6699834
AID.expenditure_type,
AID.expenditure_organization_id,
AID.award_id,
AP_INVOICE_DISTRIBUTIONS_PKG.GET_TOTAL_DIST_AMOUNT(
aid.invoice_distribution_id),
AID.dist_code_combination_id,
AID.assets_tracking_flag,
AID.asset_book_type_code,
AID.asset_category_id,
AID.description
FROM ap_invoice_distributions_all AID
WHERE AID.invoice_id = X_invoice_id
AND AID.invoice_line_number = P_line_number
AND AID.line_type_lookup_code in ('ITEM', 'ACCRUAL')
ORDER BY AID.distribution_line_number;
l_last_update_login AP_INVOICE_LINES.LAST_UPDATE_LOGIN%TYPE;
l_dist_updated NUMBER; --Bug#10241241
'AP_INVOICE_DISTRIBUTIONS_PKG.insert_charge_from_alloc <-'
||X_calling_sequence;
SELECT org_id,
set_of_books_id,
default_dist_ccid,
overlay_dist_code_concat,
balancing_segment,
account_segment,
cost_center_segment,
project_id,
task_id,
award_id,
expenditure_type,
expenditure_organization_id,
assets_tracking_flag,
asset_book_type_code,
asset_category_id,
accounting_date,
amount,
base_amount,
line_type_lookup_code,
description,
awt_group_id,
type_1099,
income_tax_region,
created_by,
last_update_login,
pay_awt_group_id,
--Bug9296445
reference_1,
reference_2,
stat_amount, --10241241
-- Bug 13841774. Start.
NVL(line_source, 'DUMMY'),
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
-- Bug 13841774. End.
INTO l_org_id,
l_sob_id,
l_default_dist_ccid,
l_overlay_dist_concat,
l_balancing_segment,
l_account_segment,
l_cost_center_segment,
l_project_id,
l_task_id,
l_award_id,
l_expenditure_type,
l_expenditure_organization_id,
l_assets_tracking_flag,
l_asset_book_type_code,
l_asset_category_id,
l_accounting_date,
l_line_amount,
l_line_base_amount,
l_line_type_lookup_code,
l_line_description,
l_awt_group_id,
l_type_1099,
l_income_tax_region,
l_created_by,
l_last_update_login,
l_pay_awt_group_id,
--Bug9296445
l_reference_1,
l_reference_2,
l_stat_amount, --10241241
-- Bug 13841774. Start.
l_line_source,
l_attribute_category,
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_attribute11,
l_attribute12,
l_attribute13,
l_attribute14,
l_attribute15
-- Bug 13841774. End.
FROM ap_invoice_lines
WHERE invoice_id = X_invoice_id
AND line_number = X_line_number;
SELECT count(*)
INTO l_existing_distributions
FROM ap_invoice_distributions
WHERE invoice_id = X_invoice_id
AND invoice_line_number = X_line_number
AND rownum=1; --13507856 added the rownum condition to improve the performance
select ail.*
into l_chrg_line_rec
from ap_invoice_lines_all ail
where invoice_id = X_invoice_id
and line_number = X_line_number;
SELECT batch_id,
invoice_date,
vendor_id,
exchange_rate,
exchange_rate_type,
exchange_date,
invoice_currency_code,
invoice_amount
INTO l_batch_id,
l_invoice_date,
l_vendor_id,
l_exchange_rate,
l_exchange_rate_type,
l_exchange_date,
l_invoice_currency_code,
l_invoice_amount
FROM ap_invoices
WHERE invoice_id = X_invoice_id;
SELECT rule_type, status
INTO l_rule_type, l_rule_status
FROM ap_allocation_rules
WHERE invoice_id = X_invoice_id
AND chrg_invoice_line_number = X_line_number;
SELECT DECODE(NVL(ai.net_of_retainage_flag, 'N'),
'Y', (SUM(NVL(ail.amount, 0)) + SUM(NVL(retained_amount,0))),
SUM(ail.amount) )
INTO l_sum_lines_amount
FROM ap_invoice_lines_all ail,
ap_invoices_all ai
WHERE ai.invoice_id = ail.invoice_id
AND ail.invoice_id = X_invoice_id
AND ail.line_type_lookup_code NOT IN ('AWT')
/*bugfix:5685469*/
AND ((AIL.line_type_lookup_code <> 'TAX'
and (AIL.line_type_lookup_code NOT IN ('PREPAY')
or NVL(AIL.invoice_includes_prepay_flag,'N') = 'Y'
)
OR
(AIL.line_type_lookup_code = 'TAX'
and (AIL.prepay_invoice_id IS NULL
or (AIL.prepay_invoice_id is not null
and NVL(AIL.invoice_includes_prepay_flag, 'N') = 'Y'
)
)
)
)
)
group by ai.net_of_retainage_flag;
SELECT count(*)
INTO l_count_undistributed_lines
FROM ap_invoice_lines_all AIL
WHERE AIL.invoice_id = X_invoice_id
AND AIL.line_type_lookup_code = 'ITEM'
AND NVL(AIL.match_type,'NOT_MATCHED') NOT IN
('PRICE_CORRECTION', 'QTY_CORRECTION','LINE_CORRECTION','AMOUNT_CORRECTION')
--Bug 5558693
AND amount <>
(SELECT NVL(SUM(NVL(aid.amount,0)),0)
FROM ap_invoice_distributions_all AID
WHERE AID.invoice_id = ail.invoice_id
AND AID.invoice_line_number = AIL.line_number
AND (AID.line_type_lookup_code NOT IN ('RETAINAGE','PREPAY')
OR (AID.prepay_distribution_id IS NOT NULL
AND AID.line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX', 'TIPV', 'TRV', 'TERV')))
);
SELECT count(*)
INTO l_count_undistributed_lines
FROM ap_invoice_lines AIL, ap_allocation_rule_lines ARL
WHERE AIL.invoice_id = X_invoice_id
AND ARL.invoice_id = X_invoice_id
AND ARL.chrg_invoice_line_number = X_line_number
AND ARL.to_invoice_line_number = AIL.line_number
--Bug 5558693
AND AIL.amount <> (SELECT NVL(SUM(nvl(amount,0)),0)
FROM ap_invoice_distributions AID
WHERE AID.invoice_id = AIL.invoice_id
AND AID.invoice_line_number = AIL.line_number
AND (AID.line_type_lookup_code NOT IN ('RETAINAGE','PREPAY')
OR (AID.prepay_distribution_id IS NOT NULL
AND AID.line_type_lookup_code IN ('REC_TAX', 'NONREC_TAX', 'TIPV', 'TRV', 'TERV')))
);
SELECT gsob.chart_of_accounts_id, ap.base_currency_code,
ap.allow_flex_override_flag
INTO l_chart_of_accounts_id, l_base_currency_code,
l_allow_po_override
FROM ap_system_parameters ap, gl_sets_of_books gsob
WHERE ap.set_of_books_id = gsob.set_of_books_id
AND ap.org_id = l_org_id; -- Bug 13858818
SELECT JG_ZZ_SHARED_PKG.GET_COUNTRY(l_org_id, null)
INTO l_country_code
FROM DUAL;
l_dist_updated := 0; --Bug#10241241
SELECT ap_invoice_distributions_s.nextval INTO l_inv_dist_id FROM DUAL;
INSERT INTO ap_invoice_distributions(
batch_id,
invoice_id,
invoice_line_number,
invoice_distribution_id,
distribution_line_number,
line_type_lookup_code,
distribution_class,
description,
dist_match_type,
org_id,
dist_code_combination_id,
accounting_date,
period_name,
accrual_posted_flag,
cash_posted_flag,
amount_to_post,
base_amount_to_post,
posted_amount,
posted_base_amount,
posted_flag,
accounting_event_id,
upgrade_posted_amt,
upgrade_base_posted_amt,
set_of_books_id,
amount,
base_amount,
rounding_amt,
quantity_variance,
base_quantity_variance,
match_status_flag,
encumbered_flag,
packet_id,
reversal_flag,
parent_reversal_id,
cancellation_flag,
income_tax_region,
type_1099,
stat_amount,
charge_applicable_to_dist_id,
prepay_amount_remaining,
prepay_distribution_id,
parent_invoice_id,
corrected_invoice_dist_id,
corrected_quantity,
other_invoice_id,
po_distribution_id,
rcv_transaction_id,
unit_price,
matched_uom_lookup_code,
quantity_invoiced,
final_match_flag,
related_id,
assets_addition_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_addition_flag,
award_id,
gms_burdenable_raw_cost,
awt_flag,
awt_group_id,
awt_tax_rate_id,
awt_gross_amount,
awt_invoice_id,
awt_origin_group_id,
awt_invoice_payment_id,
awt_withheld_amt,
inventory_transfer_status,
reference_1,
reference_2,
receipt_verified_flag,
receipt_required_flag,
receipt_missing_flag,
justification,
expense_group,
start_expense_date,
end_expense_date,
receipt_currency_code,
receipt_conversion_rate,
receipt_currency_amount,
daily_amount,
web_parameter_id,
adjustment_reason,
merchant_document_number,
merchant_name,
merchant_reference,
merchant_tax_reg_number,
merchant_taxpayer_id,
country_of_supply,
credit_card_trx_id,
company_prepaid_invoice_id,
cc_reversal_flag,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
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,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id,
--ETAX: Invwkb
intended_use,
--Freight and Special Charges
rcv_charge_addition_flag,
---7022001
pay_awt_group_id)
SELECT l_batch_id, -- batch_id
X_invoice_id, -- invoice_id
X_line_number, -- invoice_line_number
l_inv_dist_id, -- invoice_distribution_id
l_dist_tab(i).dist_line_num, -- distribution_line_number
l_line_type_lookup_code, -- line_type_lookup_code
l_distribution_class, -- distribution_class
l_dist_tab(i).description, -- description
'NOT_MATCHED', -- dist_match_type
l_org_id, -- l_org_id
l_dist_tab(i).dist_ccid, -- dist_code_combination_id
l_open_gl_date, -- accounting_date
l_open_period_name, -- period_name
'N', -- accrual_posted_flag
'N', -- cash_posted_flag
NULL, -- amount_to_post
NULL, -- base_amount_to_post
NULL, -- posted_amount
NULL, -- posted_base_amount
'N', -- posted_flag
NULL, -- accounting_event_id
NULL, -- upgrade_posted_amt
NULL, -- upgrade_base_posted_amt
l_sob_id, -- set_of_books_id
l_dist_tab(i).amount, -- amount
l_dist_tab(i).base_amount, -- base_amount
l_dist_tab(i).rounding_amt, -- rounding_amt
NULL, -- quantity_variance
NULL, -- base_quantity_variance
--Invoice Lines: Distributions, changed match_status_flag
--to NULL from 'N'.
NULL, -- match_status_flag
'N', -- encumbered_flag
NULL, -- packet_id
'N', -- reversal_flag
NULL, -- parent_reversal_id
'N', -- cancellation_flag
decode(l_type_1099,null,null,l_income_tax_region),
-- income_tax_region
l_type_1099, -- type_1099
NULL, -- stat_amount
l_dist_tab(i).charge_applicable_to_dist, -- charge_applicable_to_dist_id
NULL, -- prepay_amount_remaining
NULL, -- prepay_distribution_id
NULL, -- parent_invoice_id
NULL, -- corrected_inv_dist_id
NULL, -- corrected_quantity
NULL, -- other_invoice_id
NULL, -- po_distribution_id
NULL, -- rcv_transaction_id
NULL, -- unit_price
NULL, -- matched_uom_lookup_code
NULL, -- quantity_invoiced
NULL, -- final_match_flag
NULL, -- related_id
'U', -- assets_addition_flag
l_dist_tab(i).assets_tracking_flag, -- assets_tracking_flag
l_dist_tab(i).asset_book_type_code, -- asset_book_type_code
l_dist_tab(i).asset_category_id, -- asset_category_id
l_dist_tab(i).project_id, -- project_id
l_dist_tab(i).task_id, -- task_id
l_dist_tab(i).expenditure_type, -- expenditure_type
l_dist_tab(i).expenditure_item_date, -- expenditure_item_date
l_dist_tab(i).expenditure_organization_id, -- expenditure_organization_id
/* commented for Bug fix 6699834. added below line as replacement NULL, -- pa_quantity */
l_dist_tab(i).pa_quantity, -- pa_quantity -- bug6699834
l_dist_tab(i).pa_addition_flag, -- pa_addition_flag
NULL, -- award_id
NULL, -- gms_burdenable_raw_cost
NULL, -- awt_flag
l_awt_group_id, -- awt_group_id
NULL, -- awt_tax_rate_id
NULL, -- awt_gross_amount
NULL, -- awt_invoice_id
NULL, -- awt_origin_group_id
NULL, -- awt_invoice_payment_id
NULL, -- awt_withheld_amt
'N', -- inventory_transfer_status
l_reference_1, -- NULL --reference_1 --Bug9296445
l_reference_2, -- NULL --reference_2 --Bug9296445
NULL, -- receipt_verified_flag
NULL, -- receipt_required_flag
NULL, -- receipt_missing_flag
NULL, -- justification
NULL, -- expense_group
NULL, -- start_expense_date
NULL, -- end_expense_date
NULL, -- receipt_currency_code
NULL, -- receipt_conversion_rate
NULL, -- receipt_currency_amount
NULL, -- daily_amount
NULL, -- web_parameter_id
NULL, -- adjustment_reason
NULL, -- merchant_document_number
NULL, -- merchant_name
NULL, -- merchant_reference
NULL, -- merchant_tax_reg_number
NULL, -- merchant_taxpayer_id
NULL, -- country_of_supply
NULL, -- credit_card_trx_id
NULL, -- company_prepaid_invoice_id
NULL, -- cc_reversal_flag
-- Bug 13841774. Start.
l_attribute_category, -- attribute_category
l_attribute1, -- attribute1
l_attribute2, -- attribute2
l_attribute3, -- attribute3
l_attribute4, -- attribute4
l_attribute5, -- attribute5
l_attribute6, -- attribute6
l_attribute7, -- attribute7
l_attribute8, -- attribute8
l_attribute9, -- attribute9
l_attribute10, -- attribute10
l_attribute11, -- attribute11
l_attribute12, -- attribute12
l_attribute13, -- attribute13
l_attribute14, -- attribute14
l_attribute15, -- attribute15
-- Bug 13841774. End.
NULL, -- global_attribute_category
NULL, -- global_attribute1
NULL, -- global_attribute2
--bugfix:4674194
decode(AP_EXTENDED_WITHHOLDING_PKG.AP_EXTENDED_WITHHOLDING_OPTION,
'Y',ail1.ship_to_location_id,''), --global_attribute3
NULL, -- global_attribute4
NULL, -- global_attribute5
NULL, -- global_attribute6
NULL, -- global_attribute7
NULL, -- global_attribute8
NULL, -- global_attribute9
NULL, -- global_attribute10
NULL, -- global_attribute11
NULL, -- global_attribute12
NULL, -- global_attribute13
NULL, -- global_attribute14
NULL, -- global_attribute15
NULL, -- global_attribute16
NULL, -- global_attribute17
NULL, -- global_attribute18
NULL, -- global_attribute19
NULL, -- global_attribute20
l_created_by, -- created_by
sysdate, -- creation_date
0, -- last_updated_by
sysdate, -- last_update_date
l_last_update_login, -- last_update_login
NULL, -- program_application_id
NULL, -- program_id
NULL, -- program_update_date
NULL, -- request_id
--ETAX: Invwkb
ail.primary_intended_use, -- intended_use
'N', -- rcv_charge_addition_flag
l_pay_awt_group_id -- pay_awt_group_id 7022001
FROM ap_invoice_lines AIL, --Charge line
ap_invoice_lines AIL1, --ITEM Line
ap_invoice_distributions aid
WHERE ail.invoice_id = X_invoice_id
AND ail.line_number = X_line_number
AND aid.invoice_id = ail.invoice_id
AND aid.invoice_distribution_id = l_dist_tab(i).charge_applicable_to_dist
AND ail1.invoice_id = ail.invoice_id
AND ail1.line_number = aid.invoice_line_number;
IF (l_dist_updated = 0 and
l_stat_amount is not null and
GET_UOM(l_dist_tab(i).dist_ccid,l_chart_of_accounts_id) is not null) THEN
Update ap_invoice_distributions
set stat_amount = l_stat_amount
where invoice_id = X_invoice_id
and invoice_line_number= X_line_number
and distribution_line_number = l_dist_tab(i).dist_line_num;
l_dist_updated := 1;
UPDATE AP_INVOICE_LINES
SET GENERATE_DISTS = 'D'
WHERE invoice_id = X_invoice_id
AND line_number = X_line_number
AND amount = (select sum(amount)
from ap_invoice_distributions
where invoice_id = x_invoice_id
and invoice_line_number = x_line_number);
UPDATE AP_ALLOCATION_RULES
SET STATUS = 'EXECUTED'
WHERE invoice_id = X_invoice_id
AND chrg_invoice_line_number = X_line_number
AND exists
(select 'Line Amount Equals Distribution Total'
from ap_invoice_lines_all ail,
ap_invoice_distributions_all aid
where ail.invoice_id = aid.invoice_id
and ail.line_number = aid.invoice_line_number
and ail.invoice_id = X_invoice_id
and ail.line_number = X_line_number
group by ail.line_number, ail.amount
having ail.amount = sum(aid.amount));
END insert_charge_from_alloc;
SELECT sum(nvl(AID.amount,0))
INTO l_original_amount
FROM ap_invoice_distributions_all AID
WHERE AID.invoice_distribution_id = X_invoice_distribution_id
OR AID.related_id = X_invoice_distribution_id;
SELECT nvl(distribution_line_number,0)
INTO l_dist_line_num
FROM ap_invoice_distributions
WHERE invoice_distribution_id = X_invoice_dist_id;
SELECT nvl(invoice_line_number,0)
INTO l_inv_line_num
FROM ap_invoice_distributions
WHERE invoice_distribution_id = X_invoice_dist_id;
SELECT nvl(ai.invoice_num,0)
INTO l_inv_num
FROM ap_invoice_distributions aid,
ap_invoices ai
WHERE aid.invoice_distribution_id = X_invoice_dist_id
AND aid.invoice_id = ai.invoice_id;
SELECT invoice_distribution_id
INTO l_distribution_id
FROM ap_invoice_distributions
WHERE parent_reversal_id = X_related_dist_id;
SELECT distribution_line_number
INTO l_reversing_dist_num
FROM ap_invoice_distributions
WHERE parent_reversal_id = x_invoice_dist_id;
| x_distribution_amt - Out parameter as updated value for this dist line
| x_dist_base_amt - Out parameter as updated value for this dist line
| x_ipv - Out parameter as calculated ipv
| x_bipv - Out parameter as calculated base amount for ipv
| x_erv - Out parameter as calculated erv in base currency code
| x_calling_sequence - calling sequence for debug purpose
| x_debug_switch - a control to log debug information
|
| PROGRAM FLOW
|
| KNOWN ISSUES
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Change
|
*============================================================================*/
FUNCTION Calculate_Variance(
X_DISTRIBUTION_ID IN NUMBER,
X_REPORTING_LEDGER_ID IN NUMBER,
X_DISTRIBUTION_AMT OUT NOCOPY NUMBER,
X_DIST_BASE_AMT OUT NOCOPY NUMBER,
X_IPV IN OUT NOCOPY NUMBER,
X_BIPV IN OUT NOCOPY NUMBER,
X_ERV IN OUT NOCOPY NUMBER,
X_DEBUG_INFO IN OUT NOCOPY VARCHAR2,
X_DEBUG_CONTEXT IN OUT NOCOPY VARCHAR2,
X_CALLING_SEQUENCE IN OUT NOCOPY VARCHAR2) Return Boolean
IS
l_inv_currency_code ap_invoices.invoice_currency_code%TYPE;
SELECT AI.invoice_currency_code, -- l_inv_currency_code
ASP.base_currency_code, -- l_base_currency_code
D.line_type_lookup_code, -- l_dist_line_type
D.unit_price, -- l_inv_price
nvl(D.quantity_invoiced, 0), -- l_quantity_invoiced
nvl(PD.quantity_ordered,0) -
nvl(PD.quantity_cancelled,0), -- l_po_qty
nvl(PLL.price_override, 0), -- l_po_price
decode(AI.invoice_currency_code,
ASP.base_currency_code, 1,
PD.rate), -- l_po_rate
decode (AI.invoice_currency_code,
ASP.base_currency_code, 1 ,
RTXN.currency_conversion_rate), -- l_rtxn_rate
nvl(AI.exchange_rate,1), -- l_inv_rate
nvl(PLL.match_option, 'P'), -- l_match_option
D.matched_uom_lookup_code, -- l_rtxn_uom
RSL.item_id, -- l_rtxn_item_id
PL.unit_meas_lookup_code, -- l_po_uom
D.amount, -- l_distribution_amt
decode(AI.invoice_currency_code,
ASP.base_currency_code, nvl(D.amount,0),
nvl(D.base_amount,0)), -- l_dist_base_amt
D.dist_match_type, -- l_dist_match_type
D.corrected_invoice_dist_id, -- l_corrected_invoice_dist_id
D.corrected_quantity, -- l_corrected_quantity
PLL.matching_basis, -- l_match_basis /* Amount Based Matching */
ail.line_source --Bug#10416960
FROM ap_invoices_all AI,
ap_system_parameters_all ASP,
ap_invoice_distributions D,
po_distributions PD,
po_lines PL,
po_line_types PLT, -- Amount Based Matching
po_line_locations PLL,
rcv_transactions RTXN,
rcv_shipment_lines RSL,
AP_INVOICE_LINES_ALL ail --bug#10416960
WHERE AI.invoice_id = D.invoice_id
AND D.invoice_distribution_id = X_distribution_id
AND AIL.invoice_id = D.invoice_id
AND AIL.line_number=D.invoice_line_number
AND nvl(ASP.org_id, -999) = nvl(AI.org_id,-999)
AND nvl(AI.org_id,-999) = nvl(D.org_id,-999)
AND D.po_distribution_id = PD.po_distribution_id
AND PL.po_header_id = PD.po_header_id
AND PL.po_line_id = PD.po_line_id
AND PL.line_type_id = PLT.line_type_id -- Amount Based Matching
AND PD.line_location_id = PLL.line_location_id
AND D.rcv_transaction_id = RTXN.transaction_id (+)
AND RTXN.shipment_line_id = RSL.shipment_line_id (+)
AND D.posted_flag in ('N', 'P')
AND nvl(D.encumbered_flag, 'N') in ('N', 'H', 'P')
--Modified below condition for bug#14360581
AND (D.line_type_lookup_code IN ('ITEM', 'ACCRUAL',
'RETROEXPENSE', 'RETROACCRUAL') --Retropricing
OR (D.line_type_lookup_code ='IPV'
AND D.corrected_invoice_dist_id IS NOT NULL))
--Bug#10416960
AND NVL(D.dist_match_type,'NOT_MATCHED') <> 'ADJUSTMENT_CORRECTION'
AND ( (NVL(D.dist_match_type,'NOT_MATCHED') = 'PO_PRICE_ADJUSTMENT'
AND ail.line_source = 'PO PRICE ADJUSTMENT')
OR NVL(D.dist_match_type,'NOT_MATCHED') <> 'PO_PRICE_ADJUSTMENT');
SELECT AI.invoice_currency_code, -- l_inv_currency_code
ASP.base_currency_code, -- l_base_currency_code
D.line_type_lookup_code, -- l_dist_line_type
D.unit_price, -- l_inv_price
nvl(D.quantity_invoiced, 0), -- l_quantity_invoiced
nvl(PD.quantity_ordered,0) -
nvl(PD.quantity_cancelled,0), -- l_po_qty
nvl(PLL.price_override, 0), -- l_po_price
decode(AI.invoice_currency_code,
ASP.base_currency_code, 1,
nvl(retain_ai.exchange_rate,1)), --l_po_rate bug 9242891 retained_inv_rate
decode (AI.invoice_currency_code,
ASP.base_currency_code, 1 ,
nvl(retain_ai.exchange_rate,1)), -- l_rtxn_rate --bug 9242891 retained_inv_rate
nvl(AI.exchange_rate,1), -- l_inv_rate
nvl(PLL.match_option, 'P'), -- l_match_option
D.matched_uom_lookup_code, -- l_rtxn_uom
RSL.item_id, -- l_rtxn_item_id
PL.unit_meas_lookup_code, -- l_po_uom
D.amount, -- l_distribution_amt
decode(AI.invoice_currency_code,
ASP.base_currency_code, nvl(D.amount,0),
nvl(D.base_amount,0)), -- l_dist_base_amt
D.dist_match_type, -- l_dist_match_type
D.corrected_invoice_dist_id, -- l_corrected_invoice_dist_id
D.corrected_quantity, -- l_corrected_quantity
PLL.matching_basis, -- l_match_basis /* Amount Based Matching */
NULL --line_source
FROM ap_invoices_all AI,
ap_invoices retain_ai, --bug 9242891 retained invoice
ap_system_parameters_all ASP,
ap_invoice_distributions D,
ap_invoice_distributions retain_d, --bug 9242891 retainage inv dists
po_distributions PD,
po_lines PL,
po_line_types PLT, -- Amount Based Matching
po_line_locations PLL,
rcv_transactions RTXN,
rcv_shipment_lines RSL
WHERE AI.invoice_id = D.invoice_id
AND AI.invoice_type_lookup_code = 'RETAINAGE RELEASE' --bug 9242891
AND D.retained_invoice_dist_id = retain_d.Invoice_Distribution_Id --bug 9242891
AND retain_ai.invoice_id = retain_D.invoice_id --bug 9242891
AND D.invoice_distribution_id = X_distribution_id
AND nvl(ASP.org_id, -999) = nvl(AI.org_id,-999)
AND nvl(AI.org_id,-999) = nvl(D.org_id,-999)
AND D.po_distribution_id = PD.po_distribution_id
AND PL.po_header_id = PD.po_header_id
AND PL.po_line_id = PD.po_line_id
AND PL.line_type_id = PLT.line_type_id -- Amount Based Matching
AND PD.line_location_id = PLL.line_location_id
AND D.rcv_transaction_id = RTXN.transaction_id (+)
AND RTXN.shipment_line_id = RSL.shipment_line_id (+)
AND D.posted_flag in ('N', 'P')
AND nvl(D.encumbered_flag, 'N') in ('N', 'H', 'P')
AND D.line_type_lookup_code = 'RETAINAGE' --bug 9242891
AND D.retained_invoice_dist_id is not NULL; --bug 9242891
SELECT COUNT(*)
INTO l_cnt
FROM AP_INVOICES_ALL ai,
AP_INVOICE_DISTRIBUTIONS aid
WHERE aid.Invoice_Distribution_Id = X_DISTRIBUTION_ID
AND ai.invoice_id = aid.invoice_id
AND ai.invoice_type_lookup_code = 'RETAINAGE RELEASE';
select nvl(AI.exchange_rate, 1)
into l_corrected_inv_rate
from AP_INVOICES AI,
AP_INVOICE_DISTRIBUTIONS D
where D.invoice_distribution_id = l_corrected_inv_dist_id
and D.invoice_id = AI.invoice_id;
select nvl(AI.exchange_rate, 1)
into l_corrected_inv_rate
from AP_INVOICES AI,
AP_INVOICE_DISTRIBUTIONS D
where D.invoice_distribution_id = l_corrected_inv_dist_id
and D.invoice_id = AI.invoice_id;
debug_info := 'Select from ap_invoice_distributions_all';
Select count(*)
Into dummy
From ap_invoice_distributions_all AID
Where NVL(AID.cancellation_flag, 'N' ) <> 'Y'
And NVL( AID.reversal_flag, 'N' ) <> 'Y'
And AID.corrected_invoice_dist_id = p_invoice_dist_id
And rownum < 2; --bug 5034678
debug_info := 'Select from ap_invoice_distributions_all';
Select count(*)
Into dummy
From ap_invoice_distributions_all AID
Where AID.charge_applicable_to_dist_id = p_invoice_dist_id
And NVL(AID.cancellation_flag, 'N') <> 'Y'
And NVL(AID.reversal_flag, 'N') <> 'Y';
Select count(*)
Into dummy_self
From ap_self_assessed_tax_dist_all AID
Where AID.charge_applicable_to_dist_id = p_invoice_dist_id
And NVL(AID.cancellation_flag, 'N') <> 'Y'
And NVL(AID.reversal_flag, 'N') <> 'Y';
'invoked from NegotiationAMImpl.handleDistributionDeleteAction';
'invoked from NegotiationAMImpl.handleDistributionDeleteAction';
'invoked from NegotiationAMImpl.handleDistributionDeleteAction';
UPDATE ap_invoice_distributions_all
SET distribution_class = 'PERMANENT'
WHERE invoice_id = p_invoice_id
AND invoice_line_number = NVL(p_invoice_line_number, invoice_line_number)
AND distribution_class = 'CANDIDATE'
RETURNING invoice_line_number BULK COLLECT INTO l_line_number_tab;
UPDATE ap_self_assessed_tax_dist_all
SET distribution_class = 'PERMANENT'
WHERE invoice_id = p_invoice_id
AND invoice_line_number = NVL(p_invoice_line_number, invoice_line_number)
AND distribution_class = 'CANDIDATE';
UPDATE ap_invoice_lines_all ail
SET generate_dists = 'D'
WHERE nvl(ail.generate_dists,'N') = 'Y'
AND invoice_id = p_invoice_id
AND ail.line_number = l_line_number_tab(uniq_values);
/*AND ail.amount = (SELECT SUM(NVL(aid.amount,0))
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_id = ail.invoice_id
AND aid.invoice_line_number = ail.line_number
AND aid.distribution_class = 'PERMANENT'); */
UPDATE AP_ALLOCATION_RULES ALR
SET STATUS = 'EXECUTED'
WHERE alr.invoice_id = p_invoice_id
AND alr.chrg_invoice_line_number = l_line_number_tab(uniq_values)
AND EXISTS (SELECT 'Valid charge line'
FROM ap_invoice_lines ail
WHERE ail.invoice_id = p_invoice_id --bug 5052657
AND ail.line_number = alr.chrg_invoice_line_number
AND nvl(ail.generate_dists,'N') = 'D'
AND ail.line_type_lookup_code IN ('FREIGHT','MISCELLANEOUS'));
SELECT sum(aid.amount)
INTO l_total_charge_amount
FROM ap_invoice_distributions aid
WHERE aid.invoice_id = p_invoice_id
AND aid.charge_applicable_to_dist_id = p_invoice_distribution_id;
SELECT 'Y' INTO L_AWT_COMPUTED
FROM DUAL WHERE EXISTS
(
SELECT INVOICE_DISTRIBUTION_ID
FROM AP_INVOICE_DISTRIBUTIONS
WHERE LINE_TYPE_LOOKUP_CODE='AWT'
AND INVOICE_ID=P_INVOICE_ID
AND AWT_RELATED_ID=P_DISTRIBUTION_ID
AND NVL(REVERSAL_FLAG,'N')='N'
);