[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select sum(nvl(efd.amount,0)) amount,
efd.dist_code_combination_id ccid,
efd.tax_code ,
nvl(efd.amount_includes_tax_flag,'N') amount_includes_tax_flag, --9727870
decode(P_ROLLUP_FLAG,
'N',efd.feed_distribution_id) feed_distribution_id,
decode(P_ROLLUP_FLAG,
'N',efl.transaction_date,
nvl(P_START_DATE,nvl(P_END_DATE,sysdate))) transaction_date,
decode(P_ROLLUP_FLAG,
'N',efd.description) description,
efd.org_id, -- Bug 5592139
efl.merchant_name,
efl.merchant_number, --bug8299023
efd.attribute_category, /* DFFs added for Bug 14457995 */
efd.attribute1,
efd.attribute2,
efd.attribute3,
efd.attribute4,
efd.attribute5,
efd.attribute6,
efd.attribute7,
efd.attribute8,
efd.attribute9,
efd.attribute10,
efd.attribute11,
efd.attribute12,
efd.attribute13,
efd.attribute14,
efd.attribute15
from ap_expense_feed_lines efl,
ap_expense_feed_dists efd,
ap_card_programs cp
where efl.feed_line_id = efd.feed_line_id
and efl.card_program_id = P_CARD_PROGRAM_ID
and efl.card_program_id = cp.card_program_id
and nvl(efd.invoiced_flag,'N') = 'N'
and ((nvl(efd.status_lookup_code,'VALIDATED') = 'APPROVED')
OR
(nvl(cp.exclude_unverified_flag,'N') = 'N' AND
nvl(efd.status_lookup_code,'VALIDATED') = 'VALIDATED')
OR
(nvl(cp.exclude_rejected_flag,'N') = 'N' AND
nvl(efd.status_lookup_code,'VALIDATED') in ('REJECTED','VERIFIED'))
OR
(nvl(cp.exclude_personal_flag,'N') = 'N' AND
nvl(efd.status_lookup_code,'VALIDATED') = 'PERSONAL')
OR
(nvl(cp.exclude_disputed_flag,'N') = 'N' AND
nvl(efd.status_lookup_code,'VALIDATED') = 'DISPUTED')
OR
(nvl(cp.exclude_held_flag,'N') = 'N' AND
nvl(efd.status_lookup_code,'VALIDATED') = 'HOLD'))
and (efl.posted_date is null OR
(efl.posted_date between
nvl(P_START_DATE,efl.posted_date-1) and
nvl(P_END_DATE,efl.posted_date+1)))
group by efd.dist_code_combination_id,
efd.tax_code,
nvl(efd.amount_includes_tax_flag,'N'), --9727870
decode(P_ROLLUP_FLAG,
'N',efd.feed_distribution_id),
decode(P_ROLLUP_FLAG,
'N',efl.transaction_date,
nvl(P_START_DATE,nvl(P_END_DATE,sysdate))),
decode(P_ROLLUP_FLAG,
'N',efd.description),
efd.org_id, -- Bug 5620010
efl.merchant_name,
efl.merchant_number, --bug8299023
efd.attribute_category, /*Bug 14457995*/
efd.attribute1,
efd.attribute2,
efd.attribute3,
efd.attribute4,
efd.attribute5,
efd.attribute6,
efd.attribute7,
efd.attribute8,
efd.attribute9,
efd.attribute10,
efd.attribute11,
efd.attribute12,
efd.attribute13,
efd.attribute14,
efd.attribute15 ; --Bug 14457995
select ap_invoices_interface_s.nextval
into l_invoice_id
from dual;
select ap_invoice_lines_interface_s.nextval
into l_invoice_line_id
from dual;
l_debug_info := 'inserting into ap_invoice_lines_interface';
insert into ap_invoice_lines_interface
(INVOICE_ID,
INVOICE_LINE_ID,
LINE_NUMBER,
LINE_TYPE_LOOKUP_CODE,
AMOUNT,
ACCOUNTING_DATE,
DESCRIPTION,
TAX_CODE,
AMOUNT_INCLUDES_TAX_FLAG,
DIST_CODE_COMBINATION_ID,
ORG_ID, -- Bug 5592139
MERCHANT_NAME,
MERCHANT_REFERENCE,--bug8299023
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15) VALUES --Bug 14457995
(l_invoice_id,
l_invoice_line_id,
l_count,
'ITEM',
rec_lines_int.amount,
l_transaction_date,
rec_lines_int.description,
rec_lines_int.tax_code,
rec_lines_int.amount_includes_tax_flag,
rec_lines_int.ccid,
rec_lines_int.org_id, -- Bug 5592139
rec_lines_int.merchant_name,
rec_lines_int.merchant_number, --bug8299023
rec_lines_int.attribute_category,
rec_lines_int.attribute1,
rec_lines_int.attribute2,
rec_lines_int.attribute3,
rec_lines_int.attribute4,
rec_lines_int.attribute5,
rec_lines_int.attribute6,
rec_lines_int.attribute7,
rec_lines_int.attribute8,
rec_lines_int.attribute9,
rec_lines_int.attribute10,
rec_lines_int.attribute11,
rec_lines_int.attribute12,
rec_lines_int.attribute13,
rec_lines_int.attribute14,
rec_lines_int.attribute15
); --Bug 14457995
update AP_EXPENSE_FEED_DISTS
set invoiced_flag = 'Y',
INVOICE_ID = l_invoice_id,
INVOICE_LINE_ID = l_invoice_line_id
where feed_distribution_id = rec_lines_int.feed_distribution_id;
update AP_EXPENSE_FEED_DISTS EFD
set invoiced_flag = 'Y',
INVOICE_ID = l_invoice_id,
INVOICE_LINE_ID = l_invoice_line_id
where nvl(invoiced_flag,'N') = 'N'
and dist_code_combination_id = rec_lines_int.ccid
and nvl(efd.tax_code,'N')= nvl(rec_lines_int.tax_code,'N') --9727870
and nvl(efd.amount_includes_tax_flag,'N')= rec_lines_int.amount_includes_tax_flag --9727870
and exists
(select 'Parent record meets group criteria from lines_cursor'
from AP_EXPENSE_FEED_LINES EFL,
AP_CARD_PROGRAMS CP
where EFL.feed_line_id = EFD.feed_line_id
and EFL.card_program_id = P_CARD_PROGRAM_ID
and EFL.card_program_id = CP.card_program_id
and EFL.merchant_name = rec_lines_int.merchant_name --9727870
and EFL.merchant_number = rec_lines_int.merchant_number --9727870
and ((nvl(efd.status_lookup_code,'VALIDATED') = 'APPROVED')
OR
(nvl(cp.exclude_unverified_flag,'N') = 'N' AND
nvl(efd.status_lookup_code,'VALIDATED') = 'VALIDATED')
OR
(nvl(cp.exclude_rejected_flag,'N') = 'N' AND
nvl(efd.status_lookup_code,'VALIDATED') = 'REJECTED')
OR
(nvl(cp.exclude_personal_flag,'N') = 'N' AND
nvl(efd.status_lookup_code,'VALIDATED') = 'PERSONAL')
OR
(nvl(cp.exclude_disputed_flag,'N') = 'N' AND
nvl(efd.status_lookup_code,'VALIDATED') = 'DISPUTED')
OR
(nvl(cp.exclude_held_flag,'N') = 'N' AND
nvl(efd.status_lookup_code,'VALIDATED') = 'HOLD')
OR
(nvl(cp.exclude_unreconciled_flag,'N') = 'N' AND
nvl(efd.status_lookup_code,'VALIDATED') = 'VERIFIED'))
and (efl.posted_date is null OR
(efl.posted_date between
nvl(P_START_DATE,efl.posted_date-1) and
nvl(P_END_DATE,efl.posted_date+1))));
select vendor_id,
vendor_site_id,
card_program_currency_code
into l_vendor_id,
l_vendor_site_id,
l_invoice_currency_code
from ap_card_programs
where card_program_id = P_CARD_PROGRAM_ID;
l_debug_info := 'Inserting into AP_INVOICES_INTERFACE';
insert into AP_INVOICES_INTERFACE
(INVOICE_ID,
INVOICE_NUM,
VENDOR_ID,
VENDOR_SITE_ID,
INVOICE_AMOUNT,
INVOICE_CURRENCY_CODE,
SOURCE,
ORG_ID
) VALUES
(l_invoice_id,
substrb(to_char(l_invoice_id)||'-'||to_char(sysdate),1,50),
l_vendor_id,
l_vendor_site_id,
l_sum,
l_invoice_currency_code,
'PCARD',
l_org_id -- Bug 5592139
);