The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT * FROM (
SELECT per.full_name,
per.employee_num,
decode(aeh.source, 'Both Pay', apsbp.vendor_name, aps.vendor_name) vendor_name,
decode(aeh.source, 'Both Pay', apsbp.segment1, aps.segment1) segment1,
aeh.invoice_num,
aeh.week_end_date,
aeh.payment_currency_code,
aeh.total,
alc.description,
aeh.org_id OU_ID,
hou.name,
aeh.default_currency_code
FROM
ap_expense_report_headers_all aeh,
ap_suppliers aps,
ap_suppliers apsbp,
per_employees_x per,
ap_lookup_codes alc,
hr_organization_units hou
where aeh.request_id = l_req_id
and aeh.reject_code is not null
and hou.organization_id(+) = aeh.org_id
and per.employee_id = aeh.employee_id
and aps.employee_id(+) = aeh.employee_id
and apsbp.vendor_id(+) = aeh.vendor_id
and alc.lookup_type in ('INVOICE IMPORT REJECTION','REJECT CODE')
and alc.lookup_code = aeh.reject_code
UNION ALL
SELECT per.full_name,
per.npw_number,
aps.vendor_name,
aps.segment1,
aeh.invoice_num,
aeh.week_end_date,
aeh.payment_currency_code,
aeh.total,
alc.description,
aeh.org_id OU_ID,
hou.name,
aeh.default_currency_code
FROM
ap_expense_report_headers_all aeh,
ap_suppliers aps,
per_cont_workers_x per,
ap_lookup_codes alc,
hr_organization_units hou
where aeh.request_id = l_req_id
and aeh.reject_code is not null
and hou.organization_id(+) = aeh.org_id
and per.person_id = aeh.employee_id
and aps.vendor_id(+) = aeh.vendor_id
and alc.lookup_type in ('INVOICE IMPORT REJECTION','REJECT CODE')
and alc.lookup_code = aeh.reject_code)
ORDER BY OU_ID;
SELECT I.invoice_num,
P.invoice_num,
V.vendor_name,
V.segment1,
I.invoice_currency_code,
I.invoice_amount,
ap_invoices_utility_pkg.get_prepaid_amount(I.invoice_id),
I.invoice_amount - ap_invoices_utility_pkg.get_prepaid_amount(I.invoice_id),
ap_invoices_utility_pkg.get_prepay_amount_remaining(P.invoice_id),
ap_invoices_utility_pkg.get_amt_applied_per_prepay(I.invoice_id,P.invoice_id),
aeh.org_id OU_ID,
hou.name,
aeh.default_currency_code
FROM ap_invoices_all I, ap_invoices_all P, ap_suppliers V,
ap_expense_report_headers_all aeh, hr_organization_units hou
WHERE aeh.request_id = l_req_id
AND aeh.vouchno is not null
AND hou.organization_id(+) = aeh.org_id
AND I.invoice_id = aeh.vouchno
AND P.invoice_id IN ( SELECT apd.invoice_id
FROM ap_invoice_distributions_all AID, ap_invoice_distributions_all APD
WHERE AID.invoice_id = I.invoice_id
AND AID.prepay_distribution_id = APD.invoice_distribution_id
AND AID.line_type_lookup_code = 'PREPAY')
AND V.vendor_id = I.vendor_id
AND V.vendor_id = P.vendor_id
ORDER BY OU_ID;
SELECT * FROM (
SELECT per.full_name,
per.employee_num,
decode(aeh.source, 'Both Pay', apsbp.vendor_name, aps.vendor_name) vendor_name,
decode(aeh.source, 'Both Pay', apsbp.segment1, aps.segment1) segment1,
aeh.invoice_num,
aeh.week_end_date,
aeh.payment_currency_code,
ai.invoice_amount,
decode(aeh.hold_lookup_code, null, null, alc.description) lookup_desc,
aeh.description header_desc,
nvl(aeh.vouchno,0),
aeh.org_id OU_ID,
hou.name,
aeh.default_currency_code
FROM
ap_expense_report_headers_all aeh,
ap_invoices_all ai,
ap_suppliers aps,
ap_suppliers apsbp,
per_employees_x per,
ap_lookup_codes alc,
hr_organization_units hou
where aeh.request_id = l_req_id
and nvl(aeh.vouchno, 0) > 0
and ai.invoice_id = aeh.vouchno
and aeh.reject_code is null
and hou.organization_id(+) = aeh.org_id
and per.employee_id = aeh.employee_id
and aps.employee_id(+) = aeh.employee_id
and apsbp.vendor_id(+) = aeh.vendor_id
and alc.lookup_type = decode(aeh.hold_lookup_code, null, 'EXPENSE REPORT STATUS', 'HOLD CODE')
and alc.lookup_code = decode(aeh.hold_lookup_code, null, nvl(aeh.expense_status_code, 'INVOICED'), aeh.hold_lookup_code)
UNION ALL
SELECT per.full_name,
per.npw_number,
aps.vendor_name,
aps.segment1,
aeh.invoice_num,
aeh.week_end_date,
aeh.payment_currency_code,
ai.invoice_amount,
decode(aeh.hold_lookup_code, null, null, alc.description) lookup_desc,
aeh.description header_desc,
nvl(aeh.vouchno,0),
aeh.org_id OU_ID,
hou.name,
aeh.default_currency_code
FROM
ap_expense_report_headers_all aeh,
ap_invoices_all ai,
ap_suppliers aps,
per_cont_workers_x per,
ap_lookup_codes alc,
hr_organization_units hou
where aeh.request_id = l_req_id
and nvl(aeh.vouchno, 0) > 0
and ai.invoice_id = aeh.vouchno
and aeh.reject_code is null
and hou.organization_id(+) = aeh.org_id
and per.person_id = aeh.employee_id
and aps.vendor_id(+) = aeh.vendor_id
and alc.lookup_type = decode(aeh.hold_lookup_code, null, 'EXPENSE REPORT STATUS', 'HOLD CODE')
and alc.lookup_code = decode(aeh.hold_lookup_code, null, nvl(aeh.expense_status_code, 'INVOICED'), aeh.hold_lookup_code))
ORDER BY OU_ID;
SELECT base_currency_code, default_exchange_rate_type
INTO l_base_currency, l_default_rate_type
FROM ap_system_parameters_all where org_id = l_org_id;
fnd_message.set_name('SQLAP', 'OIE_NO_REPORT_SELECTED');
SELECT base_currency_code, default_exchange_rate_type
INTO l_base_currency, l_default_rate_type
FROM ap_system_parameters_all where org_id = l_org_id;
SELECT base_currency_code, default_exchange_rate_type
INTO l_base_currency, l_default_rate_type
FROM ap_system_parameters_all where org_id = l_org_id;
SELECT count(*)
INTO l_line_count
FROM ap_expense_report_lines_all
WHERE report_header_id = p_report_header_id
AND amount IS NULL;
SELECT count(*)
INTO l_dist_count
FROM ap_exp_report_dists_all
WHERE report_header_id = p_report_header_id
AND amount IS NULL;
select total into l_header_total from ap_expense_report_headers_all where report_header_id = p_report_header_id;
select parameter_id into l_parameter_id from ap_expense_report_params_all where expense_type_code = 'PERSONAL';
select nvl(sum(amount),0) into l_line_total
from ap_expense_report_lines_all
where report_header_id = p_report_header_id
and (itemization_parent_id is null or itemization_parent_id <> -1)
and web_parameter_id <> l_parameter_id;
select nvl(sum(amount),0) into l_dist_total from ap_exp_report_dists_all where report_header_id = p_report_header_id
and (web_parameter_id is null or web_parameter_id <> l_parameter_id);
SELECT *
FROM ( SELECT *
FROM fnd_attached_documents
WHERE entity_name = 'OIE_HEADER_ATTACHMENTS'
AND pk1_value = To_Char(p_report_header_id)
UNION ALL
SELECT *
FROM fnd_attached_documents
WHERE entity_name = 'OIE_LINE_ATTACHMENTS'
AND pk1_value IN ( SELECT To_Char(report_line_id)
FROM ap_expense_report_lines_all
WHERE report_header_id = p_report_header_id
)
) ORDER BY entity_name,pk1_value,attached_document_id ;--Used the Order by clause so that seq_num will be first given to Header attachments.
SELECT *
FROM fnd_documents
WHERE document_id = l_document_id;
SELECT *
FROM fnd_documents_tl
WHERE document_id = l_document_id
AND rownum = 1;
SELECT fnd_attached_documents_s.nextval
INTO AttachedDocTabRec.attached_document_id
FROM dual;
FND_ATTACHED_DOCUMENTS_PKG.INSERT_ROW
(x_rowid => l_rowid
, x_attached_document_id => AttachedDocTabRec.attached_document_id
, x_document_id => AttachedDocTabRec.document_id
, x_seq_num => l_seq_num
, x_entity_name => 'AP_INVOICES'
, x_pk1_value => To_Char(p_invoice_id)
, x_pk2_value => null
, x_pk3_value => null
, x_pk4_value => null
, x_pk5_value => null
, x_automatically_added_flag => 'Y'
, x_creation_date => sysdate
, x_created_by => to_number(fnd_global.user_id)
, x_last_update_date => sysdate
, x_last_updated_by => to_number(fnd_global.user_id)
, x_last_update_login => to_number(FND_GLOBAL.LOGIN_ID)
, x_column1 => AttachedDocTabRec.column1
, x_datatype_id => null
, x_category_id => AttachedDocTabRec.category_id
, x_security_type => null
, X_security_id => null
, X_publish_flag => null
, X_image_type => null
, X_storage_type => null
, X_usage_type => null
, X_language => null
, X_description => null
, X_file_name => null
, X_media_id => l_media_id
, X_doc_attribute_Category => null
, X_doc_attribute1 => null
, X_doc_attribute2 => null
, X_doc_attribute3 => null
, X_doc_attribute4 => null
, X_doc_attribute5 => null
, X_doc_attribute6 => null
, X_doc_attribute7 => null
, X_doc_attribute8 => null
, X_doc_attribute9 => null
, X_doc_attribute10 => null
, X_doc_attribute11 => null
, X_doc_attribute12 => null
, X_doc_attribute13 => null
, X_doc_attribute14 => null
, X_doc_attribute15 => null
);
/* Logic to update the document usage_type to "S" */
OPEN expense_documents_cur(AttachedDocTabRec.document_id);
FND_DOCUMENTS_PKG.Update_Row
(X_document_id => DocumentTabRec.document_id
,X_last_update_date => sysdate
,X_last_updated_by => to_number(fnd_global.user_id)
,X_last_update_login => to_number(FND_GLOBAL.LOGIN_ID)
,X_datatype_id => DocumentTabRec.datatype_id
,X_category_id => DocumentTabRec.category_id
,X_security_type => DocumentTabRec.security_type
,X_security_id => DocumentTabRec.security_id
,X_publish_flag => DocumentTabRec.publish_flag
,X_image_type => DocumentTabRec.image_type
,X_storage_type => DocumentTabRec.storage_type
,X_usage_type => 'S'
,X_start_date_active => DocumentTabRec.start_date_active
,X_end_date_active => DocumentTabRec.end_date_active
,X_language => DocumentTLTabRec.language
,X_description => DocumentTLTabRec.description
,X_file_name => DocumentTabRec.file_name
,X_media_id => DocumentTabRec.media_id
,X_Attribute_Category => DocumentTLTabRec.doc_attribute_category
,X_Attribute1 => DocumentTLTabRec.doc_attribute1
,X_Attribute2 => DocumentTLTabRec.doc_attribute2
,X_Attribute3 => DocumentTLTabRec.doc_attribute3
,X_Attribute4 => DocumentTLTabRec.doc_attribute4
,X_Attribute5 => DocumentTLTabRec.doc_attribute5
,X_Attribute6 => DocumentTLTabRec.doc_attribute6
,X_Attribute7 => DocumentTLTabRec.doc_attribute7
,X_Attribute8 => DocumentTLTabRec.doc_attribute8
,X_Attribute9 => DocumentTLTabRec.doc_attribute9
,X_Attribute10 => DocumentTLTabRec.doc_attribute10
,X_Attribute11 => DocumentTLTabRec.doc_attribute11
,X_Attribute12 => DocumentTLTabRec.doc_attribute12
,X_Attribute13 => DocumentTLTabRec.doc_attribute13
,X_Attribute14 => DocumentTLTabRec.doc_attribute14
,X_Attribute15 => DocumentTLTabRec.doc_attribute15
,X_url => DocumentTabRec.url
,X_title => DocumentTLTabRec.title);
SELECT Count(*)
INTO l_count_invalid_cat
FROM ( SELECT *
FROM fnd_attached_documents
WHERE entity_name = 'OIE_HEADER_ATTACHMENTS'
AND pk1_value = To_Char(p_report_header_id)
UNION ALL
SELECT *
FROM fnd_attached_documents
WHERE entity_name = 'OIE_LINE_ATTACHMENTS'
AND pk1_value IN ( SELECT To_Char(report_line_id)
FROM ap_expense_report_lines_all
WHERE report_header_id = p_report_header_id
)
) OIE_ATTACHMENTS
WHERE OIE_ATTACHMENTS.CATEGORY_ID
NOT IN
( SELECT fdcu.CATEGORY_ID
FROM fnd_doc_category_usages fdcu,
fnd_attachment_functions faf,
fnd_document_categories fdc
WHERE faf.function_name = 'APXINWKB'
AND fdcu.enabled_flag = 'Y'
AND faf.attachment_function_id = fdcu.attachment_function_id
AND fdc.category_id = fdcu.category_id
AND sysdate BETWEEN nvl(start_date_active,sysdate-1) AND nvl(end_date_active,sysdate+1)
);
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 trunc(p_source_date) 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 > trunc(p_source_date)
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);
-- Bug: 8408909, Donot update currency code and rate on line change
update ap_exp_report_dists set
receipt_currency_amount = l_dist_rec_curr_amt
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
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,
emps.is_contingent
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,
'N' is_contingent
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,
'Y' is_contingent
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);
UpdateDistsWithReceiptInfo(l_invoice_rec.report_header_id, g_debug_switch);
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,
last_update_date = sysdate,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
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,
last_update_date = sysdate,
last_updated_by = g_last_updated_by,
last_update_login = g_last_update_login
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(sum(amount))
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 IN (SELECT invoice_id
FROM ap_invoices_interface
WHERE request_id = l_request_id
);
DELETE FROM ap_interface_rejections
WHERE parent_table = 'AP_INVOICE_LINES_INTERFACE'
and parent_id IN (SELECT aili.invoice_line_id
FROM ap_invoices_interface aii, ap_invoice_lines_interface aili
WHERE aii.invoice_id = aili.invoice_id
AND aii.request_id = l_request_id
);
DELETE FROM ap_invoice_lines_interface
WHERE invoice_id IN (SELECT invoice_id
FROM ap_invoices_interface
WHERE request_id = l_request_id
);
DELETE FROM ap_invoices_interface
WHERE request_id = l_request_id;
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
------------------------------------------------------------------------
l_payment_priority ap_supplier_sites_all.payment_priority%TYPE;
select payment_priority
into l_payment_priority
from ap_supplier_sites_all
where vendor_site_id = p_vendor_rec.vendor_site_id;
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,
PAYMENT_PRIORITY)
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),--Bug#8369669
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,
Decode(p_invoice_rec.is_contingent,'Y',p_vendor_rec.party_site_id,'')),
l_payment_priority);
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
--bug 8658097 starts
,EXPENSE_START_DATE
,EXPENSE_END_DATE
--bug 8658097 ends
)
(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, '')
--bug 8658097 starts
,xl.start_expense_date
,xl.end_expense_date
--bug 8658097 ends
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;