[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NAME
INTO l_le_name
FROM xle_entity_profiles
WHERE legal_entity_id = p_legal_entity_id;
SELECT name
INTO l_ou_name
FROM hr_all_organization_units
WHERE organization_id = p_org_id;
SELECT document_rejection_level_code,
payment_rejection_level_code,
require_prop_pmts_review_flag
INTO p_doc_rejection_level_code,
p_payment_rejection_level_code,
p_payments_review_settings
FROM iby_internal_payers_all
WHERE org_id IS NULL;
SELECT COUNT(DISTINCT payment_instruction_id)
INTO l_ps_count
FROM iby_fd_paymentS_v
WHERE payment_instruction_id is NOT NULL
AND payment_service_request_id = p_psr_id;
| FUNCTION - get_selected_ps_count
| DESCRIPTION - Gets the count of selected scheduled payments for the
| Payment Batch(Pay Run)
|
*==========================================================================*/
FUNCTION get_selected_ps_count(p_checkrun_id IN NUMBER,
p_psr_id IN NUMBER)
RETURN NUMBER IS
l_count1 NUMBER;
l_selected_ps_count NUMBER;
l_selected_ps_count := 0;
SELECT count(*)
INTO l_count1
FROM ap_selected_invoices_All
WHERE checkrun_id = p_checkrun_id
AND original_invoice_id is NULL
AND original_payment_num is NULL
AND ok_to_pay_flag = 'Y';
SELECT count(*)
INTO l_count2
FROM ap_invoice_payments_all aip,
ap_checks_all ac
WHERE aip.check_id = ac.check_id
AND ac.checkrun_id = p_checkrun_id;
l_selected_ps_count := l_count1 + l_count2;
RETURN l_selected_ps_count;
| DESCRIPTION - Gets the count of un-selected scheduled payments for the
| Payment Batch(Pay Run)
|
*==========================================================================*/
FUNCTION Get_unsel_ps_Count(p_checkrun_id IN NUMBER)
RETURN NUMBER IS
l_unsel_ps_count NUMBER;
SELECT count(*)
INTO l_unsel_ps_count
FROM ap_unselected_invoices_All
WHERE checkrun_id = p_checkrun_id;
SELECT count(*)
INTO l_rejected_ps_count
FROM IBY_DOCS_PAYABLE_ALL
WHERE payment_service_request_id = p_psr_id
AND rejected_docs_group_id is NOT NULL;
| but were not selected for a particular dont_pay_reason_code
|
*==========================================================================*/
FUNCTION get_unsel_reason_count(
p_checkrun_id IN NUMBER,
p_dont_pay_code IN VARCHAR2)
RETURN NUMBER IS
l_count NUMBER;
SELECT count(*)
INTO l_count
FROM ap_unselected_invoices_all
WHERE checkrun_id = p_checkrun_id
AND dont_pay_reason_code NOT IN ('NEEDS_INVOICE_VALIDATION',
'FAILED_INVOICE_VALIDATION',
'ZERO INVOICE',
'NEEDS_APPROVAL',
'APPROVER_REJECTED',
'USER REMOVED',
'SCHEDULED_PAYMENT_HOLD',
'SUPPLIER_SITE_HOLD',
'DISCOUNT_RATE_TOO_LOW');
SELECT count(*)
INTO l_count
FROM ap_unselected_invoices_all
WHERE checkrun_id = p_checkrun_id
AND dont_pay_reason_code = p_dont_pay_code;
SELECT count(*)
INTO l_ps_ur_count
FROM ap_selected_invoices_All asi,
ap_system_parameters_all asp
WHERE asi.org_id = asp.org_id
AND asi.checkrun_id = p_checkrun_id
AND asp.base_currency_code = p_ledger_currency_code
AND asi.payment_currency_code = p_payment_currency_code
AND asi.payment_exchange_rate_type = 'User'
AND asi.ok_to_pay_flag = 'Y';
| DESCRIPTION - This is the total count of selected scheduled payments that
| have missing exchange rates
|
*============================================================================*/
FUNCTION Get_missing_rates_ps_Count(p_checkrun_id IN NUMBER)
RETURN NUMBER IS
l_missing_rates_ps_count NUMBER := 0;
SELECT count(*)
INTO l_missing_rates_ps_count
FROM ap_selected_invoices_all asi,
ap_system_parameters_all asp
WHERE asi.org_id = asp.org_id
AND asi.checkrun_id = p_checkrun_id
AND asp.base_currency_code <> asi.payment_currency_code
AND asi.payment_exchange_rate_type = 'User'
AND asi.payment_exchange_rate is NULL
AND asi.ok_to_pay_flag = 'Y'
AND EXISTS (SELECT 'No User Rate'
FROM ap_user_exchange_rates aur
WHERE aur.ledger_currency_code = asp.base_currency_code
AND aur.payment_currency_code = asi.payment_currency_code
AND aur.exchange_rate is NULL);
| DESCRIPTION - This is the Total Interest due for the Selected Payment
| Schedule wrt the Payment Date on the Pay Run. The Interest
| Due is calculated during the AutoSelect/Recalculation.
|
*============================================================================*/
FUNCTION Get_Interest_due(
p_checkrun_id IN NUMBER,
p_invoice_id IN NUMBER,
p_payment_num IN NUMBER
)
RETURN NUMBER IS
l_interest_due NUMBER;
SELECT payment_amount
INTO l_interest_due
FROM ap_selected_invoices_All
WHERE checkrun_id = p_checkrun_id
AND original_invoice_id = p_invoice_id
AND original_payment_num = p_payment_num;
SELECT SUM(NVL(amount_paid,0))
INTO l_interest_paid
FROM ap_invoices_all ai
WHERE invoice_id IN (
SELECT DISTINCT related_invoice_id
FROM ap_invoice_relationships air
WHERE original_invoice_id = p_invoice_id );
| DESCRIPTION - This is the Interest Invoice planned for the Selected
| Scheduled Payment.
| Used in PsDetailsPG
|
*==========================================================================*/
FUNCTION Get_Interest_inv(
p_checkrun_id IN NUMBER,
p_invoice_id IN NUMBER,
p_payment_num IN NUMBER
)
RETURN VARCHAR2 IS
l_interest_inv AP_INVOICES_ALL.invoice_num%TYPE;
SELECT invoice_num
INTO l_interest_inv
FROM ap_selected_invoices_All
WHERE checkrun_id = p_checkrun_id
AND original_invoice_id = p_invoice_id
AND original_payment_num = p_payment_num;
SELECT annual_interest_rate
INTO l_rate
FROM ap_interest_periods
WHERE p_check_date BETWEEN start_date and end_date;
| Name Link navigates the user to Selected Payment Schedules
| Page if the request is not yet submitted to IBY and status
| is 'REVIEW' or 'MISSING RATES'. If the request has been
| submitted to IBY, the link navigates the user to
| IBY_FD_REQUEST_DETAIL Page unless the status is INSERTED' or
| 'SUBMITTED'. For all other statuses the Link is disabled
|
|
*==========================================================================*/
FUNCTION get_destination_function(p_checkrun_id IN NUMBER,
p_status_code IN VARCHAR2)
RETURN VARCHAR2 IS
l_function fnd_form_functions.FUNCTION_NAME%TYPE;
SELECT count(*)
INTO l_count1
FROM ap_selected_invoices_All
WHERE checkrun_id = p_checkrun_id;
SELECT count(*)
INTO l_count2
FROM ap_selected_invoices
WHERE checkrun_id = p_checkrun_id;
SELECT count(*)
INTO l_count1
FROM ap_selected_invoices_All
WHERE checkrun_id = p_checkrun_id;
SELECT count(*)
INTO l_count2
FROM ap_selected_invoices
WHERE checkrun_id = p_checkrun_id;
| The Icon navigates the user to Selected Payment Schedules
| Page if the request is not yet submitted to IBY and status
| is 'REVIEW' or 'MISSING RATES'. If the request has been
| submitted to IBY, the link navigates the user to the
| following pages
|
| IBY Statuses Destinations
| ---------------------------- -----------------------
| INFORMATION_REQUIRED IBY_FD_ASSIGN_COMPLETE
| PENDING_REVIEW_DOC_VAL_ERRORS IBY_FD_DOCS_VALIDATE
| PENDING_REVIEW_PMT_VAL_ERRORS IBY_FD_PAYMENT_VALIDATE
| PENDING_REVIEW IBY_FD_PAYMENT_REVIEW
|
*============================================================================*/
FUNCTION get_action_function(p_checkrun_id IN NUMBER,
p_status_code IN VARCHAR2)
RETURN VARCHAR2 IS
l_function fnd_form_functions.function_name%TYPE;
SELECT count(*)
INTO l_count1
FROM ap_selected_invoices_All
WHERE checkrun_id = p_checkrun_id;
SELECT count(*)
INTO l_count2
FROM ap_selected_invoices
WHERE checkrun_id = p_checkrun_id;
| FUNCTION - Update Payment Schedules
| DESCRIPTION - This method locks the newly added Payment Schedules
|
| Bug 5646905 -- Modified the update statement from the = clause to the IN
| clause so the plan can execute the sub-query first.
|
|
*============================================================================*/
FUNCTION Update_payment_schedules(
p_checkrun_id IN NUMBER)
RETURN VARCHAR2 IS
BEGIN
UPDATE ap_payment_schedules_all APS
SET APS.checkrun_id = p_checkrun_id
WHERE (APS.invoice_id, APS.payment_num ) IN
(SELECT ASI.invoice_id, ASI.payment_num
FROM ap_selected_invoices_all ASI
WHERE APS.invoice_id = ASI.invoice_id
AND APS.payment_num = ASI.payment_num
AND ASI.checkrun_id = p_checkrun_id)
AND APS.checkrun_id is NULL;
l_payment_method_code ap_inv_selection_criteria_all.payment_method_code%TYPE;
l_payment_profile_id ap_inv_selection_criteria_all.payment_profile_id%TYPE;
l_payment_document_id ap_inv_selection_criteria_all.payment_document_id%TYPE;
l_create_instrs_flag ap_inv_selection_criteria_all.create_instrs_flag%TYPE;
SELECT currency_code
FROM ap_currency_group
WHERE checkrun_id = p_checkrun_id;
SELECT legal_entity_id
FROM ap_le_group
WHERE checkrun_id = p_checkrun_id;
SELECT org_id,
'OPERATING_UNIT'
FROM ap_ou_group
WHERE checkrun_id = p_checkrun_id;
SELECT payment_method_code,
payment_profile_id,
bank_account_id,
payment_document_id,
create_instrs_flag
INTO l_payment_method_code,
l_payment_profile_id,
l_int_bank_account_id,
l_payment_document_id,
l_create_instrs_flag
FROM ap_inv_selection_criteria_all
WHERE checkrun_id = p_checkrun_id;
l_curr_arr.DELETE;
l_le_arr.DELETE;
l_ou_arr.DELETE;
l_payment_method_code ap_inv_selection_criteria_all.payment_method_code%TYPE;
l_payment_profile_id ap_inv_selection_criteria_all.payment_profile_id%TYPE;
l_payment_document_id ap_inv_selection_criteria_all.payment_document_id%TYPE;
l_create_instrs_flag ap_inv_selection_criteria_all.create_instrs_flag%TYPE;
SELECT currency_code
FROM ap_currency_group
WHERE template_id = p_template_id;
SELECT legal_entity_id
FROM ap_le_group
WHERE template_id = p_template_id;
SELECT org_id,
'OPERATING_UNIT'
FROM ap_ou_group
WHERE template_id = p_template_id;
SELECT payment_method_code,
payment_profile_id,
bank_account_id,
payment_document_id,
create_instrs_flag
INTO l_payment_method_code,
l_payment_profile_id,
l_int_bank_account_id,
l_payment_document_id,
l_create_instrs_flag
FROM ap_payment_templates
WHERE template_id = p_template_id;
l_curr_arr.DELETE;
l_le_arr.DELETE;
l_ou_arr.DELETE;
SELECT request_id, status
INTO l_request_id, l_status
FROM ap_inv_selection_criteria_all
WHERE checkrun_id = p_checkrun_id;
SELECT request_id, payment_service_request_status
INTO l_request_id, l_psr_status
FROM iby_pay_service_requests
WHERE payment_service_request_id = p_psr_id;
SELECT COUNT(*)
INTO l_pmt_complete_count
FROM iby_payments_all
WHERE payment_service_request_id = p_psr_id
AND payment_instruction_id is NOT NULL
AND ROWNUM =1;