The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ins.PRINTER_NAME, ins.payment_instruction_status,
ins.TRANSMIT_INSTR_IMMED_FLAG,
ins.PRINT_INSTRUCTION_IMMED_FLAG, pp.processing_type
FROM iby_pay_instructions_all ins,
iby_payment_profiles pp
WHERE ins.payment_instruction_id = p_payment_instruction_id
AND ins.payment_profile_id = pp.payment_profile_id;
SELECT nvl(pp.disallow_save_print_flag,'N') into l_save_no_output
FROM iby_pay_instructions_all ins,
iby_payment_profiles pp
WHERE ins.payment_instruction_id = p_payment_instruction_id
AND ins.payment_profile_id = pp.payment_profile_id;
SELECT temp.application_short_name,
temp.template_code,
temp.default_language,
temp.default_territory,
decode(template_type_code,
'RTF','PDF',
'ETEXT','ETEXT',
'XSL-XML','XML',
'PDF','PDF')
into
l_appl_name,
l_template_code,
l_template_lang,
l_template_terr,
l_output_format
FROM iby_pay_instructions_all ins,
iby_payment_profiles pp,
iby_formats_b format,
XDO_TEMPLATES_B temp
WHERE ins.payment_instruction_id = p_payment_instruction_id
AND ins.payment_profile_id = pp.payment_profile_id
AND format.FORMAT_CODE = pp.PAYMENT_FORMAT_CODE
AND format.FORMAT_TEMPLATE_CODE = temp.template_code
AND temp.application_id = 673
AND SYSDATE BETWEEN NVL(temp.start_date, SYSDATE) AND NVL(temp.end_date,SYSDATE);
SELECT ins.object_version_number, ins.payment_instruction_status,
pp.processing_type, pp.mark_complete_event, pp.payment_format_code,
pp.print_instruction_immed_flag, ins.process_type
FROM iby_pay_instructions_all ins, iby_payment_profiles pp
WHERE ins.payment_profile_id = pp.payment_profile_id
AND payment_instruction_id = p_payment_instruction_id;
SELECT payment_id, payment_status, object_version_number
FROM iby_payments_all
WHERE payment_instruction_id = p_payment_instruction_id
AND payment_status in ('FORMATTED', 'INSTRUCTION_CREATED');
SELECT payment_id, payment_status, object_version_number
FROM iby_payments_all
WHERE payment_instruction_id = p_payment_instruction_id
AND payment_status in ('READY_TO_REPRINT', 'VOID_BY_SETUP_REPRINT', 'VOID_BY_OVERFLOW_REPRINT');
UPDATE
iby_pay_instructions_all
SET
payment_instruction_status = p_newStatus,
object_version_number = l_instruction_ovn + 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_instruction_id = p_payment_instruction_id;
UPDATE
iby_payments_all
SET
payment_status = 'SUBMITTED_FOR_PRINTING',
object_version_number = l_payment.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;
UPDATE
iby_payments_all
SET
payment_status = 'SUBMITTED_FOR_PRINTING',
object_version_number = l_payment.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;
UPDATE
iby_payments_all
SET
payment_status = 'VOID_BY_SETUP',
object_version_number = l_payment.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;
UPDATE
iby_payments_all
SET
payment_status = 'VOID_BY_OVERFLOW',
object_version_number = l_payment.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;
UPDATE
iby_payments_all
SET
payment_status = 'FORMATTED',
object_version_number = l_payment.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;
UPDATE
iby_payments_all
SET
payment_status = 'FORMATTED',
object_version_number = l_payment.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;
UPDATE
iby_payments_all
SET
payment_status = 'FORMATTED',
object_version_number = l_payment.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;
UPDATE
iby_payments_all
SET
payment_status = 'FORMATTED',
object_version_number = l_payment.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;
UPDATE
iby_payments_all
SET
payment_status = 'TRANSMITTED',
object_version_number = l_payment.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;
UPDATE
iby_payments_all
SET
payment_status = 'FORMATTED',
object_version_number = l_payment.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;
PROCEDURE Insert_Transmission_Error
(
p_payment_instruction_id IN NUMBER,
p_error_code IN VARCHAR2,
p_error_msg IN VARCHAR2
)
IS
l_transaction_error_id NUMBER;
l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Insert_Transmission_Error';
select IBY_TRANSACTION_ERRORS_S.nextval into l_transaction_error_id from dual;
insert into
iby_transaction_errors
(transaction_error_id, transaction_type, transaction_id,
error_code, error_date, error_status, created_by,
creation_date, last_updated_by, last_update_date,
object_version_number, override_allowed_on_error_flag,
do_not_apply_error_flag, error_type, error_message)
values
(l_transaction_error_id, 'PAYMENT_INSTRUCTION', p_payment_instruction_id,
p_error_code, sysdate, 'ACTIVE', fnd_global.user_id,
sysdate, fnd_global.user_id, sysdate,
1, 'Y',
'N', 'BANK', p_error_msg);
END Insert_Transmission_Error;
SELECT document_payable_id
FROM iby_docs_payable_all doc, iby_payments_all pmt,
iby_pay_instructions_all ins
WHERE ins.payment_instruction_id = p_payment_instruction_id
AND ins.payment_instruction_id = pmt.payment_instruction_id
AND doc.straight_through_flag = 'Y'
AND pmt.payment_id = doc.payment_id;
UPDATE
iby_docs_payable_all
SET
straight_through_flag = 'N',
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
document_payable_id = l_doc.document_payable_id;
l_obj_updated BOOLEAN := FALSE;
SELECT pp.payment_profile_id,
pp.periodic_sequence_name_1,
pp.periodic_sequence_name_2,
pp.periodic_sequence_name_3,
pp.last_used_number_1,
pp.last_used_number_2,
pp.last_used_number_3
FROM iby_payment_profiles pp, iby_pay_instructions_all ins
WHERE ins.payment_instruction_id = p_payment_instruction_id
AND ins.payment_profile_id = pp.payment_profile_id;
UPDATE iby_acct_pmt_profiles_b
SET
last_used_number_1 = p_last_val1
WHERE
payment_profile_id = l_payment_profile_id;
l_obj_updated := TRUE;
UPDATE iby_acct_pmt_profiles_b
SET
last_used_number_2 = p_last_val1
WHERE
payment_profile_id = l_payment_profile_id;
l_obj_updated := TRUE;
UPDATE iby_acct_pmt_profiles_b
SET
last_used_number_3 = p_last_val1
WHERE
payment_profile_id = l_payment_profile_id;
l_obj_updated := TRUE;
UPDATE iby_acct_pmt_profiles_b
SET
last_used_number_1 = p_last_val2
WHERE
payment_profile_id = l_payment_profile_id;
l_obj_updated := TRUE;
UPDATE iby_acct_pmt_profiles_b
SET
last_used_number_2 = p_last_val2
WHERE
payment_profile_id = l_payment_profile_id;
l_obj_updated := TRUE;
UPDATE iby_acct_pmt_profiles_b
SET
last_used_number_3 = p_last_val2
WHERE
payment_profile_id = l_payment_profile_id;
l_obj_updated := TRUE;
UPDATE iby_acct_pmt_profiles_b
SET
last_used_number_1 = p_last_val3
WHERE
payment_profile_id = l_payment_profile_id;
l_obj_updated := TRUE;
UPDATE iby_acct_pmt_profiles_b
SET
last_used_number_2 = p_last_val3
WHERE
payment_profile_id = l_payment_profile_id;
l_obj_updated := TRUE;
UPDATE iby_acct_pmt_profiles_b
SET
last_used_number_3 = p_last_val3
WHERE
payment_profile_id = l_payment_profile_id;
l_obj_updated := TRUE;
IF l_obj_updated THEN
UPDATE iby_acct_pmt_profiles_b
SET
object_version_number = object_version_number + 1,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.LOGIN_ID
WHERE
payment_profile_id = l_payment_profile_id;
SELECT object_version_number
FROM iby_pay_instructions_all
WHERE payment_instruction_id = p_payment_instruction_id;
UPDATE
iby_pay_instructions_all
SET
remittance_advice_created_flag = 'Y',
object_version_number = l_instruction_ovn + 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_instruction_id = p_payment_instruction_id;
SELECT object_version_number
FROM iby_pay_instructions_all
WHERE payment_instruction_id = p_payment_instruction_id;
UPDATE
iby_pay_instructions_all
SET
positive_pay_file_created_flag = 'Y',
object_version_number = l_instruction_ovn + 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_instruction_id = p_payment_instruction_id;
SELECT object_version_number
FROM iby_pay_instructions_all
WHERE payment_instruction_id = p_payment_instruction_id;
UPDATE
iby_pay_instructions_all
SET
regulatory_report_created_flag = 'Y',
object_version_number = l_instruction_ovn + 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_instruction_id = p_payment_instruction_id;
SELECT sra.remittance_advice_format_code
FROM iby_pay_instructions_all ins, iby_payment_profiles pp,
iby_remit_advice_setup sra
WHERE payment_instruction_id = p_payment_instruction_id
AND ins.payment_profile_id = pp.payment_profile_id
AND pp.system_profile_code = sra.system_profile_code;
SELECT pp.pay_file_letter_format_code,
pp.positive_pay_format_code,
pp.declaration_report_name
FROM iby_pay_instructions_all ins, iby_payment_profiles pp
WHERE payment_instruction_id = p_payment_instruction_id
AND ins.payment_profile_id = pp.payment_profile_id;
SELECT sra.allow_multiple_copy_flag
FROM iby_pay_instructions_all ins, iby_payment_profiles pp,
iby_remit_advice_setup sra
WHERE payment_instruction_id = p_payment_instruction_id
AND ins.payment_profile_id = pp.payment_profile_id
AND pp.system_profile_code = sra.system_profile_code;
SELECT organization_type, organization_id, name
FROM ce_security_profiles_v;
select APPLICATION_SHORT_NAME
into l_appl_name
from FND_APPLICATION
where APPLICATION_ID = FND_GLOBAL.resp_appl_id;
SELECT po.org_type, po.org_id
FROM iby_pay_instructions_all ins, iby_process_orgs po
WHERE ins.payment_instruction_id = p_payment_instruction_id
AND ins.payment_instruction_id = po.object_id
AND po.object_type = 'PAYMENT_INSTRUCTION';
SELECT 'exist'
FROM ce_security_profiles_v
WHERE organization_type = p_org_type
AND organization_id = p_org_id;
SELECT count(po.org_id)
FROM iby_pay_instructions_all ins, iby_process_orgs po, ce_security_profiles_v ce_sp
WHERE ins.payment_instruction_id = p_payment_instruction_id
AND ins.payment_instruction_id = po.object_id
AND po.object_type = 'PAYMENT_INSTRUCTION'
AND ce_sp.organization_type = po.org_type
AND ce_sp.organization_id = po.org_id;
SELECT count(po.org_id)
FROM iby_pay_service_requests ppr, iby_process_orgs po, ce_security_profiles_v ce_sp
WHERE ppr.payment_service_request_id = p_payment_service_request_id
AND ppr.payment_service_request_id = po.object_id
AND po.object_type = 'PAYMENT_REQUEST'
AND ce_sp.organization_type = po.org_type
AND ce_sp.organization_id = po.org_id;
SELECT count(po.org_id)
FROM iby_pay_service_requests ppr, iby_process_orgs po
WHERE ppr.payment_service_request_id = p_payment_service_request_id
AND ppr.payment_service_request_id = po.object_id
AND po.object_type = 'PAYMENT_REQUEST';
SELECT payment_profile_name
FROM iby_payment_profiles
WHERE payment_profile_id = p_payment_profile_id;
UPDATE iby_acct_pmt_profiles_b
SET
reset_value_1 = nvl(p_reset_value, reset_value_1),
last_used_number_1 = nvl(p_reset_value, reset_value_1),
reset_request_1 = fnd_global.CONC_REQUEST_ID,
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_profile_id = p_payment_profile_id;
UPDATE iby_acct_pmt_profiles_b
SET
reset_value_2 = nvl(p_reset_value, reset_value_2),
last_used_number_2 = nvl(p_reset_value, reset_value_2),
reset_request_2 = fnd_global.CONC_REQUEST_ID,
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_profile_id = p_payment_profile_id;
UPDATE iby_acct_pmt_profiles_b
SET
reset_value_3 = nvl(p_reset_value, reset_value_3),
last_used_number_3 = nvl(p_reset_value, reset_value_3),
reset_request_3 = fnd_global.CONC_REQUEST_ID,
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_profile_id = p_payment_profile_id;
SELECT pay_file_letter_format_code
FROM iby_payment_profiles pp, iby_pay_instructions_all ins
WHERE ins.payment_instruction_id = p_payment_instruction_id
AND ins.payment_profile_id = pp.payment_profile_id;