[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select displayed_field
from ap_lookup_codes
where lookup_type = 'XML TOKEN NAME'
and lookup_code = p_lookup_code;
update ap_invoice_lines_interface
set DIST_CODE_COMBINATION_ID =
(select FREIGHT_CODE_COMBINATION_ID
from ap_system_parameters_all sys,ap_invoices_interface h
where sys.org_id = h.org_id and h.invoice_id = p_invoice_id)
where line_type_lookup_code = 'FREIGHT' and
invoice_id = p_invoice_id;
UPDATE AP_INVOICE_LINES_INTERFACE
SET LINE_TYPE_LOOKUP_CODE = 'MISCELLANEOUS'
WHERE LINE_TYPE_LOOKUP_CODE NOT IN ('ITEM', 'TAX', 'FREIGHT') and
INVOICE_ID = p_invoice_id;
select distinct org_id into l_org_id
from po_headers_all
where segment1 = p_po_number;
select vendor_id into p_vendor_id
from po_vendor_sites_all
where org_id = p_org_id and vendor_site_id = p_vendor_site_id;
select company_admin_email
into p_email_address
from ecx_tp_headers
where party_type = 'S' and
party_site_id = p_vendor_site_id and
party_id = p_vendor_id;
'SELECT company_admin_email INTO :tab FROM ecx_tp_headers
where party_site_id = ' || p_vendor_site_id ||
' and party_id = ' || p_vendor_id ||
' and party_type = '|| ''''|| l_party_type || '''';
select h.invoice_id, h.vendor_id
from ap_invoices_interface h
where h.source like 'XML GATEWAY' and
h.status is NULL and
h.vendor_name is NOT NULL;
update ap_invoices_interface
set vendor_name = null
where invoice_id = l_inv_ids(i);
select ap_p2p_inbound_notification_s.nextval into l_item_key from dual;
select fnd_global.tab||displayed_field||': '
from ap_lookup_codes
where lookup_type = 'XML TOKEN NAME' and
lookup_code = 'INVOICE LINE NUMBER';
select displayed_field||': '
from ap_lookup_codes
where lookup_type = 'XML TOKEN NAME' and
lookup_code = 'INVOICE NUMBER';
select distinct vendor_email_address
from ap_invoices_interface
where request_id = c_request_id
and vendor_email_address is not null; --bug4065112
select h.invoice_id, to_number(null),h.invoice_num, to_number(null),
fnd_global.tab||fnd_global.tab||lc.description||
decode(r.token_name1, null, null,
fnd_global.newline||fnd_global.tab||fnd_global.tab||
get_token_display_field(r.token_name1)
||': '||r.token_value1) ||
decode(r.token_name2, null, null,
fnd_global.newline||fnd_global.tab||fnd_global.tab||
get_token_display_field(r.token_name2)
||': '||r.token_value2) ||
decode(r.token_name3, null, null,
fnd_global.newline||fnd_global.tab||fnd_global.tab||
get_token_display_field(r.token_name3)
||': '||r.token_value3) ||
decode(r.token_name4, null, null,
fnd_global.newline||fnd_global.tab||fnd_global.tab||
get_token_display_field(r.token_name4)
||': '||r.token_value4) ||
decode(r.token_name5, null, null,
fnd_global.newline||fnd_global.tab||fnd_global.tab||
get_token_display_field(r.token_name5)
||': '||r.token_value5) ||
decode(r.token_name6, null, null,
fnd_global.newline||fnd_global.tab||fnd_global.tab||
get_token_display_field(r.token_name6)
||': '||r.token_value6) ||
decode(r.token_name7, null, null,
fnd_global.newline||fnd_global.tab||fnd_global.tab||
get_token_display_field(r.token_name7)
||': '||r.token_value7) ||
decode(r.token_name8, null, null,
fnd_global.newline||fnd_global.tab||fnd_global.tab||
get_token_display_field(r.token_name8)
||': '||r.token_value8) ||
decode(r.token_name9, null, null,
fnd_global.newline||fnd_global.tab||fnd_global.tab||
get_token_display_field(r.token_name9)
||': '||r.token_value9) ||
decode(r.token_name10, null, null,
fnd_global.newline||fnd_global.tab||fnd_global.tab||
get_token_display_field(r.token_name10)
||': '||r.token_value10)||
fnd_global.newline
-- Bug 4065112 starts
,group_id, external_doc_ref
-- Bug 4065112 ends
from ap_invoices_interface h,
ap_interface_rejections r,
ap_lookup_codes lc
where h.request_id = c_request_id
and nvl(r.notify_vendor_flag, 'N') = 'Y'
and h.invoice_id = r.parent_id
and r.parent_table = 'AP_INVOICES_INTERFACE'
and h.vendor_email_address = c_vendor_email_address
and lc.lookup_code = r.reject_lookup_code
and lc.lookup_type = 'REJECT CODE'
union all
select h.invoice_id, l.invoice_line_id,h.invoice_num, l.line_number,
fnd_global.tab||fnd_global.tab||lc.description||
decode(r.token_name1, null, null,
fnd_global.newline||fnd_global.tab||fnd_global.tab||
get_token_display_field(r.token_name1)
||': '||r.token_value1) ||
decode(r.token_name2, null, null,
fnd_global.newline||fnd_global.tab||fnd_global.tab||
get_token_display_field(r.token_name2)
||': '||r.token_value2) ||
decode(r.token_name3, null, null,
fnd_global.newline||fnd_global.tab||fnd_global.tab||
get_token_display_field(r.token_name3)
||': '||r.token_value3) ||
decode(r.token_name4, null, null,
fnd_global.newline||fnd_global.tab||fnd_global.tab||
get_token_display_field(r.token_name4)
||': '||r.token_value4) ||
decode(r.token_name5, null, null,
fnd_global.newline||fnd_global.tab||fnd_global.tab||
get_token_display_field(r.token_name5)
||': '||r.token_value5) ||
decode(r.token_name6, null, null,
fnd_global.newline||fnd_global.tab||fnd_global.tab||
get_token_display_field(r.token_name6)
||': '||r.token_value6) ||
decode(r.token_name7, null, null,
fnd_global.newline||fnd_global.tab||fnd_global.tab||
get_token_display_field(r.token_name7)
||': '||r.token_value7) ||
decode(r.token_name8, null, null,
fnd_global.newline||fnd_global.tab||fnd_global.tab||
get_token_display_field(r.token_name8)
||': '||r.token_value8) ||
decode(r.token_name9, null, null,
fnd_global.newline||fnd_global.tab||fnd_global.tab||
get_token_display_field(r.token_name9)
||': '||r.token_value9) ||
decode(r.token_name10, null, null,
fnd_global.newline||fnd_global.tab||fnd_global.tab||
get_token_display_field(r.token_name10)
||': '||r.token_value10)||
fnd_global.newline
-- Bug 4065112 starts
,group_id, external_doc_ref
-- Bug 4065112 ends
from ap_invoices_interface h,
ap_invoice_lines_interface l,
ap_interface_rejections r,
ap_lookup_codes lc
where h.request_id = c_request_id
and h.invoice_id = l.invoice_id
and nvl(r.notify_vendor_flag, 'N') = 'Y'
and l.invoice_line_id = r.parent_id
and r.parent_table = 'AP_INVOICE_LINES_INTERFACE'
and h.vendor_email_address = c_vendor_email_address
and lc.lookup_code = r.reject_lookup_code
and lc.lookup_type = 'REJECT CODE'
order by 1, 2;
select ap_p2p_inbound_notification_s.nextval into l_item_key from dual;
select NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO'),1,1),' ',
NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
into l_org_id
from dual;
l_result := ap_utilities_pkg.delete_invoice_from_interface(
l_invoice_id_table,
l_invoice_line_id_table,
'AP_XML_INVOICE_INBOUND_PKG.notify_supplier');
select PRIORITY_REQUEST_ID
from fnd_concurrent_requests
where request_id = FND_GLOBAL.CONC_REQUEST_ID;
select fnd_global.tab||PROGRAM ||' with Request ID: '||
to_char(request_id)||fnd_global.newline
from fnd_conc_req_summary_v
where priority_request_id = l_priority_request_id
order by request_id;
UPDATE ap_invoice_lines_interface
SET taxable_flag = 'Y'
WHERE line_type_lookup_code = 'ITEM'
AND invoice_id IN
(SELECT h.invoice_id
FROM ap_invoices_interface h, ap_invoice_lines_interface l
WHERE h.invoice_id = l.invoice_id
AND h.source = 'XML GATEWAY'
AND h.group_id = p_group_id
AND l.line_type_lookup_code = 'TAX'
AND l.line_group_number is null
GROUP BY h.invoice_id);
UPDATE ap_invoice_lines_interface
SET taxable_flag = 'Y'
WHERE invoice_line_id = p_item_line_id;
SELECT distinct ORG_ID
FROM AP_INVOICES_INTERFACE
WHERE GROUP_ID = p_group_id
AND SOURCE = 'XML GATEWAY';
SELECT FREIGHT_CODE_COMBINATION_ID
INTO l_freight_ccid
FROM AP_SYSTEM_PARAMETERS_ALL
WHERE NVL(ORG_ID,TO_NUMBER(NVL(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99'))) =
NVL(l_org_id,TO_NUMBER(NVL(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99')));
SELECT MISC_CHARGE_CCID
INTO l_misc_ccid
FROM FINANCIALS_SYSTEM_PARAMS_ALL
WHERE NVL(ORG_ID,TO_NUMBER(NVL(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99'))) =
NVL(l_org_id,TO_NUMBER(NVL(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99')));
UPDATE AP_INVOICE_LINES_INTERFACE
SET DIST_CODE_COMBINATION_ID =
decode(LINE_TYPE_LOOKUP_CODE,'FREIGHT',l_freight_ccid,
'MISCELLANEOUS',l_misc_ccid),
PRORATE_ACROSS_FLAG =
decode(LINE_TYPE_LOOKUP_CODE,'FREIGHT',decode(l_freight_ccid,'','Y','N'),
'MISCELLANEOUS',decode(l_misc_ccid,'','Y','N'))
WHERE INVOICE_ID in
(SELECT INVOICE_ID
FROM AP_INVOICES_INTERFACE
WHERE GROUP_ID = p_group_id
AND NVL(ORG_ID,TO_NUMBER(NVL(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99'))) =
NVL(l_org_id,TO_NUMBER(NVL(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10)), '-99')))
AND SOURCE = 'XML GATEWAY')
AND LINE_TYPE_LOOKUP_CODE in ('FREIGHT','MISCELLANEOUS');