[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
WHERE xml_pmt_lvl.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
WHERE pmt.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')
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')
;
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(xmlagg(xml_pmt_lvl.payment))
FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_payment_profiles ppp
WHERE xml_pmt_lvl.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(xmlagg(xml_pmt_lvl.payment))
FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_payment_profiles ppp
WHERE xml_pmt_lvl.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(xmlagg(xml_pmt_lvl.payment))
FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_payment_profiles ppp
WHERE xml_pmt_lvl.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')
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(xmlagg(xml_pmt_lvl.payment))
FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_payment_profiles ppp
WHERE xml_pmt_lvl.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')
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(xmlagg(xml_pmt_lvl.payment))
FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_payment_profiles ppp
WHERE xml_pmt_lvl.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(xmlagg(xml_pmt_lvl.payment))
FROM iby_xml_fd_pmt_1_0_v xml_pmt_lvl,iby_payment_profiles ppp
WHERE xml_pmt_lvl.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)
;
SELECT xmltype.getclobval(xmlagg(pmt.payment))
FROM IBY_XML_FD_11iPMT_1_0_V pmt
WHERE pmt.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)
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 ( ( NVL(UPPER(p_reselect),'NO') = 'YES' )
OR ( NVL(pmt.positive_pay_file_created_flag,'N') = 'N' AND NVL(UPPER(p_reselect),'NO') = 'NO' ) );
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');
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');
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_payment_profiles ppp,
iby_formats_vl ppfformat,
ce_bank_accounts ba
WHERE pmt.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_payment_profiles ppp,
iby_formats_vl ppfformat,
ce_bank_accounts ba
WHERE pmt.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_payment_profiles ppp,
iby_formats_vl ppfformat,
ce_bank_accounts ba
WHERE pmt.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');
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_payment_profiles ppp,
iby_formats_vl ppfformat,
ce_bank_accounts ba
WHERE pmt.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');
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_payment_profiles ppp,
iby_formats_vl ppfformat,
ce_bank_accounts ba
WHERE pmt.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_payment_profiles ppp,
iby_formats_vl ppfformat,
ce_bank_accounts ba
WHERE pmt.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 ap_checks_all
SET positive_pay_status_code = 'SENT AS VOIDED'
WHERE check_ID IN
(SELECT pmt.check_id
FROM IBY_XML_FD_11iPMT_1_0_V pmt
WHERE pmt.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)
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 ( ( NVL(UPPER(p_reselect),'NO') = 'YES' )
OR ( NVL(pmt.positive_pay_file_created_flag,'N') = 'N'
AND NVL(UPPER(p_reselect),'NO') = 'NO' ) ) );
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);
iby_debug_pub.add(debug_msg => 'Inserting tax reg number l_tax_registration : '|| l_tax_registration ||' in cache for p_legal_entity_id : '||l_key,
debug_level => G_LEVEL_STATEMENT, module => l_debug_module);
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 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 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_docs_payable_all xml_doc_lvl
WHERE xml_doc_lvl.formatting_payment_id = p_payment_id
AND xml_doc_lvl.document_status NOT IN
('FAILED_BY_CALLING_APP','FAILED_BY_REJECTION_LEVEL','FAILED_BY_RELATED_DOCUMENT','FAILED_VALIDATION','REJECTED', 'REMOVED'); --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 count(payment_id)
FROM iby_docs_payable_all xml_doc_lvl
WHERE xml_doc_lvl.payment_id = p_payment_id; --bug 7459662
SELECT count(payment_id)
FROM iby_docs_payable_all xml_doc_lvl
WHERE xml_doc_lvl.payment_id = p_payment_id
and xml_doc_lvl.document_status = 'PAYMENT_CREATED';
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.DOCS_AGG)
FROM
IBY_XML_FD_AMEX_PMT_1_0_V xml_pmt_lvl
WHERE
xml_pmt_lvl.payment_instruction_id = p_payment_instruction_id
and xml_pmt_lvl.payment_status not in ('VOID', 'VOID_BY_OVERFLOW', 'VOID_BY_SETUP');
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 xml_pmt_lvl.logical_reference_number between nvl(p_from_pmt_ref,xml_pmt_lvl.logical_reference_number)
and nvl(p_to_pmt_ref,xml_pmt_lvl.logical_reference_number)
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')
ORDER BY xml_pmt_lvl.logical_reference_number;
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 min(payment_id)
FROM iby_payments_all
WHERE payment_instruction_id = p_payment_instruction_id;
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
WHERE payment_id = p_payment_id
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 XMLAgg(doc_payable)
FROM iby_xml_fd_doc_1_0_v
WHERE formatting_payment_id = p_payment_id
AND document_status = 'PAYMENT_CREATED';
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 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 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", ail.line_number),
XMLElement("PONumber", ph.segment1),
XMLElement("LineType",
XMLElement("Code", ail.line_type_lookup_code),
XMLElement("Meaning", null)),
XMLElement("LineDescription", TRANSLATE(ail.description, p_conc_invalid_chars, p_conc_replacement_chars)),
XMLElement("LineGrossAmount",
XMLElement("Value", ail.amount),
XMLElement("Currency", XMLElement("Code", ibydoc.document_currency_code))),
XMLElement("UnitPrice", ail.unit_price),
XMLElement("Quantity", ail.quantity_invoiced),
XMLElement("UnitOfMeasure",
XMLElement("Code", ail.unit_meas_lookup_code),
XMLElement("Meaning", null)),
XMLElement("Tax",
XMLElement("TaxCode", ail.tax),
XMLElement("TaxRate", ail.tax_rate)
),
IBY_FD_EXTRACT_EXT_PUB.Get_Docline_Ext_Agg(ibydoc.document_payable_id, ail.line_number)
)
)
FROM
ap_invoice_lines_all ail,
po_headers_all ph,
iby_docs_payable_all ibydoc
WHERE ibydoc.document_payable_id = p_document_payable_id
AND ail.po_header_id = ph.po_header_id(+)
AND nvl(ibydoc.calling_app_doc_unique_ref2,-99) = ail.invoice_id
AND ibydoc.calling_app_id = 200;
SELECT XMLAgg(
XMLElement("DocumentPayableLine",
XMLElement("LineNumber", ail.line_number),
XMLElement("PONumber", ph.segment1),
XMLElement("LineType",
XMLElement("Code", ail.line_type_lookup_code),
XMLElement("Meaning", null)),
XMLElement("LineDescription", TRANSLATE(ail.description, p_conc_invalid_chars, p_conc_replacement_chars)),
XMLElement("LineGrossAmount",
XMLElement("Value", ail.amount),
XMLElement("Currency", XMLElement("Code", p_doc_currency_code))),
XMLElement("UnitPrice", ail.unit_price),
XMLElement("Quantity", ail.quantity_invoiced),
XMLElement("UnitOfMeasure",
XMLElement("Code", ail.unit_meas_lookup_code),
XMLElement("Meaning", null)),
XMLElement("Tax",
XMLElement("TaxCode", ail.tax),
XMLElement("TaxRate", ail.tax_rate)
),
IBY_FD_EXTRACT_EXT_PUB.Get_Docline_Ext_Agg(p_document_payable_id, ail.line_number)
)
)
FROM
ap_invoice_lines_all ail,
po_headers_all ph
WHERE ail.po_header_id = ph.po_header_id(+)
AND nvl(p_call_app_doc_unique_ref2,-99) = ail.invoice_id
AND p_calling_app_id = 200;
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 fl.language_code , loc.country
FROM hz_party_sites ps, hz_locations loc, iby_payments_all pmt
, fnd_languages fl
WHERE payment_id = p_payment_id
AND fl.language_code = loc.language
AND pmt.party_site_id = ps.party_site_id(+)
AND loc.location_id = ps.location_id;
SELECT pmt.payment_reference_number, pmt.paper_document_number
FROM iby_payments_all pmt
WHERE pmt.payment_id = p_payment_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 hz_loc.location_id , hz_loc.address1 , hz_loc.address2,
hz_loc.address3 , hz_loc.address4 , hz_loc.city, hz_loc.county, hz_loc.state,
hz_loc.province, hz_loc.country, te.iso_territory_code, te.territory_short_name, hz_loc.postal_code,
IBY_FD_EXTRACT_GEN_PVT.format_hz_address(hz_loc.location_id),
IBY_FD_EXTRACT_GEN_PVT.format_hz_address(hz_loc.location_id, 'POSTAL_ADDR')
FROM hz_locations hz_loc,
fnd_territories_vl te
WHERE hz_loc.country = te.territory_code
AND 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", l_location_id),
XMLElement("AddressLine1", l_address1), XMLElement("AddressLine2", l_address2),
XMLElement("AddressLine3", l_address3), XMLElement("AddressLine4", l_address4),
XMLElement("City", l_city), XMLElement("County", l_county),
XMLElement("State", l_state), XMLElement("Province", l_province),
XMLElement("Country", l_country), XMLElement("ISO3DigitCountry", l_iso_territory_code),
XMLElement("CountryName", l_territory_short_name), XMLElement("PostalCode", l_postal_code),
XMLElement("PreFormattedConcatenatedAddress", l_concat_addr),
XMLElement("PreFormattedMailingAddress", l_mail_addr, 'POSTAL_ADDR')) INTO l_hz_addr
FROM DUAL;
SELECT hz_loc.location_id , hz_loc.address1 , hz_loc.address2,
hz_loc.address3 , hz_loc.address4 , hz_loc.city, hz_loc.county, hz_loc.state,
hz_loc.province, hz_loc.country, te.iso_territory_code, te.territory_short_name, hz_loc.postal_code,
IBY_FD_EXTRACT_GEN_PVT.format_hz_address(hz_loc.location_id),
IBY_FD_EXTRACT_GEN_PVT.format_hz_address(hz_loc.location_id, 'POSTAL_ADDR')
FROM hz_locations hz_loc,
fnd_territories_vl te
WHERE hz_loc.country = te.territory_code
AND location_id = p_location_id;
/* SELECT address
FROM IBY_XML_HZ_ADDR_1_0_V
WHERE location_id = p_location_id; */
SELECT te.iso_territory_code , te.territory_short_name
FROM fnd_territories_vl te
WHERE te.territory_code = p_country;
SELECT XMLConcat( XMLElement("Country", p_country),
XMLElement("ISO3DigitCountry", l_te_code),
XMLElement("CountryName", l_te_sname) ) INTO l_hz_addr
FROM dual ;
SELECT XMLConcat( XMLElement("AddressInternalID", l_location_id),
XMLElement("AddressLine1", l_address1), XMLElement("AddressLine2", l_address2),
XMLElement("AddressLine3", l_address3), XMLElement("AddressLine4", l_address4),
XMLElement("City", l_city), XMLElement("County", l_county),
XMLElement("State", l_state), XMLElement("Province", l_province),
XMLElement("Country", l_country), XMLElement("ISO3DigitCountry", l_iso_territory_code),
XMLElement("CountryName", l_territory_short_name), XMLElement("PostalCode", l_postal_code),
XMLElement("PreFormattedConcatenatedAddress", l_concat_addr),
XMLElement("PreFormattedMailingAddress", l_mail_addr, 'POSTAL_ADDR')) INTO l_hz_addr
FROM DUAL;
SELECT hr_loc.location_id, hr_loc.address_line_1, hr_loc.address_line_2, hr_loc.address_line_3,
hr_loc.town_or_city, DECODE(hr_loc.country, 'CA', NULL, hr_loc.region_1),
DECODE(hr_loc.country, 'CA', NULL, hr_loc.region_2), hr_loc.country,
te.iso_territory_code, te.territory_short_name, hr_loc.postal_code,
IBY_FD_EXTRACT_GEN_PVT.format_hr_address(hr_loc.location_id),
IBY_FD_EXTRACT_GEN_PVT.format_hr_address(hr_loc.location_id, 'POSTAL_ADDR'),
hr_loc.location_code
FROM hr_locations_all hr_loc, fnd_territories_vl te
WHERE hr_loc.country = te.territory_code(+)
AND location_id = p_location_id;
SELECT address
FROM IBY_XML_HR_ADDR_1_0_V
WHERE location_id = p_location_id; */
SELECT XMLConcat( XMLElement("AddressInternalID", l_location_id),
XMLElement("AddressLine1", l_address1), XMLElement("AddressLine2", l_address2),
XMLElement("AddressLine3", l_address3), XMLElement("City", l_town_or_city),
XMLElement("County", l_county), XMLElement("State", l_state), XMLElement("Country", l_country),
XMLElement("ISO3DigitCountry", l_iso_territory_code),
XMLElement("CountryName", l_territory_short_name),XMLElement("PostalCode", l_postal_code),
XMLElement("PreFormattedConcatenatedAddress", l_concat_addr),
XMLElement("PreFormattedMailingAddress", l_mail_addr),
XMLElement("AddressName", l_location_code) ) INTO l_hr_addr
FROM DUAL;
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;
SELECT value
INTO l_numeric_char_mask
FROM V$NLS_PARAMETERS
WHERE parameter='NLS_NUMERIC_CHARACTERS';
Select XMLAgg(
XMLElement("CreditCardNumber",p_card_num))
from dual;
SELECT REPLACE(calling_app_doc_ref_number,'.1')
INTO l_base_inv_num
FROM iby_docs_payable_all
WHERE document_payable_id = p_document_payable_id;
SELECT DISTINCT cards.card_reference_id
INTO l_instrid
FROM
ap_expense_report_headers_all hdr,
ap_expense_report_lines_all lines,
ap_credit_card_trxns_all trxns,
ap_cards_all cards
WHERE cards.card_id = trxns.card_id
AND trxns.trx_id = lines.credit_card_trx_id
AND lines.report_header_id = hdr.report_header_id
AND hdr.invoice_num =l_base_inv_num;
select Intermediary_acct_id,
Account_Number,
IBAN,
Bank_Name,
Bank_code,
BRANCH_NUMBER,
BIC,
check_digits,
city,
country_code,
comments
from iby_intermediary_accts
where bank_acct_id = p_bank_acct_id
order by Intermediary_acct_id asc;
/* select XMLCONCAT(
(XMLELEMENT("IntermediaryAccountID", Intermediary_acct_id)),
(XMLELEMENT("BankAccountNumber",Account_Number)),
(XMLELEMENT("IBANNumber",IBAN)),
(XMLELEMENT("BankName",Bank_Name)),
(XMLELEMENT("BankCode", Bank_code)),
(XMLELEMENT("BranchNumber",BRANCH_NUMBER)),
(XMLELEMENT("SwiftCode",BIC)),
(XMLELEMENT("CheckDigits",check_digits)),
(XMLELEMENT("City",city)),
(XMLELEMENT("Country",country_code)),
(XMLELEMENT("Comments",comments))) from iby_intermediary_accts where bank_acct_id = p_bank_acct_id order by Intermediary_acct_id asc;*/
select XMLCONCAT(XMLELEMENT("IntermediaryBankAccount1",
(XMLELEMENT("IntermediaryAccountID", l_Intermediary_acct_id)),
(XMLELEMENT("BankAccountNumber",l_Account_Number)),
(XMLELEMENT("IBANNumber",l_IBAN)),
(XMLELEMENT("BankName",l_Bank_Name)),
(XMLELEMENT("BankCode", l_Bank_code)),
(XMLELEMENT("BranchNumber",l_BRANCH_NUMBER)),
(XMLELEMENT("SwiftCode",l_BIC)),
(XMLELEMENT("CheckDigits",l_check_digits)),
(XMLELEMENT("City",l_city)),
(XMLELEMENT("Country",l_country_code)),
(XMLELEMENT("Comments",l_comments))),
XMLELEMENT("IntermediaryBankAccount2",
(XMLELEMENT("IntermediaryAccountID", l_Intermediary_acct_id2)),
(XMLELEMENT("BankAccountNumber",l_Account_Number2)),
(XMLELEMENT("IBANNumber",l_IBAN2)),
(XMLELEMENT("BankName",l_Bank_Name2)),
(XMLELEMENT("BankCode", l_Bank_code2)),
(XMLELEMENT("BranchNumber",l_BRANCH_NUMBER2)),
(XMLELEMENT("SwiftCode",l_BIC2)),
(XMLELEMENT("CheckDigits",l_check_digits2)),
(XMLELEMENT("City",l_city2)),
(XMLELEMENT("Country",l_country_code2)),
(XMLELEMENT("Comments",l_comments2)) )
)
into l_intermediary_accounts from dual;
SELECT instr.instrument_payment_use_id, instr.attribute_category
FROM iby_pmt_instr_uses_all instr,
iby_payments_all pmt,
iby_external_payees_all payee
WHERE instr.instrument_id = c_ext_bank_account_id
AND InStr.instrument_type = 'BANKACCOUNT'
AND InStr.payment_flow = 'DISBURSEMENTS'
AND pmt.payment_id = c_payment_id
AND payee.ext_payee_id = instr.ext_pmt_party_id
AND
(
(payee.supplier_site_id IS NOT NULL AND payee.org_id IS NOT NULL AND payee.org_type IS NOT NULL AND
payee.supplier_site_id = pmt.supplier_site_id AND
NVL(payee.party_site_id,-1) = NVL(pmt.party_site_id,-1) AND --Bug 14597313
payee.org_id = pmt.org_id AND
payee.org_type = pmt.org_type AND
payee.payment_function = pmt.payment_function AND
payee.payee_party_id = pmt.payee_party_id)
OR
(payee.supplier_site_id IS NULL AND payee.party_site_id IS NOT NULL AND payee.org_id IS NOT NULL AND payee.org_type IS NOT NULL AND
payee.party_site_id = pmt.party_site_id AND
payee.org_id = pmt.org_id AND
payee.org_type = pmt.org_type AND
payee.payment_function = pmt.payment_function AND
payee.payee_party_id = pmt.payee_party_id)
OR
(payee.supplier_site_id IS NULL AND payee.party_site_id IS NOT NULL AND payee.org_id IS NULL AND payee.org_type IS NULL AND
payee.party_site_id = pmt.party_site_id AND
payee.payment_function = pmt.payment_function AND
payee.payee_party_id = pmt.payee_party_id)
OR
(payee.supplier_site_id IS NULL AND payee.party_site_id IS NULL AND payee.org_id IS NULL AND payee.org_type IS NULL AND
payee.payment_function = pmt.payment_function AND
payee.payee_party_id = pmt.payee_party_id)
OR
(payee.supplier_site_id IS NULL AND payee.party_site_id IS NULL AND payee.org_id IS NOT NULL AND payee.org_type IS NOT NULL AND
payee.org_id = pmt.org_id AND
payee.org_type = pmt.org_type AND
payee.payment_function = pmt.payment_function AND
payee.payee_party_id = pmt.payee_party_id)
)
ORDER BY payee.supplier_site_id,
payee.party_site_id,
payee.org_id
;
SELECT XMLCONCAT(XMLELEMENT("PayeeBankAccountAssignment",
XMLELEMENT("DescriptiveFlexField",IBY_EXTRACTGEN_PVT.Get_Dffs('IBY_PMT_INSTR_USES_ALL',l_instr_assign_id, null))))
into g_inter_accts_tbl(l_instr_assign_id) from dual;
SELECT XMLCONCAT(XMLELEMENT("PayeeBankAccountAssignment",
XMLELEMENT("DescriptiveFlexField",IBY_EXTRACTGEN_PVT.Get_Dffs('IBY_PMT_INSTR_USES_ALL',l_instr_assign_id, null))))
into l_assign_dff from dual;