[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT closing_status
INTO l_gl_period_status
FROM gl_period_statuses
WHERE application_id=200
AND set_of_books_id= p_set_of_books_id
AND to_date(p_source_date,'DD-MM-RRRR') BETWEEN start_date AND end_date
AND NVL(adjustment_period_flag, 'N') = 'N';
SELECT min(start_date)
INTO l_new_gl_date
FROM gl_period_statuses
WHERE application_id = 200
AND set_of_books_id = p_set_of_books_id
AND start_date > to_date(p_source_date,'DD-MM-RRRR')
AND (closing_status in ('O', 'F'))
AND NVL(adjustment_period_flag, 'N') = 'N';
PROCEDURE UpdateDistsWithReceiptInfo(p_report_header_id IN NUMBER, p_debug_switch IN VARCHAR2) IS
-------------------------------------------------------------------------------------------------
CURSOR c_report_lines_dists(l_report_header_id IN NUMBER) IS
SELECT xl.report_line_id, xl.currency_code,
xl.receipt_currency_code,
xl.receipt_conversion_rate,
xl.receipt_currency_amount,xd.amount ,
xd.report_distribution_id
FROM ap_expense_report_lines xl,
ap_exp_report_dists xd
WHERE xd.report_line_id = xl.report_line_id
and xl.report_header_id = l_report_header_id
and xd.report_header_id = l_report_header_id
and xd.receipt_currency_amount is null
order by xd.report_line_id, xd.report_distribution_id;
l_debug_info := 'Start UpdateDistsWithReceiptInfo';
-- When the line changes update the last distribution of the previous
-- line with the reminder.
--------------------------------------------------------------------
IF (l_prev_line_id <> 0 AND l_prev_line_id <> l_report_line_id) THEN
IF ( l_prev_ln_receipt_curr_amt - l_total_dist_rec_curr_amt <> 0 ) THEN
l_dist_rec_curr_amt := l_dist_rec_curr_amt + (l_prev_ln_receipt_curr_amt - l_total_dist_rec_curr_amt);
update ap_exp_report_dists set
receipt_currency_amount = l_dist_rec_curr_amt,
receipt_currency_code = l_receipt_currency_code,
receipt_conversion_rate = l_receipt_conversion_rate
where report_distribution_id = l_prev_dist_id;
update ap_exp_report_dists set
receipt_currency_amount = l_dist_rec_curr_amt,
receipt_currency_code = l_receipt_currency_code,
receipt_conversion_rate = l_receipt_conversion_rate
where report_distribution_id = l_report_distribution_id;
update ap_exp_report_dists set
receipt_currency_amount = l_dist_rec_curr_amt,
receipt_currency_code = l_receipt_currency_code,
receipt_conversion_rate = l_receipt_conversion_rate
where report_distribution_id = l_prev_dist_id;
l_debug_info := 'End UpdateDistsWithReceiptInfo';
l_debug_info := 'Exception in UpdateDistsWithReceiptInfo' || SQLERRM;
END UpdateDistsWithReceiptInfo;
SELECT employee_terms_id,
base_currency_code,
sp.set_of_books_id,
fp.non_recoverable_tax_flag,
nvl(sp.inv_doc_category_override, 'N'),
sp.gl_date_from_receipt_flag,
fp.expense_check_address_flag,
f.minimum_accountable_unit,
f.precision,
sp.employee_pay_group_lookup_code,
sp.employee_terms_id,
sp.apply_advances_default
FROM ap_system_parameters_all sp,
financials_system_parameters fp,
fnd_currencies f
WHERE sp.base_currency_code = f.currency_code
AND sp.org_id = l_org_id;
SELECT ai.invoice_id, aerh.report_header_id,
aerh.advance_invoice_to_apply, aerh.maximum_amount_to_apply
FROM ap_expense_report_headers_all aerh, ap_invoices_all ai
WHERE ai.APPLICATION_ID = 200
AND ai.PRODUCT_TABLE = 'AP_EXPENSE_REPORT_HEADERS_ALL'
AND ai.REFERENCE_KEY1 = aerh.report_header_id
AND aerh.invoice_num = ai.invoice_num
AND aerh.request_id = l_request_id
AND aerh.vouchno = 0;
SELECT to_number(aii.reference_key1) report_header_id,
reject_lookup_code,
aii.invoice_id
FROM ap_interface_rejections air, ap_invoices_interface aii
WHERE air.parent_table = 'AP_INVOICES_INTERFACE'
AND air.parent_id = aii.invoice_id
AND aii.request_id = l_request_id
UNION ALL
SELECT to_number(aii.reference_key1) report_header_id,
reject_lookup_code,
aii.invoice_id
FROM ap_interface_rejections air,
ap_invoices_interface aii,
ap_invoice_lines_interface aili
WHERE air.parent_table = 'AP_INVOICE_LINES_INTERFACE'
AND air.parent_id = aili.invoice_line_id
AND aii.invoice_id = aili.invoice_id
AND aii.request_id = l_request_id;
SELECT XH.report_header_id report_header_id,
nvl(emps.employee_id, -1) employee_id,
emps.employee_num employee_number,
XH.week_end_date week_end_date,
nvl(XH.invoice_num, '') invoice_num,
to_char(ap_utilities_pkg.ap_round_currency(XH.total,
XH.default_currency_code)) total,
nvl(XH.description, '') description,
substrb(rtrim(emps.last_name || ', ' || emps.first_name ||
DECODE(people.middle_names, null, '', ' ') ||
people.middle_names),
1,
240) name,
nvl(locs.location_code, '') location_code,
locs.address_line_1 address_line_1,
locs.address_line_2 address_line_2,
locs.address_line_3 address_line_3,
locs.town_or_city city,
decode(locs.STYLE,
'CA',
'',
'CA_GLB',
'',
nvl(locs.region_2, '')) state,
locs.postal_code postal_code,
decode(locs.STYLE,
'US',
'',
'US_GLB',
'',
'IE',
'',
'IE_GLB',
'',
'GB',
'',
'CA',
nvl(locs.REGION_1, ''),
'JP',
nvl(locs.REGION_1, ''),
nvl(AP_WEB_DB_EXPLINE_PKG.GetCountyProvince(locs.STYLE,
locs.REGION_1),
'')) province,
decode(locs.STYLE,
'US',
nvl(locs.REGION_1, ''),
'US_GLB',
nvl(locs.REGION_1, ''),
'IE',
nvl(AP_WEB_DB_EXPLINE_PKG.GetCountyProvince(locs.STYLE,
locs.REGION_1),
''),
'IE_GLB',
nvl(AP_WEB_DB_EXPLINE_PKG.GetCountyProvince(locs.STYLE,
locs.REGION_1),
''),
'GB',
nvl(AP_WEB_DB_EXPLINE_PKG.GetCountyProvince(locs.STYLE,
locs.REGION_1),
''),
'') county,
locs.country,
nvl(V.vendor_id, -1) vendor_id,
nvl(XH.vendor_id, -1) header_vendor_id,
--nvl(XH.hold_lookup_code, '') hold_lookup_code,
--nvl(l1.displayed_field, '') nls_hold_code,
--l1.description hold_description,
XH.created_by created_by,
XH.default_currency_code default_currency_code,
nvl(XH.default_exchange_rate_type, '') default_exchange_rate_type,
nvl(XH.default_exchange_rate,-1) default_exchange_rate,
nvl(to_char(XH.default_exchange_date), '') default_exchange_date,
nvl(XH.accts_pay_code_combination_id, -1) accts_pay_ccid,
XH.set_of_books_id set_of_books_id,
XH.accounting_date accounting_date,
nvl(XH.vendor_site_id, -1) header_vendor_site_id,
nvl(XH.apply_advances_default, 'N') apply_advances_flag,
nvl(XH.advance_invoice_to_apply, -1) advance_invoice_to_apply,
to_char(nvl(XH.maximum_amount_to_apply, XH.amt_due_employee)) amount_want_to_apply,
XH.expense_check_address_flag home_or_office,
nvl(emps.employee_id, -1) current_emp_id,
XH.voucher_num voucher_num,
'' base_amount,
nvl(XH.doc_category_code, '') doc_category_code,
nvl(XH.reference_1, '') reference_1,
XH.reference_2 reference_2,
nvl(to_char(XH.awt_group_id), '') awt_group_id,
XH.global_attribute1,
XH.global_attribute2,
XH.global_attribute3,
XH.global_attribute4,
XH.global_attribute5,
XH.global_attribute6,
XH.global_attribute7,
XH.global_attribute8,
XH.global_attribute9,
XH.global_attribute10,
XH.global_attribute11,
XH.global_attribute12,
XH.global_attribute13,
XH.global_attribute14,
XH.global_attribute15,
XH.global_attribute16,
XH.global_attribute17,
XH.global_attribute18,
XH.global_attribute19,
XH.global_attribute20,
XH.global_attribute_category,
nvl(decode(p_transfer_flag, 'Y', XH.attribute1), '') attribute1,
nvl(decode(p_transfer_flag, 'Y', XH.attribute2), '') attribute2,
nvl(decode(p_transfer_flag, 'Y', XH.attribute3), '') attribute3,
nvl(decode(p_transfer_flag, 'Y', XH.attribute4), '') attribute4,
nvl(decode(p_transfer_flag, 'Y', XH.attribute5), '') attribute5,
nvl(decode(p_transfer_flag, 'Y', XH.attribute6), '') attribute6,
nvl(decode(p_transfer_flag, 'Y', XH.attribute7), '') attribute7,
nvl(decode(p_transfer_flag, 'Y', XH.attribute8), '') attribute8,
nvl(decode(p_transfer_flag, 'Y', XH.attribute9), '') attribute9,
nvl(decode(p_transfer_flag, 'Y', XH.attribute10), '') attribute10,
nvl(decode(p_transfer_flag, 'Y', XH.attribute11), '') attribute11,
nvl(decode(p_transfer_flag, 'Y', XH.attribute12), '') attribute12,
nvl(decode(p_transfer_flag, 'Y', XH.attribute13), '') attribute13,
nvl(decode(p_transfer_flag, 'Y', XH.attribute14), '') attribute14,
nvl(decode(p_transfer_flag, 'Y', XH.attribute15), '') attribute15,
nvl(decode(p_transfer_flag, 'Y', XH.attribute_category), '') attribute_category,
nvl(XH.payment_currency_code, XH.default_currency_code) payment_currency_code,
nvl(XH.payment_cross_rate_type, '') payment_cross_rate_type,
nvl(XH.payment_cross_rate_date, XH.week_end_date) payment_cross_rate_date,
nvl(XH.payment_cross_rate, 1) payment_cross_rate,
nvl(XH.prepay_num, '') prepay_num,
nvl(XH.prepay_dist_num, '') prepay_dist_num,
nvl(to_char(XH.prepay_gl_date), '') prepay_gl_date,
nvl(xh.paid_on_behalf_employee_id, '') paid_on_behalf_employee_id,
to_char(nvl(xh.amt_due_employee, to_char(0))) amt_due_employee,
to_char(nvl(xh.amt_due_ccard_company, to_char(0))) amt_due_ccard_company,
substrb(rtrim(decode(people.per_information18,
null,
decode(people.per_information19,
null,
null,
people.per_information19),
people.per_information18 || ', ' ||
people.per_information19)),
1,
240) per_information18_19,
people.per_information_category per_information_category,
XH.source source,
p_group_id group_id,
locs.style style,
XH.org_id org_id,
'' invoice_id,
'' invoice_type_lookup_code,
'' gl_date,
'' alternate_name,
'' amount_app_to_discount,
V.payment_method_lookup_code
FROM ap_expense_report_headers XH,
hr_locations locs,
per_all_people_f people,
(SELECT
h.employee_id,
h.full_name,
h.employee_num,
h.organization_id,
h.last_name,
h.first_name,
h.business_group_id,
h.location_id
FROM per_employees_x h
WHERE AP_WEB_DB_HR_INT_PKG.isPersonCwk(h.employee_id)='N'
UNION ALL
SELECT
h.person_id employee_id,
h.full_name,
h.npw_number employee_num,
h.organization_id,
h.last_name,
h.first_name,
h.business_group_id,
h.location_id
FROM PER_CONT_WORKERS_CURRENT_X h) emps,
ap_suppliers V
--ap_lookup_codes l1
WHERE vouchno = 0
AND XH.employee_id = V.employee_id(+)
AND XH.employee_id = emps.employee_id(+)
AND (trunc(sysdate) between people.effective_start_date(+) AND
people.effective_end_date(+))
AND ((emps.business_group_id IS NULL) OR
(emps.business_group_id in
(SELECT nvl(FSP.business_group_id, 0)
FROM financials_system_parameters FSP)))
AND emps.employee_id = people.person_id(+)
AND emps.location_id = locs.location_id(+)
AND decode(XH.source,
'CREDIT CARD',
'SelfService',
'Both Pay',
'SelfService',
XH.source) = p_source
AND NVL(XH.expense_status_code, 'NO ERROR') not IN
('ERROR', 'PEND_HOLDS_CLEARANCE', 'HOLD_PENDING_RECEIPTS')
AND XH.hold_lookup_code is null
--AND l1.lookup_type(+) = 'HOLD CODE'
--AND l1.lookup_code(+) = XH.hold_lookup_code
AND ((XH.org_id IS NOT NULL AND
p_org_id IS NOT NULL AND
XH.org_id = p_org_id)
OR (p_org_id IS NULL AND
XH.org_id IS NOT NULL AND
(mo_global.check_access(XH.org_id)= 'Y'))
OR (p_org_id is NOT NULL AND XH.org_id IS NULL)
OR (p_org_id is NULL AND XH.org_id IS NULL))
AND EXISTS
(SELECT 'Y'
FROM AP_EXPENSE_REPORT_LINES XL
WHERE XH.REPORT_HEADER_ID = XL.REPORT_HEADER_ID)
ORDER BY UPPER(emps.last_name) desc,
UPPER(emps.first_name) desc,
UPPER(people.middle_names) desc,
total,
week_end_date desc;
--FOR UPDATE OF XH.report_header_id NOWAIT;
l_last_updated_by NUMBER;
g_last_updated_by := to_number(FND_GLOBAL.USER_ID);
g_last_update_login := to_number(FND_GLOBAL.LOGIN_ID);
SELECT 'Batch name is not unique'
INTO l_dummy
FROM ap_batches
WHERE batch_name = l_batch_name;
SELECT AP_BATCHES_S2.nextval INTO l_batch_id FROM dual;
UpdateDistsWithReceiptInfo(l_invoice_rec.report_header_id, g_debug_switch);
SELECT 'user defined document category exists'
INTO l_dummy
FROM fnd_doc_sequence_categories
WHERE code = l_invoice_rec.doc_category_code
AND (table_name = 'AP_INVOICES' OR
table_name = 'AP_INVOICES_ALL');
SELECT AP_INVOICES_INTERFACE_S.nextval
INTO l_invoice_rec.invoice_id
FROM DUAL;
SELECT 'Y'
INTO l_is_active_employee
FROM per_periods_of_service_v
WHERE person_id = l_invoice_rec.employee_id
AND trunc(sysdate) <= trunc(nvl(final_process_date, sysdate))
AND ROWNUM=1
ORDER BY LAST_UPDATE_DATE DESC;
SELECT nvl(sum(decode(payment_status_flag, 'Y',
decode(sign(earliest_settlement_date - sysdate),1,0,1),
0)), 0)
INTO l_available_prepays
FROM ap_invoices I,
ap_suppliers PV
WHERE exists (SELECT 'x'
FROM ap_invoice_distributions aid
WHERE aid.invoice_id = i.invoice_id
AND aid.line_type_lookup_code IN ('ITEM','TAX')
AND NVL(aid.reversal_flag,'N') <> 'Y'
AND nvl(aid.prepay_amount_remaining, aid.amount) > 0 )
AND I.vendor_id = PV.vendor_id
AND PV.employee_id = l_invoice_rec.employee_id
AND I.invoice_type_lookup_code = 'PREPAYMENT'
AND earliest_settlement_date IS NOT NULL
AND I.invoice_amount > 0
AND I.invoice_currency_code = l_invoice_rec.default_currency_code
AND PV.vendor_id = l_vendor_rec.vendor_id;
SELECT nvl(sum(decode(payment_status_flag, 'Y',
decode(sign(earliest_settlement_date - sysdate),1,0,1),
0)), 0)
INTO l_available_prepays
FROM ap_invoices I,
ap_suppliers PV
WHERE exists (SELECT 'x'
FROM ap_invoice_distributions aid
WHERE aid.invoice_id = i.invoice_id
AND aid.line_type_lookup_code IN ('ITEM','TAX')
AND NVL(aid.reversal_flag,'N') <> 'Y'
AND nvl(aid.prepay_amount_remaining, aid.amount) > 0 )
AND I.vendor_id = PV.vendor_id
AND PV.employee_id = l_invoice_rec.employee_id
AND I.invoice_type_lookup_code = 'PREPAYMENT'
AND earliest_settlement_date IS NOT NULL
AND I.invoice_amount > 0
AND I.invoice_currency_code = l_invoice_rec.default_currency_code;
UPDATE ap_expense_report_headers_all
SET apply_advances_default = 'N'
WHERE report_header_id = l_invoice_rec.report_header_id;
UPDATE ap_expense_report_headers_all
SET apply_advances_default = 'Y'
WHERE report_header_id = l_invoice_rec.report_header_id;
l_debug_info := 'Insert into AP Invoices Interface table';
InsertInvoiceInterface(l_invoice_rec, l_vendor_rec);
InsertInvoiceLinesInterface(l_invoice_rec.report_header_id, l_invoice_rec.invoice_id,
p_transfer_flag, l_base_currency, l_enable_recoverable_flag);
l_debug_info := 'Update request_id';
UPDATE ap_expense_report_headers_all
SET request_id = l_request_id
WHERE report_header_id = l_invoice_rec.report_header_id;
UPDATE ap_expense_report_headers_all
SET reject_code = l_reject_code,
request_id = l_request_id,
vouchno = 0
WHERE report_header_id = l_invoice_rec.report_header_id;
UPDATE ap_expense_report_headers_all
SET vouchno = l_invoice_id_list(i),
reject_code = null
WHERE report_header_id = l_report_header_id_list(i);
SELECT abs(nvl(amount,0))
INTO l_actual_adv_applied
FROM ap_invoice_lines_all
WHERE invoice_id = l_invoice_id_list(i)
AND line_type_lookup_code = 'PREPAY'
AND prepay_invoice_id = l_oie_applied_prepay_list(i);
UPDATE ap_expense_report_headers_all
SET maximum_amount_to_apply = l_actual_adv_applied,
amt_due_employee = ( nvl(amt_due_employee,0) + (l_oie_applied_amt_list(i) - l_actual_adv_applied) )
WHERE report_header_id = l_report_header_id_list(i);
l_debug_info := 'Updated ap_expense_report_headers_all for report_header_id = '||to_char(l_report_header_id_list(i))||' with maximum_amount_to_apply = '||to_char(l_actual_adv_applied);
SELECT sum(amount)
INTO l_inv_total_amount
FROM ap_invoice_lines_all ap1
WHERE invoice_id = l_invoice_id_list(i);
SELECT payment_due_from_code
INTO l_payment_due_frm
FROM ap_credit_card_trxns_all trx
WHERE trx.report_header_id =l_report_header_id_list(i)
AND trx.category ='BUSINESS'
AND rownum = 1;
UPDATE ap_expense_report_headers_all ah
SET expense_status_code = l_expense_status_code
WHERE report_header_id = l_report_header_id_list(i) ;
l_invoice_id_list.DELETE;
l_report_header_id_list.DELETE;
UPDATE ap_expense_report_headers_all
SET reject_code = l_reject_code_list(i), vouchno = 0
WHERE report_header_id = l_report_header_id_list(i)
and nvl(vouchno,0) = 0;
DELETE FROM ap_interface_rejections
WHERE parent_table = 'AP_INVOICES_INTERFACE'
AND parent_id = l_invoice_id_list(i);
DELETE FROM ap_interface_rejections
WHERE parent_table = 'AP_INVOICE_LINES_INTERFACE'
AND parent_id IN
(SELECT invoice_line_id
FROM ap_invoice_lines_interface
WHERE invoice_id = l_invoice_id_list(i));
DELETE FROM ap_invoices_interface
WHERE invoice_id = l_invoice_id_list(i);
DELETE FROM ap_invoice_lines_interface
WHERE invoice_id = l_invoice_id_list(i);
SELECT xl.report_header_id,
xl.report_line_id,
gcc.code_combination_id code_combination_id,
nvl(lc.lookup_code, '') line_type_lookup_code,
nvl(xl.vat_code, '') line_vat_code,
nvl(xl.tax_code_id, -1) line_tax_code_id,
SIGN(nvl(amount, 0)) distribution_amount_sign,
SIGN(nvl(stat_amount, 0)) stat_amount_sign,
to_char(nvl(xl.stat_amount, '')) stat_amount,
xl.set_of_books_id line_set_of_books_id,
to_char(nvl(ap_utilities_pkg.ap_round_currency(xl.amount,
XH.default_currency_code),
0)) distribution_amount,
nvl(xl.item_description, '') item_description,
xl.line_type_lookup_code db_line_type,
xl.distribution_line_number,
to_char(decode(p_base_currency,
xh.default_currency_code,
null,
DECODE(F.minimum_accountable_unit,
'',
ROUND(ap_utilities_pkg.ap_round_currency(xl.amount,
XH.default_currency_code) *
xh.default_exchange_rate,
F.precision),
ROUND(ap_utilities_pkg.ap_round_currency(xl.amount,
XH.default_currency_code) *
xh.default_exchange_rate /
F.minimum_accountable_unit) *
F.minimum_accountable_unit))) base_amount,
DECODE(nvl(gcc.account_type, 'x'), 'A', 'Y', 'N') assets_tracking_flag,
nvl(decode(p_transfer_flag, 'Y', xl.attribute1), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute2), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute3), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute4), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute5), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute6), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute7), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute8), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute9), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute10), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute11), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute12), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute13), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute14), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute15), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute_category), ''),
nvl(xl.project_accounting_context, ''),
nvl(to_char(xl.project_id), ''),
nvl(to_char(xl.task_id), ''),
nvl(to_char(xl.expenditure_organization_id), ''),
nvl(xl.expenditure_type, ''),
nvl(to_char(xl.expenditure_item_date), ''),
nvl(to_char(xl.pa_quantity), ''),
nvl(xl.reference_1, ''),
nvl(xl.reference_2, ''),
nvl(to_char(xl.awt_group_id), ''),
xl.amount_includes_tax_flag,
nvl(xl.tax_code_override_flag, 'N'),
'' tax_recovery_rate,
'N' tax_recovery_override_flag,
nvl(decode(p_enable_recoverable_flag,
'Y',
decode(xl.line_type_lookup_code, 'TAX', 'Y', 'N'),
'N'),
'N') tax_recoverable_flag,
xl.global_attribute1,
xl.global_attribute2,
xl.global_attribute3,
xl.global_attribute4,
xl.global_attribute5,
xl.global_attribute6,
xl.global_attribute7,
xl.global_attribute8,
xl.global_attribute9,
xl.global_attribute10,
xl.global_attribute11,
xl.global_attribute12,
xl.global_attribute13,
xl.global_attribute14,
xl.global_attribute15,
xl.global_attribute16,
xl.global_attribute17,
xl.global_attribute18,
xl.global_attribute19,
xl.global_attribute20,
xl.global_attribute_category,
nvl(xl.receipt_verified_flag, ''),
nvl(xl.receipt_required_flag, ''),
nvl(xl.receipt_missing_flag, ''),
nvl(xl.justification, ''),
nvl(xl.expense_group, ''),
to_char(nvl(xl.start_expense_date, '')),
to_char(nvl(xl.start_expense_date, xh.week_end_date)) start_expense_date2,
to_char(nvl(xl.end_expense_date, '')),
nvl(xl.merchant_document_number, ''),
nvl(xl.merchant_name, ''),
nvl(xl.merchant_reference, ''),
nvl(xl.merchant_tax_reg_number, ''),
nvl(xl.merchant_taxpayer_id, ''),
nvl(xl.country_of_supply, ''),
nvl(xl.receipt_currency_code, ''),
to_char(nvl(xl.receipt_conversion_rate, '')),
to_char(nvl(xl.receipt_currency_amount, '')),
to_char(nvl(xl.daily_amount, '')),
to_char(nvl(xl.web_parameter_id, '')),
nvl(xl.adjustment_reason, ''),
nvl(xl.credit_card_trx_id, ''),
nvl(xl.company_prepaid_invoice_id, ''),
xl.created_by,
'' pa_addition_flag,
'' type_1099,
'' income_tax_region,
'' award_id,
'' invoice_id,
'' accounting_date,
XL.org_id org_id
FROM ap_expense_report_lines XL,
gl_code_combinations gcc,
ap_lookup_codes lc,
fnd_currencies F,
ap_expense_report_headers XH
WHERE XL.report_header_id = XH.report_header_id
AND XH.report_header_id = p_report_header_id
AND XL.code_combination_id = gcc.code_combination_id(+)
AND nvl(XL.itemization_parent_id,0) <> -1 /* Itemization Project */
AND lc.lookup_code(+) = XL.line_type_lookup_code
AND lc.lookup_type(+) = 'INVOICE DISTRIBUTION TYPE'
AND F.currency_code = p_base_currency;
PROCEDURE InsertInvoiceInterface(p_invoice_rec InvoiceInfoRecType,
p_vendor_rec VendorInfoRecType) IS
------------------------------------------------------------------------
BEGIN
INSERT INTO AP_INVOICES_INTERFACE
(INVOICE_ID,
APPLICATION_ID,
PRODUCT_TABLE,
REFERENCE_KEY1,
INVOICE_NUM,
INVOICE_TYPE_LOOKUP_CODE,
INVOICE_DATE,
VENDOR_ID,
VENDOR_NUM,
VENDOR_NAME,
VENDOR_SITE_ID,
VENDOR_SITE_CODE,
INVOICE_AMOUNT,
INVOICE_CURRENCY_CODE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
EXCHANGE_DATE,
TERMS_ID,
TERMS_NAME,
DESCRIPTION,
AWT_GROUP_ID,
AWT_GROUP_NAME,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
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,
STATUS,
SOURCE,
GROUP_ID,
REQUEST_ID,
PAYMENT_CROSS_RATE_TYPE,
PAYMENT_CROSS_RATE_DATE,
PAYMENT_CROSS_RATE,
PAYMENT_CURRENCY_CODE,
WORKFLOW_FLAG,
DOC_CATEGORY_CODE,
VOUCHER_NUM,
PAY_GROUP_LOOKUP_CODE,
GOODS_RECEIVED_DATE,
INVOICE_RECEIVED_DATE,
GL_DATE,
ACCTS_PAY_CODE_COMBINATION_ID,
ORG_ID,
AMOUNT_APPLICABLE_TO_DISCOUNT,
PREPAY_NUM,
PREPAY_LINE_NUM,
PREPAY_APPLY_AMOUNT,
PREPAY_GL_DATE,
INVOICE_INCLUDES_PREPAY_FLAG,
NO_XRATE_BASE_AMOUNT,
VENDOR_EMAIL_ADDRESS,
TERMS_DATE,
REQUESTER_ID,
PAID_ON_BEHALF_EMPLOYEE_ID,
PARTY_ID,
PARTY_SITE_ID)
VALUES
(p_invoice_rec.invoice_id,
200,
'AP_EXPENSE_REPORT_HEADERS_ALL',
p_invoice_rec.report_header_id,
p_invoice_rec.invoice_num,
p_invoice_rec.invoice_type_lookup_code,
p_invoice_rec.week_end_date,
p_invoice_rec.vendor_id,
'',
'',
p_vendor_rec.vendor_site_id,
'',
p_invoice_rec.total,
p_invoice_rec.default_currency_code,
decode(p_invoice_rec.default_exchange_rate,-1,''),
p_invoice_rec.default_exchange_rate_type,
p_invoice_rec.default_exchange_date,
p_vendor_rec.terms_id,
'',
p_invoice_rec.description,
p_invoice_rec.awt_group_id,
'',
sysdate,
g_last_updated_by,
'',
sysdate,
p_invoice_rec.created_by,
p_invoice_rec.attribute_category,
p_invoice_rec.attribute1,
p_invoice_rec.attribute2,
p_invoice_rec.attribute3,
p_invoice_rec.attribute4,
p_invoice_rec.attribute5,
p_invoice_rec.attribute6,
p_invoice_rec.attribute7,
p_invoice_rec.attribute8,
p_invoice_rec.attribute9,
p_invoice_rec.attribute10,
p_invoice_rec.attribute11,
p_invoice_rec.attribute12,
p_invoice_rec.attribute13,
p_invoice_rec.attribute14,
p_invoice_rec.attribute15,
p_invoice_rec.global_attribute_category,
p_invoice_rec.global_attribute1,
p_invoice_rec.global_attribute2,
p_invoice_rec.global_attribute3,
p_invoice_rec.global_attribute4,
p_invoice_rec.global_attribute5,
p_invoice_rec.global_attribute6,
p_invoice_rec.global_attribute7,
p_invoice_rec.global_attribute8,
p_invoice_rec.global_attribute9,
p_invoice_rec.global_attribute10,
p_invoice_rec.global_attribute11,
p_invoice_rec.global_attribute12,
p_invoice_rec.global_attribute13,
p_invoice_rec.global_attribute14,
p_invoice_rec.global_attribute15,
p_invoice_rec.global_attribute16,
p_invoice_rec.global_attribute17,
p_invoice_rec.global_attribute18,
p_invoice_rec.global_attribute19,
p_invoice_rec.global_attribute20,
'',
decode(p_invoice_rec.source,'CREDIT CARD',
'SelfService',
'Both Pay',
'SelfService',
p_invoice_rec.source),
p_invoice_rec.group_id,
FND_GLOBAL.CONC_REQUEST_ID,
p_invoice_rec.payment_cross_rate_type,
p_invoice_rec.payment_cross_rate_date,
p_invoice_rec.payment_cross_rate,
p_invoice_rec.payment_currency_code,
'',
p_invoice_rec.doc_category_code,
p_invoice_rec.voucher_num,
p_vendor_rec.pay_group,
'',
'',
p_invoice_rec.gl_date,
nvl(decode(p_invoice_rec.accts_pay_ccid, -1, p_vendor_rec.liab_acc), p_vendor_rec.liab_acc),
p_invoice_rec.org_id,
p_invoice_rec.amount_app_to_discount,
decode(p_invoice_rec.apply_advances_flag,
'Y',
p_invoice_rec.prepay_num,
''),
decode(p_invoice_rec.apply_advances_flag,
'Y',
p_invoice_rec.prepay_dist_num,
''),
decode(p_invoice_rec.apply_advances_flag,
'Y',
p_invoice_rec.amount_want_to_apply,
''),
p_invoice_rec.prepay_gl_date,
'',
'',
'',
decode(p_vendor_rec.terms_date_basis,
'Current',
sysdate,
p_invoice_rec.week_end_date),
'',
p_invoice_rec.paid_on_behalf_employee_id,
p_vendor_rec.party_id,
decode(p_invoice_rec.invoice_type_lookup_code,
'PAYMENT REQUEST',
p_vendor_rec.party_site_id,
''));
END InsertInvoiceInterface;
PROCEDURE InsertInvoiceLinesInterface(p_report_header_id IN NUMBER,
p_invoice_id IN NUMBER,
p_transfer_flag IN VARCHAR2,
p_base_currency IN VARCHAR2,
p_enable_recoverable_flag IN VARCHAR2) IS
------------------------------------------------------------------------------------------
l_debug_info VARCHAR2(2000);
l_debug_info := 'Insert into Invoice Lines Interface, p_invoice_id ' || p_invoice_id || ', p_report_header_id ' || p_report_header_id || ', p_transfer_flag ' || p_transfer_flag || ', p_enable_recoverable_flag ' || p_enable_recoverable_flag;
INSERT INTO AP_INVOICE_LINES_INTERFACE
(INVOICE_ID,
APPLICATION_ID,
PRODUCT_TABLE,
REFERENCE_KEY1,
REFERENCE_KEY2,
INVOICE_LINE_ID,
LINE_NUMBER,
LINE_TYPE_LOOKUP_CODE,
LINE_GROUP_NUMBER,
AMOUNT,
ACCOUNTING_DATE,
DESCRIPTION,
AMOUNT_INCLUDES_TAX_FLAG,
TAX_CLASSIFICATION_CODE,
ITEM_DESCRIPTION,
DIST_CODE_COMBINATION_ID,
AWT_GROUP_ID,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
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,
PROJECT_ID,
TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
EXPENDITURE_ORGANIZATION_ID,
PROJECT_ACCOUNTING_CONTEXT,
PA_ADDITION_FLAG,
PA_QUANTITY,
STAT_AMOUNT,
TYPE_1099,
INCOME_TAX_REGION,
ASSETS_TRACKING_FLAG,
ORG_ID,
REFERENCE_1,
REFERENCE_2,
TAX_RECOVERY_RATE,
TAX_RECOVERY_OVERRIDE_FLAG,
TAX_RECOVERABLE_FLAG,
TAX_CODE_OVERRIDE_FLAG,
TAX_CODE_ID,
CREDIT_CARD_TRX_ID,
AWARD_ID,
TAXABLE_FLAG,
COMPANY_PREPAID_INVOICE_ID,
EXPENSE_GROUP,
JUSTIFICATION,
MERCHANT_DOCUMENT_NUMBER,
MERCHANT_NAME,
MERCHANT_REFERENCE,
MERCHANT_TAXPAYER_ID,
MERCHANT_TAX_REG_NUMBER,
RECEIPT_CONVERSION_RATE,
RECEIPT_CURRENCY_AMOUNT,
RECEIPT_CURRENCY_CODE,
COUNTRY_OF_SUPPLY)
(SELECT
p_invoice_id,
200,
'AP_EXPENSE_REPORT_LINES_ALL',
xl.report_header_id,
xl.report_line_id,
AP_INVOICE_LINES_INTERFACE_S.nextval,
xl.distribution_line_number,
nvl(lc.lookup_code, '') line_type_lookup_code,
'',
to_char(nvl(ap_utilities_pkg.ap_round_currency(xl.amount,
XH.default_currency_code),
0)) distribution_amount,
'' accounting_date,
nvl(xl.item_description, '') item_description,
xl.amount_includes_tax_flag,
nvl(xl.vat_code, '') line_vat_code,
nvl(xl.item_description, '') item_description,
gcc.code_combination_id code_combination_id,
nvl(to_char(xl.awt_group_id), ''),
g_last_updated_by,
sysdate,
g_last_update_login,
xl.created_by,
sysdate,
nvl(decode(p_transfer_flag, 'Y', xl.attribute_category), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute1), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute2), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute3), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute4), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute5), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute6), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute7), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute8), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute9), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute10), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute11), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute12), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute13), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute14), ''),
nvl(decode(p_transfer_flag, 'Y', xl.attribute15), ''),
xl.global_attribute_category,
xl.global_attribute1,
xl.global_attribute2,
xl.global_attribute3,
xl.global_attribute4,
xl.global_attribute5,
xl.global_attribute6,
xl.global_attribute7,
xl.global_attribute8,
xl.global_attribute9,
xl.global_attribute10,
xl.global_attribute11,
xl.global_attribute12,
xl.global_attribute13,
xl.global_attribute14,
xl.global_attribute15,
xl.global_attribute16,
xl.global_attribute17,
xl.global_attribute18,
xl.global_attribute19,
xl.global_attribute20,
nvl(to_char(xl.project_id), ''),
nvl(to_char(xl.task_id), ''),
nvl(xl.expenditure_type, ''),
nvl(to_char(xl.expenditure_item_date), ''),
nvl(to_char(xl.expenditure_organization_id), ''),
nvl(xl.project_accounting_context, ''),
nvl2(xl.project_id, 'T', 'E') pa_addition_flag,
nvl(to_char(xl.pa_quantity), ''),
to_char(nvl(xl.stat_amount, '')) stat_amount,
'' type_1099,
'' income_tax_region,
DECODE(nvl(gcc.account_type, 'x'), 'A', 'Y', 'N') assets_tracking_flag,
XL.org_id org_id,
nvl(xl.reference_1, ''),
nvl(xl.reference_2, ''),
'' tax_recovery_rate,
'N' tax_recovery_override_flag,
nvl(decode(p_enable_recoverable_flag,
'Y',
decode(xl.line_type_lookup_code, 'TAX', 'Y', 'N'),
'N'),
'N') tax_recoverable_flag,
nvl(xl.tax_code_override_flag, 'N'),
'',
nvl(xl.credit_card_trx_id, ''),
'' award_id,
'',
nvl(xl.company_prepaid_invoice_id, ''),
nvl(xl.expense_group, ''),
nvl(xl.justification, ''),
nvl(xl.merchant_document_number, ''),
nvl(xl.merchant_name, ''),
nvl(xl.merchant_reference, ''),
nvl(xl.merchant_taxpayer_id, ''),
nvl(xl.merchant_tax_reg_number, ''),
to_char(nvl(xl.receipt_conversion_rate, '')),
to_char(nvl(xl.receipt_currency_amount, '')),
nvl(xl.receipt_currency_code, ''),
nvl(xl.country_of_supply, '')
FROM ap_expense_report_lines XL,
gl_code_combinations gcc,
ap_lookup_codes lc,
fnd_currencies F,
ap_expense_report_headers XH
WHERE XL.report_header_id = XH.report_header_id
AND XH.report_header_id = p_report_header_id
AND XL.code_combination_id = gcc.code_combination_id(+)
AND nvl(XL.itemization_parent_id,0) <> -1 /* Itemization Project */
AND lc.lookup_code(+) = XL.line_type_lookup_code
AND lc.lookup_type(+) = 'INVOICE DISTRIBUTION TYPE'
AND F.currency_code = p_base_currency);
l_debug_info := 'Done Insert into Invoice Lines Interface';
END InsertInvoiceLinesInterface;
SELECT supplier_numbering_method
FROM ap_product_setup
WHERE rownum = 1;
SELECT vendor_site_id,
nvl(pay_group_lookup_code, ''),
nvl(terms_date_basis, ''),
nvl(accts_pay_code_combination_id, -1),
nvl(terms_id, -1),
allow_awt_flag,
party_site_id
FROM ap_supplier_sites s, fnd_lookup_values l
WHERE s.vendor_site_code || '' = SUBSTRB(UPPER(l.meaning), 1, 15)
AND s.vendor_id = l_vendor_id
AND l.lookup_type = 'HOME_OFFICE'
AND l.lookup_code = home_or_office
AND s.org_id = l_org_id;
SELECT nvl(terms_date_basis, ''),
nvl(terms_id, -1),
nvl(pay_group_lookup_code, ''),
nvl(payment_priority, -1),
nvl(accts_pay_code_combination_id, -1),
nvl(prepay_code_combination_id, -1),
nvl(always_take_disc_flag, 'N'),
nvl(pay_date_basis_lookup_code, ''),
vendor_name,
segment1,
party_id
FROM ap_suppliers
WHERE vendor_id = l_vendor_id;
SELECT nvl(pay_group_lookup_code, ''),
nvl(terms_date_basis, ''),
nvl(accts_pay_code_combination_id, -1),
nvl(terms_id, -1),
allow_awt_flag,
party_site_id
FROM ap_supplier_sites s
WHERE vendor_site_id = l_vendor_site_id;
SELECT party_id
FROM per_employees_x
WHERE employee_id = l_employee_id
AND rownum = 1;
SELECT create_employee_vendor_flag, base_currency_code, employee_payment_priority
FROM ap_system_parameters_all
WHERE org_id = l_org_id;
SELECT vendor_id,
DECODE(employee_id,
NULL,
DECODE(nvl(vendor_type_lookup_code, 'EMPLOYEE'),
'EMPLOYEE',
'N',
'Y'),
p_vendor_rec.employee_id,
'N',
'Y'),
party_id
INTO l_vendor_id,
l_duplicate_vendor,
l_party_id
FROM ap_suppliers
WHERE employee_id = p_vendor_rec.employee_id;
l_debug_info := 'Update Vendor Site';
AP_VENDOR_PUB_PKG.update_vendor_site(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_val_return_status,
x_msg_count => l_val_msg_count,
x_msg_data => l_val_msg_data,
p_vendor_site_rec => l_vendor_site_rec,
p_vendor_site_id => l_vendor_site_id);
select 'Y'
into l_payee_exists
from IBY_EXTERNAL_PAYEES_ALL
where PAYEE_PARTY_ID = p_party_id
and org_id = p_org_id
and rownum =1;