[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT electronic_processing_channel
FROM
iby_payment_profiles ppp,
iby_pay_instructions_all ins
WHERE
ins.payment_instruction_id = p_payment_instruction_id
AND ins.payment_profile_id = ppp.payment_profile_id;
SELECT ''||XMLType.getClobVal(instruction)
INTO x_extract_doc
FROM iby_xml_fd_ins_1_0_v
WHERE payment_instruction_id = p_payment_instruction_id;
SELECT count(trxn_document_id)
INTO l_extract_count
FROM iby_trxn_documents
where doctype=100 and trxnmid=p_payment_instruction_id;
SELECT XMLType.getClobVal(payment_process_request)
INTO x_extract_doc
FROM iby_xml_fd_ppr_1_0_v
WHERE payment_service_request_id = p_payment_service_request_id;
SELECT XMLType.getClobVal(XMLElement("PositivePayDataExtract", XMLAgg(xml_pmt_lvl.payment)))
FROM
iby_xml_fd_pmt_1_0_v xml_pmt_lvl
WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
AND xml_pmt_lvl.payment_status = 'ISSUED'
AND (xml_pmt_lvl.positive_pay_file_created_flag='N' or xml_pmt_lvl.positive_pay_file_created_flag is NULL);
SELECT XMLType.getClobVal(XMLElement("PositivePayDataExtract", XMLAgg(xml_pmt_lvl.payment)))
FROM
iby_xml_fd_pmt_1_0_v xml_pmt_lvl,
iby_pay_instructions_all ins
WHERE xml_pmt_lvl.payment_instruction_id = ins.payment_instruction_id
AND ins.payment_profile_id = p_payment_profile_id
AND xml_pmt_lvl.payment_date >= nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'), xml_pmt_lvl.payment_date)
AND xml_pmt_lvl.payment_date <= nvl(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'), SYSDATE)
AND xml_pmt_lvl.payment_status = 'ISSUED'
AND (xml_pmt_lvl.positive_pay_file_created_flag='N' or xml_pmt_lvl.positive_pay_file_created_flag is NULL);
UPDATE iby_payments_all pmt
SET pmt.positive_pay_file_created_flag = 'Y'
WHERE pmt.payment_instruction_id = p_payment_instruction_id
AND pmt.payment_status = 'ISSUED'
AND (pmt.positive_pay_file_created_flag='N' or pmt.positive_pay_file_created_flag is NULL);
UPDATE iby_pay_instructions_all
SET positive_pay_file_created_flag= 'Y'
WHERE payment_instruction_id = p_payment_instruction_id;
SELECT pmt.payment_id,pmt.payment_instruction_id
BULK COLLECT INTO l_paymentid_arr,l_paymentinstrid_arr
FROM iby_xml_fd_pmt_1_0_v pmt,
iby_pay_instructions_all ins
WHERE pmt.payment_instruction_id = ins.payment_instruction_id
AND ins.payment_profile_id = p_payment_profile_id
AND pmt.payment_date >= nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'), pmt.payment_date)
AND pmt.payment_date <= nvl(to_date(l_to_date, 'YYYY/MM/DD HH24:MI:SS'), SYSDATE)
AND pmt.payment_status = 'ISSUED'
AND (pmt.positive_pay_file_created_flag='N' or pmt.positive_pay_file_created_flag is NULL);
UPDATE iby_payments_all
SET positive_pay_file_created_flag = 'Y'
WHERE payment_id = l_paymentid_arr(i);
UPDATE iby_pay_instructions_all ins
SET ins.positive_pay_file_created_flag = 'Y'
WHERE not exists (SELECT 'N'
FROM iby_payments_all pmt
WHERE nvl(pmt.positive_pay_file_created_flag,'N') = 'N'
AND pmt.payment_status IN('ISSUED', 'PAID')
AND pmt.payment_instruction_id = l_paymentinstrid_arr(i))
AND ins.payment_instruction_id = l_paymentinstrid_arr(i);
p_reselect IN VARCHAR2,
p_sys_key IN iby_security_pkg.des3_key_type,
x_extract_doc OUT NOCOPY CLOB
)
IS
type payment_arr is table of number;
SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl
WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
AND xml_pmt_lvl.payment_status IN ('ISSUED','PAID')
AND (xml_pmt_lvl.positive_pay_file_created_flag='N' or xml_pmt_lvl.positive_pay_file_created_flag is NULL)
;
SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl
WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
AND xml_pmt_lvl.payment_status IN ('ISSUED','PAID')
;
SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl
WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
AND xml_pmt_lvl.payment_status IN('VOID','ISSUED','PAID')
AND (xml_pmt_lvl.positive_pay_file_created_flag='N' or xml_pmt_lvl.positive_pay_file_created_flag is NULL)
;
SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl
WHERE xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
AND xml_pmt_lvl.payment_status IN('VOID','ISSUED','PAID')
;
SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_pay_instructions_all pmtinstr,iby_payment_profiles ppp
WHERE xml_pmt_lvl.payment_instruction_id = pmtinstr.payment_instruction_id
AND pmtinstr.payment_profile_id = ppp.payment_profile_id
AND ppp.positive_pay_format_code IN
(SELECT ppfformat.format_code
FROM iby_formats_vl ppfformat
WHERE ppfformat.format_name = p_format_name)
AND xml_pmt_lvl.payment_date >= nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'), xml_pmt_lvl.payment_date)
AND xml_pmt_lvl.payment_date <= nvl(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
AND xml_pmt_lvl.payment_status IN('ISSUED', 'PAID')
AND(xml_pmt_lvl.positive_pay_file_created_flag = 'N' OR xml_pmt_lvl.positive_pay_file_created_flag IS NULL)
AND xml_pmt_lvl.internal_bank_account_id IN
(SELECT ba.bank_account_id
FROM ce_bank_accounts ba
WHERE ba.bank_account_name = p_internal_bank_account_name)
;
SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_pay_instructions_all pmtinstr,iby_payment_profiles ppp
WHERE xml_pmt_lvl.payment_instruction_id = pmtinstr.payment_instruction_id
AND pmtinstr.payment_profile_id = ppp.payment_profile_id
AND ppp.positive_pay_format_code IN
(SELECT ppfformat.format_code
FROM iby_formats_vl ppfformat
WHERE ppfformat.format_name = p_format_name)
AND xml_pmt_lvl.payment_date >= nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'), xml_pmt_lvl.payment_date)
AND xml_pmt_lvl.payment_date <= nvl(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
AND xml_pmt_lvl.payment_status IN('ISSUED', 'PAID')
AND xml_pmt_lvl.internal_bank_account_id IN
(SELECT ba.bank_account_id
FROM ce_bank_accounts ba
WHERE ba.bank_account_name = p_internal_bank_account_name)
;
SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_pay_instructions_all pmtinstr,iby_payment_profiles ppp
WHERE xml_pmt_lvl.payment_instruction_id = pmtinstr.payment_instruction_id
AND pmtinstr.payment_profile_id = ppp.payment_profile_id
AND ppp.positive_pay_format_code IN
(SELECT ppfformat.format_code
FROM iby_formats_vl ppfformat
WHERE ppfformat.format_name = p_format_name)
AND decode(xml_pmt_lvl.payment_status,'VOID',xml_pmt_lvl.void_date,xml_pmt_lvl.payment_date) >= nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'), decode(xml_pmt_lvl.payment_status,'VOID',xml_pmt_lvl.void_date,xml_pmt_lvl.payment_date))
AND decode(xml_pmt_lvl.payment_status,'VOID',xml_pmt_lvl.void_date,xml_pmt_lvl.payment_date) <= nvl(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
AND xml_pmt_lvl.payment_status IN('VOID','ISSUED','PAID')
AND(xml_pmt_lvl.positive_pay_file_created_flag = 'N' OR xml_pmt_lvl.positive_pay_file_created_flag IS NULL)
AND xml_pmt_lvl.internal_bank_account_id IN
(SELECT ba.bank_account_id
FROM ce_bank_accounts ba
WHERE ba.bank_account_name = p_internal_bank_account_name)
;
SELECT xmltype.getclobval(xmlelement("PositivePayDataExtract", xmlagg(xml_pmt_lvl.payment)))
FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_pay_instructions_all pmtinstr,iby_payment_profiles ppp
WHERE xml_pmt_lvl.payment_instruction_id = pmtinstr.payment_instruction_id
AND pmtinstr.payment_profile_id = ppp.payment_profile_id
AND ppp.positive_pay_format_code IN
(SELECT ppfformat.format_code
FROM iby_formats_vl ppfformat
WHERE ppfformat.format_name = p_format_name)
AND decode(xml_pmt_lvl.payment_status,'VOID',xml_pmt_lvl.void_date,xml_pmt_lvl.payment_date) >= nvl(to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS'), decode(xml_pmt_lvl.payment_status,'VOID',xml_pmt_lvl.void_date,xml_pmt_lvl.payment_date))
AND decode(xml_pmt_lvl.payment_status,'VOID',xml_pmt_lvl.void_date,xml_pmt_lvl.payment_date) <= nvl(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
AND xml_pmt_lvl.payment_status IN('VOID','ISSUED','PAID')
AND xml_pmt_lvl.internal_bank_account_id IN
(SELECT ba.bank_account_id
FROM ce_bank_accounts ba
WHERE ba.bank_account_name = p_internal_bank_account_name)
;
p_reselect || ':' || p_from_date || ':' || p_to_date || ':' ||
p_sys_key, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
p_reselect || ':' || l_from_date || ':' || l_to_date || ':' ||
p_sys_key, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
IF (trim(p_reselect) = '' or p_reselect is null or p_reselect = 'null') THEN
IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
iby_debug_pub.add(debug_msg => 'Cannot proceed since Reselect attribute is not supplied', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
p_reselect || ':' || l_from_date || ':' || l_to_date || ':' ||
p_sys_key, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
IF upper(p_reselect) = 'NO' THEN
IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
iby_debug_pub.add(debug_msg => 'Reselect Option No ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
UPDATE iby_payments_all
SET positive_pay_file_created_flag = 'Y'
WHERE payment_instruction_id = p_payment_instruction_id
AND (positive_pay_file_created_flag='N' or positive_pay_file_created_flag is NULL)
AND payment_status IN ('ISSUED','PAID');
iby_debug_pub.add(debug_msg => 'Reselect Option Yes ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
UPDATE iby_payments_all
SET positive_pay_file_created_flag = 'Y'
WHERE payment_instruction_id = p_payment_instruction_id
AND (positive_pay_file_created_flag='N' or positive_pay_file_created_flag is NULL)
AND payment_status IN ('ISSUED','PAID');
IF upper(p_reselect) = 'NO' THEN
IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
iby_debug_pub.add(debug_msg => 'Reselect Option No ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
UPDATE iby_payments_all
SET positive_pay_file_created_flag = 'Y'
WHERE payment_instruction_id = p_payment_instruction_id
AND (positive_pay_file_created_flag='N' or positive_pay_file_created_flag is NULL)
AND payment_status IN ('VOID','ISSUED','PAID');
iby_debug_pub.add(debug_msg => 'Reselect Option Yes ' || p_payment_instruction_id, debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
UPDATE iby_payments_all
SET positive_pay_file_created_flag = 'Y'
WHERE payment_instruction_id = p_payment_instruction_id
AND payment_status IN ('VOID','ISSUED','PAID');
IF upper(p_reselect) = 'NO' THEN
IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
iby_debug_pub.add(debug_msg => 'Reselect Option No ' , debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
SELECT pmt.payment_id,pmt.payment_instruction_id
BULK COLLECT INTO l_paymentid_arr,l_paymentinstrid_arr
FROM iby_xml_fd_pmt_1_0_v pmt,
iby_pay_instructions_all pmtinstr,
iby_payment_profiles ppp,
iby_formats_vl ppfformat,
ce_bank_accounts ba
WHERE pmt.payment_instruction_id = pmtinstr.payment_instruction_id
AND pmtinstr.payment_profile_id = ppp.payment_profile_id
AND ppp.positive_pay_format_code = ppfformat.format_code
AND pmt.internal_bank_account_id = ba.bank_account_id
AND ppfformat.format_name = p_format_name
AND ba.bank_account_name = p_internal_bank_account_name
AND (pmt.positive_pay_file_created_flag='N' or pmt.positive_pay_file_created_flag is NULL)
AND pmt.payment_date >= nvl(to_date(l_from_date, 'YYYY/MM/DD HH24:MI:SS'), pmt.payment_date)
AND pmt.payment_date <= nvl(to_date(l_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
AND pmt.payment_status IN('ISSUED','PAID');
iby_debug_pub.add(debug_msg => 'Reselect Option Yes ' , debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
SELECT pmt.payment_id,pmt.payment_instruction_id
BULK COLLECT INTO l_paymentid_arr,l_paymentinstrid_arr
FROM iby_xml_fd_pmt_1_0_v pmt,
iby_pay_instructions_all pmtinstr,
iby_payment_profiles ppp,
iby_formats_vl ppfformat,
ce_bank_accounts ba
WHERE pmt.payment_instruction_id = pmtinstr.payment_instruction_id
AND pmtinstr.payment_profile_id = ppp.payment_profile_id
AND ppp.positive_pay_format_code = ppfformat.format_code
AND pmt.internal_bank_account_id = ba.bank_account_id
AND ppfformat.format_name = p_format_name
AND ba.bank_account_name = p_internal_bank_account_name
AND pmt.payment_date >= nvl(to_date(l_from_date, 'YYYY/MM/DD HH24:MI:SS'), pmt.payment_date)
AND pmt.payment_date <= nvl(to_date(l_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
AND pmt.payment_status IN('ISSUED','PAID');
IF upper(p_reselect) = 'NO' THEN
IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
iby_debug_pub.add(debug_msg => 'Reselect Option No ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
SELECT pmt.payment_id,pmt.payment_instruction_id
BULK COLLECT INTO l_paymentid_arr,l_paymentinstrid_arr
FROM iby_xml_fd_pmt_1_0_v pmt,
iby_pay_instructions_all pmtinstr,
iby_payment_profiles ppp,
iby_formats_vl ppfformat,
ce_bank_accounts ba
WHERE pmt.payment_instruction_id = pmtinstr.payment_instruction_id
AND pmtinstr.payment_profile_id = ppp.payment_profile_id
AND ppp.positive_pay_format_code = ppfformat.format_code
AND pmt.internal_bank_account_id = ba.bank_account_id
AND ppfformat.format_name = p_format_name
AND ba.bank_account_name = p_internal_bank_account_name
AND (pmt.positive_pay_file_created_flag='N' or pmt.positive_pay_file_created_flag is NULL)
AND decode(pmt.payment_status,'VOID',pmt.void_date,pmt.payment_date) >= nvl(to_date(l_from_date, 'YYYY/MM/DD HH24:MI:SS'), decode(pmt.payment_status,'VOID',pmt.void_date,pmt.payment_date))
AND decode(pmt.payment_status,'VOID',pmt.void_date,pmt.payment_date) <= nvl(to_date(l_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
AND pmt.payment_status IN('VOID','ISSUED','PAID');
iby_debug_pub.add(debug_msg => 'Reselect Option Yes ', debug_level => G_LEVEL_STATEMENT, module => l_Debug_Module);
SELECT pmt.payment_id,pmt.payment_instruction_id
BULK COLLECT INTO l_paymentid_arr,l_paymentinstrid_arr
FROM iby_xml_fd_pmt_1_0_v pmt,
iby_pay_instructions_all pmtinstr,
iby_payment_profiles ppp,
iby_formats_vl ppfformat,
ce_bank_accounts ba
WHERE pmt.payment_instruction_id = pmtinstr.payment_instruction_id
AND pmtinstr.payment_profile_id = ppp.payment_profile_id
AND ppp.positive_pay_format_code = ppfformat.format_code
AND pmt.internal_bank_account_id = ba.bank_account_id
AND ppfformat.format_name = p_format_name
AND ba.bank_account_name = p_internal_bank_account_name
AND decode(pmt.payment_status,'VOID',pmt.void_date,pmt.payment_date) >= nvl(to_date(l_from_date, 'YYYY/MM/DD HH24:MI:SS'), decode(pmt.payment_status,'VOID',pmt.void_date,pmt.payment_date))
AND decode(pmt.payment_status,'VOID',pmt.void_date,pmt.payment_date) <= nvl(to_date(l_to_date, 'YYYY/MM/DD HH24:MI:SS'), sysdate)
AND pmt.payment_status IN('VOID','ISSUED','PAID');
UPDATE iby_payments_all
SET positive_pay_file_created_flag = 'Y'
WHERE payment_id = l_paymentid_arr(i);
UPDATE iby_pay_instructions_all
SET positive_pay_file_created_flag='Y'
WHERE payment_instruction_id = p_payment_instruction_id;
UPDATE iby_pay_instructions_all ins
SET ins.positive_pay_file_created_flag = 'Y'
WHERE not exists (SELECT 'N'
FROM iby_payments_all pmt
WHERE nvl(pmt.positive_pay_file_created_flag,'N') = 'N'
AND pmt.payment_status IN('ISSUED', 'PAID')
AND pmt.payment_instruction_id = l_paymentinstrid_arr(i))
AND ins.payment_instruction_id = l_paymentinstrid_arr(i);
SELECT pvs.vendor_site_code,
pv.num_1099,
pv.global_attribute2,
pv.global_attribute3,
pv.standard_industry_class,
pvs.address_line1,
pvs.address_line2,
pvs.address_line3,
pvs.city,
pvs.zip,
pvs.province,
pvs.country,
pvs.state
FROM PO_VENDOR_SITES_ALL pvs,
PO_VENDORS pv
WHERE pv.vendor_id=p_business_entity_id
AND pvs.vendor_site_id=p_business_entity_site_id
AND pvs.country=p_country
AND pv.vendor_id=pvs.vendor_id;
SELECT decode(pvs.country,'ES',pv.vendor_name,'GR',pvs.vendor_site_code),
pv.num_1099,
pv.global_attribute2,
pv.global_attribute3,
pv.standard_industry_class,
pvs.address_line1,
pvs.address_line2,
pvs.address_line3,
pvs.city,
pvs.zip,
pvs.province,
pvs.country,
pvs.state
FROM PO_VENDOR_SITES_ALL pvs,
PO_VENDORS pv
WHERE pv.vendor_id=p_business_entity_id
AND pvs.tax_reporting_site_flag='Y'
AND pvs.country=p_country
AND pv.vendor_id=pvs.vendor_id;
SELECT email_address
FROM hz_contact_points
WHERE owner_table_name = 'HZ_PARTIES'
AND owner_table_id = p_owner_table_id
AND contact_point_type = 'EMAIL'
AND primary_flag = 'Y'
AND status = 'A';
SELECT contact_point_id
FROM (SELECT t.contact_point_id,
t.primary_flag,
t.phone_line_type,
RANK() OVER (PARTITION BY t.phone_line_type ORDER BY t.primary_flag DESC, t.contact_point_id DESC) primary_phone
FROM hz_contact_points t
WHERE t.owner_table_name = 'HZ_PARTIES'
AND t.owner_table_id = p_owner_table_id
AND t.contact_point_type = 'PHONE'
AND t.phone_line_type = 'GEN'
AND t.status = 'A') x
WHERE x.primary_phone = 1;
SELECT contact_point_id
FROM (SELECT t.contact_point_id,
t.primary_flag,
t.phone_line_type,
RANK() OVER (PARTITION BY t.phone_line_type ORDER BY t.primary_flag DESC, t.contact_point_id DESC) primary_phone
FROM hz_contact_points t
WHERE t.owner_table_name = 'HZ_PARTIES'
AND t.owner_table_id = p_owner_table_id
AND t.contact_point_type = 'PHONE'
AND t.phone_line_type = 'FAX'
AND t.status = 'A') x
WHERE x.primary_phone = 1;
SELECT url
FROM hz_contact_points
WHERE owner_table_name = 'HZ_PARTIES'
AND owner_table_id = p_owner_table_id
AND contact_point_type = 'WEB'
AND primary_flag = 'Y'
AND status = 'A';
SELECT TELEPHONE_NUMBER_1, TELEPHONE_NUMBER_2
FROM hr_locations_all hr_loc, xle_firstparty_information_v xle_firstparty
WHERE hr_loc.location_id = xle_firstparty.location_id
AND xle_firstparty.party_id = p_party_id;
SELECT
XMLElement("ContactLocators",
XMLElement("PhoneNumber", nvl(hz_format_phone_v2pub.get_formatted_phone(l_phone_cp_id), l_hr_loc_phone)),
XMLElement("FaxNumber", nvl(hz_format_phone_v2pub.get_formatted_phone(l_fax_cp_id), l_hr_loc_fax)),
XMLElement("EmailAddress", l_email),
XMLElement("Website", l_url)
)
INTO g_payer_contact_tbl(p_party_id).l_contactinfo
FROM dual;
SELECT remit_to_location_id, party_site_id, payee_party_id
FROM iby_payments_all
WHERE payment_id = p_payment_id;
SELECT email_address
FROM hz_contact_points
WHERE owner_table_name = p_owner_table_name
AND owner_table_id = p_owner_table_id
AND contact_point_type = 'EMAIL'
AND primary_flag = 'Y'
AND status = 'A';
SELECT contact_point_id
FROM (SELECT t.contact_point_id,
t.primary_flag,
t.phone_line_type,
RANK() OVER (PARTITION BY t.phone_line_type ORDER BY t.primary_flag DESC, t.contact_point_id DESC) primary_phone
FROM hz_contact_points t
WHERE t.owner_table_name = p_owner_table_name
AND t.owner_table_id = p_owner_table_id
AND t.contact_point_type = 'PHONE'
AND t.phone_line_type = 'GEN'
AND t.status = 'A') x
WHERE x.primary_phone = 1;
SELECT contact_point_id
FROM (SELECT t.contact_point_id,
t.primary_flag,
t.phone_line_type,
RANK() OVER (PARTITION BY t.phone_line_type ORDER BY t.primary_flag DESC, t.contact_point_id DESC) primary_phone
FROM hz_contact_points t
WHERE t.owner_table_name = p_owner_table_name
AND t.owner_table_id = p_owner_table_id
AND t.contact_point_type = 'PHONE'
AND t.phone_line_type = 'FAX'
AND t.status = 'A') x
WHERE x.primary_phone = 1;
SELECT url
FROM hz_contact_points
WHERE owner_table_name = p_owner_table_name
AND owner_table_id = p_owner_table_id
AND contact_point_type = 'WEB'
AND primary_flag = 'Y'
AND status = 'A';
SELECT
XMLElement("ContactLocators",
XMLElement("PhoneNumber", hz_format_phone_v2pub.get_formatted_phone(l_phone_cp_id)),
XMLElement("FaxNumber", hz_format_phone_v2pub.get_formatted_phone(l_fax_cp_id)),
XMLElement("EmailAddress", l_email),
XMLElement("Website", l_url)
)
INTO l_contactinfo
FROM dual;
SELECT address_line_1, address_line_2, address_line_3,
town_or_city, region_1, region_2,
postal_code, country
FROM hr_locations_all
WHERE location_id = p_hr_location_id;
SELECT count(payment_id)
FROM iby_xml_fd_doc_1_0_v xml_doc_lvl
WHERE xml_doc_lvl.formatting_payment_id = p_payment_id; --bug 7006504
SELECT count(payment_id)
FROM iby_xml_fd_doc_1_0_v xml_doc_lvl
WHERE xml_doc_lvl.payment_id = p_payment_id; --bug 7459662
SELECT XMLAgg(
XMLElement("TreasurySymbol",
XMLElement("Name", fv.treasury_symbol),
XMLElement("Amount",
XMLElement("Value", fv.amount),
XMLElement("Currency", XMLElement("Code", iby.payment_currency_code))
)
)
)
FROM fv_tp_ts_amt_data fv, iby_pay_instructions_all iby
WHERE iby.payment_instruction_id = fv.payment_instruction_id
AND iby.payment_instruction_id = p_payment_instruction_id;
SELECT XMLElement("FederalInstructionInfo",
XMLElement("TreasurySymbols", p_fv_treasury_symbol_agg),
XMLElement("ControlNumber", control_number),
XMLElement("ECSSummaryDosSeqNumber", iby_utility_pvt.get_view_param('FV_ECS_SEQ'))
)
FROM fv_summary_consolidate
WHERE payment_instruction_id = p_payment_instruction_id;
SELECT XMLAgg(xml_pmt_lvl.payment)
FROM
iby_xml_fd_pmt_1_0_v xml_pmt_lvl,
IBY_PAY_INSTRUCTIONS_ALL ins
WHERE
xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
AND ins.payment_instruction_id = xml_pmt_lvl.payment_instruction_id
AND ((xml_pmt_lvl.payment_status in ('INSTRUCTION_CREATED',
'VOID_BY_SETUP', 'VOID_BY_OVERFLOW') AND ins.process_type = 'STANDARD') OR
ins.process_type = 'IMMEDIATE');
SELECT XMLAgg(xml_pmt_lvl.payment)
FROM
iby_xml_fd_pmt_1_0_v xml_pmt_lvl,
IBY_PAY_INSTRUCTIONS_ALL ins
WHERE
xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
AND ins.payment_instruction_id = xml_pmt_lvl.payment_instruction_id
AND ((xml_pmt_lvl.payment_status in ('READY_TO_REPRINT',
'VOID_BY_SETUP_REPRINT', 'VOID_BY_OVERFLOW_REPRINT') AND ins.process_type = 'STANDARD') OR
ins.process_type = 'IMMEDIATE');
SELECT XMLAgg(xml_pmt_lvl.payment)
FROM
iby_xml_fd_pmt_1_0_v xml_pmt_lvl
WHERE
xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id;
SELECT XMLAgg(xml_pmt_lvl.payment)
FROM
iby_xml_fd_pmt_1_0_v xml_pmt_lvl
WHERE
xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
AND xml_pmt_lvl.payment_status in ('INSTRUCTION_CREATED', 'READY_TO_REPRINT',
'SUBMITTED_FOR_PRINTING', 'FORMATTED', 'TRANSMITTED', 'ISSUED', 'PAID');
SELECT XMLAgg(xml_pmt_lvl.payment)
FROM
iby_xml_fd_pmt_1_0_v xml_pmt_lvl
WHERE
xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
AND xml_pmt_lvl.payment_id = G_Extract_Run_Payment_id;
SELECT XMLAgg(xml_pmt_lvl.payment)
FROM
iby_xml_fd_pmt_1_0_v xml_pmt_lvl
WHERE
xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
AND (Get_SRA_Attribute(xml_pmt_lvl.payment_id, G_SRA_REQ_FLAG_ATTR) = 'Y' OR xml_pmt_lvl.payment_status ='VOID_BY_OVERFLOW')
AND Get_SRA_Attribute(xml_pmt_lvl.payment_id, G_SRA_DELIVERY_METHOD_ATTR) = G_SRA_DELIVERY_METHOD_PRINTED
AND xml_pmt_lvl.payment_status in ('INSTRUCTION_CREATED', 'READY_TO_REPRINT',
'SUBMITTED_FOR_PRINTING', 'FORMATTED', 'TRANSMITTED', 'ISSUED', 'PAID','VOID_BY_OVERFLOW');
SELECT ext_pmt_v.payment_id, ext_pmt_v.paper_document_number, ext_pmt_v.payment_status
FROM
IBY_EXT_FD_PMT_1_0_V ext_pmt_v,
IBY_PAY_INSTRUCTIONS_ALL ins
WHERE
ext_pmt_v.payment_instruction_id = p_payment_instruction_id
AND ins.payment_instruction_id = ext_pmt_v.payment_instruction_id;
SELECT ins.payment_instruction_status
FROM
IBY_PAY_INSTRUCTIONS_ALL ins
WHERE
ins.payment_instruction_id = p_payment_instruction_id;
SELECT payer
FROM iby_xml_fd_payer_1_0_v
WHERE legal_entity_id = p_legal_entity_id;
SELECT int_bank_account
FROM iby_xml_fd_prba_1_0_v
WHERE bank_account_id = p_bank_account_id;
SELECT payer
FROM iby_xml_fd_payer_1_0_v
WHERE payment_id = p_payment_id;
SELECT int_bank_account
FROM iby_xml_fd_prba_1_0_v
WHERE payment_id = p_payment_id;
SELECT payment_id
FROM iby_payments_all
WHERE payment_instruction_id = p_payment_instruction_id
AND ROWNUM = 1;
SELECT payment_id
FROM iby_payments_all
WHERE payment_instruction_id = p_payment_instruction_id
AND ROWNUM = 1;
SELECT XMLAgg(account_setting)
FROM iby_xml_fd_acct_settings_1_0_v
WHERE bep_account_id = p_bep_account_id;
SELECT XMLAgg(doc_payable)
FROM iby_xml_fd_doc_1_0_v
WHERE formatting_payment_id = p_payment_id --bug 7006504
AND document_status <> 'REMOVED';
SELECT XMLAgg(doc_payable)
FROM iby_xml_fd_doc_1_0_v xml_doc, iby_docs_payable_all doc, ce_security_profiles_v ce_sp
WHERE xml_doc.payment_id = p_payment_id --bug 7459662
AND xml_doc.document_payable_id = doc.document_payable_id
AND ce_sp.organization_type = doc.org_type
AND ce_sp.organization_id = doc.org_id
AND xml_doc.document_status <> 'REMOVED';
SELECT nvl(employee_payment_flag, 'N')
FROM iby_payments_all
WHERE payment_id = p_payment_id;
SELECT payee
FROM iby_xml_fd_payee_1_0_v
WHERE payment_id = p_payment_id;
SELECT payee
FROM iby_xml_fd_payeem_1_0_v
WHERE payment_id = p_payment_id;
SELECT payee
FROM iby_xml_fd_invpayee_1_0_v
WHERE payment_id = p_payment_id;
SELECT irel.additional_information
FROM iby_ext_payee_relationships irel
WHERE irel.party_id = l_party_id
AND irel.supplier_site_id = l_supplier_site_id
AND irel.remit_party_id = l_remit_party_id
AND irel.remit_supplier_site_id = l_remit_supplier_site_id
AND irel.active = 'Y'
AND to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') BETWEEN (to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00') AND (to_char(nvl(irel.to_date,sysdate), 'YYYY-MM-DD') || ' 23:59:59')
;
SELECT irel.relationship_id
FROM iby_ext_payee_relationships irel
WHERE irel.party_id = l_party_id
AND irel.supplier_site_id = l_supplier_site_id
AND irel.remit_party_id = l_remit_party_id
AND irel.remit_supplier_site_id = l_remit_supplier_site_id
AND irel.active = 'Y'
AND to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') BETWEEN (to_char(irel.from_date, 'YYYY-MM-DD') || ' 00:00:00') AND (to_char(nvl(irel.to_date,sysdate), 'YYYY-MM-DD') || ' 23:59:59')
;
SELECT nvl(employee_payment_flag, 'N')
FROM iby_payments_all
WHERE payment_id = p_payment_id;
SELECT ext_bank_account
FROM iby_xml_fd_peba_1_0_v
WHERE bank_account_id = p_external_bank_account_id;
SELECT ext_bank_account
FROM iby_xml_fd_pebam_1_0_v
WHERE bank_account_id = p_external_bank_account_id;
SELECT nvl(employee_payment_flag, 'N')
FROM iby_payments_all
WHERE payment_id = p_payment_id;
SELECT ext_bank_account
FROM iby_xml_fd_peba_1_0_vd
WHERE payment_id = p_payment_id;
SELECT ext_bank_account
FROM iby_xml_fd_pebam_1_0_vd
WHERE payment_id = p_payment_id;
SELECT XMLAgg(
XMLElement("DocumentPayableLine",
XMLElement("LineNumber", apdl.line_number),
XMLElement("PONumber", apdl.po_number),
XMLElement("LineType",
XMLElement("Code", apdl.line_type),
XMLElement("Meaning", null)),
XMLElement("LineDescription", TRANSLATE(apdl.description, p_conc_invalid_chars, p_conc_replacement_chars)),
XMLElement("LineGrossAmount",
XMLElement("Value", apdl.line_gross_amount),
XMLElement("Currency", XMLElement("Code", ibydoc.document_currency_code))),
XMLElement("UnitPrice", apdl.unit_price),
XMLElement("Quantity", apdl.quantity),
XMLElement("UnitOfMeasure",
XMLElement("Code", apdl.unit_of_measure),
XMLElement("Meaning", null)),
XMLElement("Tax",
XMLElement("TaxCode", apdl.tax),
XMLElement("TaxRate", apdl.tax_rate)
),
IBY_FD_EXTRACT_EXT_PUB.Get_Docline_Ext_Agg(ibydoc.document_payable_id, apdl.line_number)
)
)
FROM ap_document_lines_v apdl, iby_docs_payable_all ibydoc,
iby_pay_service_requests ibypsr
WHERE ibydoc.document_payable_id = p_document_payable_id
AND ibydoc.calling_app_doc_unique_ref1 = apdl.calling_app_doc_unique_ref1
AND nvl(ibydoc.calling_app_doc_unique_ref2,-99) = apdl.calling_app_doc_unique_ref2
AND nvl(ibydoc.calling_app_doc_unique_ref3,-99) = apdl.calling_app_doc_unique_ref3
AND nvl(ibydoc.calling_app_doc_unique_ref4,-99) = nvl(apdl.calling_app_doc_unique_ref4,-99)
AND nvl(ibydoc.calling_app_doc_unique_ref5,-99) = nvl(apdl.calling_app_doc_unique_ref5,-99)
AND ibydoc.calling_app_id = apdl.calling_app_id
AND ibypsr.call_app_pay_service_req_code = apdl.call_app_pay_service_req_code
AND ibydoc.payment_service_request_id = ibypsr.payment_service_request_id;
SELECT sra_setup.sra_override_payee_flag, sra_setup.remit_advice_delivery_method
FROM iby_payments_all pmt, iby_pay_instructions_all ins,
iby_payment_profiles pp, iby_remit_advice_setup sra_setup
WHERE pmt.payment_id = p_payment_id
AND pmt.payment_instruction_id = ins.payment_instruction_id
AND pp.payment_profile_id = ins.payment_profile_id
AND pp.system_profile_code = sra_setup.system_profile_code;
SELECT separate_remit_advice_req_flag
FROM iby_payments_all
WHERE payment_id = p_payment_id;
SELECT loc.language, loc.country
FROM hz_party_sites ps, hz_locations loc, iby_payments_all pmt
WHERE payment_id = p_payment_id
AND pmt.party_site_id = ps.party_site_id(+)
AND loc.location_id = ps.location_id;
SELECT payee.remit_advice_delivery_method,
payee.remit_advice_email,
payee.remit_advice_fax
FROM iby_external_payees_all payee,
iby_payments_all pmt
WHERE payee.payee_party_id = pmt.payee_party_id
AND payee.payment_function = pmt.payment_function
AND (payee.org_id is NULL OR (payee.org_id = pmt.org_id AND payee.org_type = pmt.org_type))
AND (payee.party_site_id is NULL OR payee.party_site_id = pmt.party_site_id)
AND (payee.supplier_site_id is NULL OR payee.supplier_site_id = pmt.supplier_site_id)
AND pmt.payment_id = p_payment_id
ORDER BY payee.supplier_site_id, payee.party_site_id, payee.org_id;
SELECT fv.treasury_symbol
FROM FV_TP_TS_AMT_DATA fv,
iby_payments_all pmt
WHERE pmt.payment_instruction_id = fv.payment_instruction_id
AND pmt.payment_id = p_payment_id
AND ROWNUM = 1;
SELECT pmt.payment_amount,
pmt.payment_currency_code
FROM iby_payments_all pmt
WHERE pmt.payment_id = p_payment_id;
SELECT sum(amount_withheld)
FROM iby_docs_payable_all
WHERE payment_id = p_payment_id;
SELECT XMLAgg(payment)
FROM iby_xml_fd_pmt_1_0_v
WHERE payment_service_request_id = p_payment_service_request_id;
SELECT count(payment_id)
FROM iby_xml_fd_pmt_1_0_v
WHERE payment_service_request_id = p_payment_service_request_id;
SELECT XMLAgg(doc_payable)
FROM iby_xml_fd_doc_1_0_v
WHERE payment_service_request_id = p_payment_service_request_id
AND payment_id is null;
SELECT count(document_payable_id)
FROM iby_xml_fd_doc_1_0_v
WHERE payment_service_request_id = p_payment_service_request_id
AND payment_id is null;
SELECT XMLAgg(payment_error)
FROM iby_xml_fd_pmt_err_1_0_v
WHERE payment_id = p_payment_id;
SELECT XMLAgg(doc_payable_error)
FROM iby_xml_fd_doc_err_1_0_v
WHERE document_payable_id = p_document_payable_id;
PROCEDURE Update_Pmt_SRA_Attr_Prt
(
p_payment_instruction_id IN NUMBER
)
IS
l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Update_Pmt_SRA_Attr_Prt';
SELECT payment_id, payment_reference_number
FROM iby_payments_all
WHERE payment_instruction_id = p_payment_instruction_id
-- note: this where clause should be kept
-- in-sync with the where clause of l_payerinstr_sra_prt_csr
-- in Get_Ins_PayerInstrAgg()
AND Get_SRA_Attribute(payment_id, G_SRA_REQ_FLAG_ATTR) = 'Y'
AND Get_SRA_Attribute(payment_id, G_SRA_DELIVERY_METHOD_ATTR) = G_SRA_DELIVERY_METHOD_PRINTED
AND payment_status in ('INSTRUCTION_CREATED', 'READY_TO_REPRINT',
'SUBMITTED_FOR_PRINTING', 'FORMATTED', 'TRANSMITTED', 'ISSUED');
UPDATE
iby_payments_all
SET
remit_advice_delivery_method = 'PRINTED',
remit_advice_email = null,
remit_advice_fax = null,
object_version_number = object_version_number + 1,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
WHERE
payment_id = l_payment.payment_id;
END Update_Pmt_SRA_Attr_Prt;
PROCEDURE Update_Pmt_SRA_Attr_Ele
(
p_payment_id IN NUMBER,
p_delivery_method IN VARCHAR2,
p_recipient_email IN VARCHAR2,
p_recipient_fax IN VARCHAR2
)
IS
BEGIN
IF p_delivery_method = 'EMAIL' THEN
UPDATE
iby_payments_all
SET
remit_advice_delivery_method = p_delivery_method,
remit_advice_email = p_recipient_email,
remit_advice_fax = null,
object_version_number = object_version_number + 1,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
WHERE payment_id = p_payment_id;
UPDATE
iby_payments_all
SET
remit_advice_delivery_method = p_delivery_method,
remit_advice_email = null,
remit_advice_fax = p_recipient_fax,
object_version_number = object_version_number + 1,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = nvl(fnd_global.LOGIN_ID, fnd_global.conc_login_id)
WHERE payment_id = p_payment_id;
END Update_Pmt_SRA_Attr_Ele;
g_docs_pay_attribs_tbl.DELETE;
g_hr_addr_tbl.DELETE;
g_hz_addr_tbl.DELETE;
g_payer_contact_tbl.DELETE;
SELECT address
FROM IBY_XML_HZ_ADDR_1_0_V
WHERE location_id = p_location_id;
SELECT address
FROM IBY_XML_HZ_ADDR_1_0_V
WHERE location_id = p_location_id;
SELECT XMLConcat( XMLElement("AddressInternalID", null),
XMLElement("AddressLine1", null), XMLElement("AddressLine2", null),
XMLElement("AddressLine3", null), XMLElement("AddressLine4", null),
XMLElement("City", null), XMLElement("County", null), XMLElement("State", null),
XMLElement("Province", null), XMLElement("Country", te.territory_code),
XMLElement("ISO3DigitCountry", te.iso_territory_code), XMLElement("CountryName", te.territory_short_name),
XMLElement("PostalCode", null),
XMLElement("PreFormattedConcatenatedAddress", null),
XMLElement("PreFormattedMailingAddress", null) )
FROM fnd_territories_vl te
WHERE te.territory_code = p_country;
SELECT address
FROM IBY_XML_HR_ADDR_1_0_V
WHERE location_id = p_location_id;
SELECT GROUP_BY_PAYMENT_CURRENCY
FROM IBY_EXT_FD_INS_1_0_V
WHERE payment_instruction_id = p_payment_instruction_id;
SELECT SUM(payment_amount)
FROM iby_payments_all
WHERE payment_status in ('INSTRUCTION_CREATED', 'READY_TO_REPRINT',
'SUBMITTED_FOR_PRINTING', 'ISSUED', 'FORMATTED', 'TRANSMITTED')
AND payment_instruction_id = p_payment_instruction_id;