R12: Detailed Troubleshooting Guide for AUTOSELECT Issues: When PPR Does Not Select Expected Invoices
How Invoices are Selected in a PPR:
There are two ways a user can submit the Payment Process Request (PPR) Program.
1. Through the Submit Payment Process Request (PsrPG) OA Framework page
This does not require a Payment Process Request Template.
Navigation: AP Responsibility > Payments : Entry > Payments Manager
Payment Process Requests tab
Submit Single Request button
From the parameters entered on this page, the OA Framework will insert the data into the following selection criteria tables (AP_INV_SELECTION_CRITERIA_ALL, AP_LE_GROUP, AP_OU_GROUP, AP_CURRENCY_GROUP, AP_PAY_GROUP)
Once the Data is inserted into the above selection criteria tables, it submits the Payment Process Request Program concurrent program -
This causes the AP_AUTOSELECT_PKG.SELECT_INVOICES procedure to be called.
2. As a Concurrent Request
This requires a Payment Process Request Template.
Navigation: AP Responsibility > Payments : Entry > Payments Manager
On the Home Page Left Side Hierarchy Structure under Concurrent Requests, click Submit a Request
Program Name - Payment Process Request Program
Enter the Template Name, Payment Date, Pay Through Date and Pay From Date on the next page.
Once you submit the Request, It will launch the Auto Select (Payment Process Request Program) Concurrent Program.
Just as above, the AP_AUTOSELECT_PKG.SELECT_INVOICES procedure is called. This in turn calls the CREATE_CHECKRUN procedure, which populates the selection criteria tables (AP_INV_SELECTION_CRITERIA_ALL, AP_LE_GROUP, AP_OU_GROUP, AP_CURRENCY_GROUP, AP_PAY_GROUP). In this case it uses the data in AP_PAYMENT_TEMPLATES and any rows in AP_LE_GROUP, AP_OU_GROUP, AP_CURRENCY_GROUP, and AP_PAY_GROUP associated with that template by template_id.
Inside the AP_AUTOSELECT_PKG.SELECT_INVOICES procedure a large query is run to select all of the rows in ap_payment_schedules_all that match the parameters given when submitting the PPR. The query uses the values shown in the selection criteria tables mentioned above. Additional processing is then done to remove selected invoices that don't meet the criteria, but couldn't initially be excluded because of interest, withholding, or grouping considerations.
Once this processing is complete, the package kicks off the Select Payment Schedules Report (APINVSEL) and determines whether the build program should be submitted. If the user checked "Stop process for review after scheduled payment selection" the PPR will have a status of Review and can be queried up for review on the PPR Search page. If the stop process checkbox was not checked and payment schedules were selected for payment, the IBY build program will be submitted.
If no invoices were selected for payment, the PPR will have a status CANCELLED NO PAYMENTS. This can be seen in ap_inv_selection_criteria_all.status. Invoices that are unselected should show up in the Unselected Invoices section of the Scheduled Payment Selection Report. They will also be in the table ap_unselected_invoices_all. In both places the reason they were unselected is displayed.
Determining Why an Invoice did not get Selected:
The following lists all of the criteria used to select an invoice. By going through this list, you can determine why an invoice was excluded.
1. The invoice should not be selected by any other PPR
Action: Using the aplist, confirm the checkrun_id is null in ap_payment_schedules_all.
2. The payment schedule's relevant date should be between the PPR's Pay From and Pay Through dates. The relevant date for selection can be either a discount date or the due date
(a) If the due date is between the pay from and pay through dates, the payment schedule will be eligible for selection regardless of the discount date.
Action: Find the due_date in ap_payment_schedules_all.
Find the pay_from_date and pay_thru_date in ap_inv_selection_criteria_all.
If the pay_from_date is null, just confirm the due_date is less than or equal to the pay_thru_date.
If the pay_from_date is not null, confirm the due_date is greater than or equal to the pay_from_date and the due_date is less than or equal to the pay_thru_date.
If this test passes, skip to Step 3.
(b) If the due_date doesn't fall within the parameters, we must rely on the discount date(s). However, this is only available when pay_only_when_due_flag is not Y
Action: Check the pay_only_when_due_flag in ap_inv_selection_criteria_all.
If this is Y, the payment schedule did not fall within the required date parameters and was not selected for that reason.
If it is null or N, continue to the next step.
(c) Determine the relevant discount date.
Action: Confirm the pay_date_basis_lookup_code in ap_supplier_sites_all is discount.
If not, the invoice won't be selected.
See if the always_take_disc_flag in ap_supplier_sites_all is Y.
If yes, then the relevant date is the discount_date from ap_payment_schedules_all.
If no, then find the check_date from ap_inv_selection_criteria_all.
Make the following comparisons and take the date from the first one that is true.
If the check_date is on or before discount date, take the discount date.
If the check_date is on or before the second discount date, take the second discount date.
If the check_date is on or before the third discount date, take the third discount date.
If none of the above is true, the invoice will not be selected.
(d) Confirm the relevant discount date is within the date parameters.
If the always_take_disc_flag is Y, the discount date from Step (c) must be between the pay_from_date and the pay_thru_date, inclusive.
If the always_take_disc_flag is Y and the pay_from_date is null, the discount date from Step (c) just has to be on or before the pay_thru_date.
If the always_take_disc_flag is N, the discount date from Step (c) must be between the check date and the pay_thru_date, inclusive.
3. The payment schedule cannot be fully paid
Action: Confirm the payment_status_flag in ap_payment_schedules_all is N or P
Also confirm the payment_status_flag in ap_invoices_all is N or P
4. The invoice cannot be subject to forced revalidation
Action: Confirm the force_revalidation_flag in ap_invoices_all is N or Null.
5. The payment schedule's payment priority must be between the Payment Priority High and Payment Priority Low parameters designated for the PPR
Action: Find the payment_priority from ap_payment_schedules_all.
Null counts as 99.
Confirm this is between the hi_payment_priority and low_payment_priority, inclusive, from ap_inv_selection_criteria_all.
6. Cancelled invoices will not be selected
Action: Confirm the cancelled_date is null in ap_invoices_all.
7. There cannot be a hold on the payment schedule
Action: Confirm the hold_flag in ap_payment_schedules_all is null or N.
8. A supplier site level hold will prevent payment schedule selection
Action: Confirm the hold_all_payments_flag in ap_supplier_sites_all is null or N.
9. For Payment Request invoices there must be a corresponding record in hz_party_sites
Action: If the invoice_type_lookup_code in ap_invoices_all is PAYMENT REQUEST, and the party_site_id from ap_invoices_all is not null, it must match to a party_site_id from hz_party_sites.
10. Fv_econ_benf_disc.ebd_check must return Y for this invoice
This is a federal function that determines whether it is economically beneficial to pay the invoice now. If Federal is not installed, this will always return Y.
Action: Run the following to check for federal installs
set serveroutput on
begin
mo_global.set_policy_context('S',&org_id_from_invoice);
if (FV_INSTALL.ENABLED) then
dbms_output.put_line('Enabled');
else
dbms_output.put_line('Not Enabled');
end if;
end;
If this returns Not Enabled, continue to the next step. If it returns Enabled, you may need to check the federal rates setup for determining economically beneficial payments.
11. If the user specified an Invoice Batch Name, the invoice must be in that batch
Action: If invoice_batch_id is populated in ap_inv_selection_criteria_all, confirm the same invoice_batch_id is populated in ap_invoices_all.
12. If the user specified the Payee the invoice must be from that payee
Action: If the vendor_id is populated in ap_inv_selection_criteria_all, confirm the same vendor_id is populated in ap_invoices_all.
If the party_id is populated in ap_inv_selection_criteria_all, confirm the same party_id is populated in ap_invoices_all.
13. If the user specified an exchange rate type, it must not conflict with the exchange rate type on the invoice
Action: If an IS_USER inv_exchange_rate_type is populated in ap_inv_selection_criteria_all, confirm the exchange_rate_type on the invoice is also User.
If an IS_NOT_USER inv_exchange_rate_type is populated in ap_inv_selection_criteria_all, confirm the exchange_rate_type on the invoice is either null or a non user type.
14. If the user specified a payment method, it must match the payment method on the payment schedule
Action: If the payment_method_code is populated in ap_inv_selection_criteria_all, confirm the same payment_method_code is populated in ap_payment_schedules_all.
15. If the user specified a vendor type, it must match the vendor type of the invoice's supplier
Action: If the vendor_type_lookup_code is populated in ap_inv_selection_criteria_all, confirm the same vendor_type_lookup_code is populated in ap_suppliers.
16. If any legal entities were specified, the invoice must be in one of those legal entities
Action: If there are any rows in ap_le_group for this checkrun_id, the legal_entity_id in ap_invoices_all must match the legal_entity_id from one of those rows.
17. If any operating units were specified, the invoice must be in one of those operating units
Action: If there are any rows in ap_ou_group for this checkrun_id, the org_id in ap_invoices_all must match the org_id from one of those rows.
18. If any currencies were specified, the invoice's payment currency must match one of them
Action: If there are any rows in ap_currency_group for this checkrun_id, the payment_currency_code in ap_invoices_all must match the currency_code from one of those rows.
19. If any pay groups were specified, the invoice's pay group must match one of them
Action: If there are any rows in ap_pay_group for this checkrun_id, the pay_group_lookup_code in ap_invoices_all must match the vendor_pay_group from one of those rows.
20. For zero-amount invoices, the user must have checked the Include Zero Amount checkbox
Action: If the amount_remaining in ap_payment_schedules_all is 0, confirm the zero_invoices_allowed value in ap_inv_selection_criteria_all is Y.
21. Unapproved invoices will not be selected
Action: Confirm the wfapproval_status in ap_invoices_all and the wfapproval_status on each row in ap_invoice_lines_all is either APPROVED, MANUALLY APPROVED or NOT REQUIRED.
24. An invoice with holds will not be selected for payment
Action: Confirm there are no rows in ap_holds_all where release_lookup_code is null.
25. The invoice must be fully validated. A different query is executed based on whether purchase encumbrances are used for any of the included orgs
Action: Determine the value of the purch_encumbrance_flag in financials_system_parameters.
A. If the purch_encumbrance_flag is null or N, confirm none of the rows in ap_invoice_distributions_all have a match_status_flag of N or S.
B. If the purch_encumbrance_flag is Y, confirm none of the rows in ap_invoice_distributions_all have a match_status_flag of N, S or T.
26. Invoices without distributions will not get selected
Action: Confirm there are rows in ap_invoice_distributions_all.
27. For customers using CCR (a Federal functionality) the Invoice Supplier should not be CCR Expired
Action: See the statement on step 10 to determine if Federal is installed. If it is installed confirm there is not a row in FV_TPP_ASSIGNMENTS_V where the beneficiary_party_id and beneficiary_party_site_id match the party_id and party_site_id from ap_invoices_all and the fv_tpp_pay_flag equals N.
28. The check date must be in an open AP period
Action: Confirm the check_date from ap_inv_selection_criteria_all falls between the start_date and end_date of a row in gl_period_statuses with application_id equal to 200 and set_of_books_id equal to the set_of_books_id from ap_invoices_all and closing_status equal to O.
29. If the allow pre-date payables option is disabled, the payment date cannot be earlier than the current date
Action: If the post_dated_payments_flag is N in ap_system_parameters_all, confirm the check_date is not before the created_date on ap_inv_selection_criteria_all.
30. When third-party payments functionality is used the payee relationship must be active
Action: If the relationship_id in ap_invoices_all is not -1, verify the active value in iby_ext_payee_relationships is Y.
31. If exchange rates are mandatory or the exchange rate type is user, exchange rates must be available
Action: Check the status in ap_inv_selection_criteria_all and confirm it is not MISSING RATES.
32. If the maximize credits checkbox is not checked, credit memos will not be selected if they would cause the payment amount to be zero
Action: If the invoice that was not selected has a negative amount, confirm the check amount for the same supplier, site, currency, and payment method plus the credit memo amount would not be zero or less. If so, confirm zero_amounts_allowed is Y in ap_inv_selection_criteria_all.
Confirm the exclusive_payment_flag is not Y in ap_invoices_all for the other invoice selected to be paid.
There are two ways a user can submit the Payment Process Request (PPR) Program.
1. Through the Submit Payment Process Request (PsrPG) OA Framework page
This does not require a Payment Process Request Template.
Navigation: AP Responsibility > Payments : Entry > Payments Manager
Payment Process Requests tab
Submit Single Request button
From the parameters entered on this page, the OA Framework will insert the data into the following selection criteria tables (AP_INV_SELECTION_CRITERIA_ALL, AP_LE_GROUP, AP_OU_GROUP, AP_CURRENCY_GROUP, AP_PAY_GROUP)
Once the Data is inserted into the above selection criteria tables, it submits the Payment Process Request Program concurrent program -
This causes the AP_AUTOSELECT_PKG.SELECT_INVOICES procedure to be called.
2. As a Concurrent Request
This requires a Payment Process Request Template.
Navigation: AP Responsibility > Payments : Entry > Payments Manager
On the Home Page Left Side Hierarchy Structure under Concurrent Requests, click Submit a Request
Program Name - Payment Process Request Program
Enter the Template Name, Payment Date, Pay Through Date and Pay From Date on the next page.
Once you submit the Request, It will launch the Auto Select (Payment Process Request Program) Concurrent Program.
Just as above, the AP_AUTOSELECT_PKG.SELECT_INVOICES procedure is called. This in turn calls the CREATE_CHECKRUN procedure, which populates the selection criteria tables (AP_INV_SELECTION_CRITERIA_ALL, AP_LE_GROUP, AP_OU_GROUP, AP_CURRENCY_GROUP, AP_PAY_GROUP). In this case it uses the data in AP_PAYMENT_TEMPLATES and any rows in AP_LE_GROUP, AP_OU_GROUP, AP_CURRENCY_GROUP, and AP_PAY_GROUP associated with that template by template_id.
Inside the AP_AUTOSELECT_PKG.SELECT_INVOICES procedure a large query is run to select all of the rows in ap_payment_schedules_all that match the parameters given when submitting the PPR. The query uses the values shown in the selection criteria tables mentioned above. Additional processing is then done to remove selected invoices that don't meet the criteria, but couldn't initially be excluded because of interest, withholding, or grouping considerations.
Once this processing is complete, the package kicks off the Select Payment Schedules Report (APINVSEL) and determines whether the build program should be submitted. If the user checked "Stop process for review after scheduled payment selection" the PPR will have a status of Review and can be queried up for review on the PPR Search page. If the stop process checkbox was not checked and payment schedules were selected for payment, the IBY build program will be submitted.
If no invoices were selected for payment, the PPR will have a status CANCELLED NO PAYMENTS. This can be seen in ap_inv_selection_criteria_all.status. Invoices that are unselected should show up in the Unselected Invoices section of the Scheduled Payment Selection Report. They will also be in the table ap_unselected_invoices_all. In both places the reason they were unselected is displayed.
Determining Why an Invoice did not get Selected:
The following lists all of the criteria used to select an invoice. By going through this list, you can determine why an invoice was excluded.
1. The invoice should not be selected by any other PPR
Action: Using the aplist, confirm the checkrun_id is null in ap_payment_schedules_all.
2. The payment schedule's relevant date should be between the PPR's Pay From and Pay Through dates. The relevant date for selection can be either a discount date or the due date
(a) If the due date is between the pay from and pay through dates, the payment schedule will be eligible for selection regardless of the discount date.
Action: Find the due_date in ap_payment_schedules_all.
Find the pay_from_date and pay_thru_date in ap_inv_selection_criteria_all.
If the pay_from_date is null, just confirm the due_date is less than or equal to the pay_thru_date.
If the pay_from_date is not null, confirm the due_date is greater than or equal to the pay_from_date and the due_date is less than or equal to the pay_thru_date.
If this test passes, skip to Step 3.
(b) If the due_date doesn't fall within the parameters, we must rely on the discount date(s). However, this is only available when pay_only_when_due_flag is not Y
Action: Check the pay_only_when_due_flag in ap_inv_selection_criteria_all.
If this is Y, the payment schedule did not fall within the required date parameters and was not selected for that reason.
If it is null or N, continue to the next step.
(c) Determine the relevant discount date.
Action: Confirm the pay_date_basis_lookup_code in ap_supplier_sites_all is discount.
If not, the invoice won't be selected.
See if the always_take_disc_flag in ap_supplier_sites_all is Y.
If yes, then the relevant date is the discount_date from ap_payment_schedules_all.
If no, then find the check_date from ap_inv_selection_criteria_all.
Make the following comparisons and take the date from the first one that is true.
If the check_date is on or before discount date, take the discount date.
If the check_date is on or before the second discount date, take the second discount date.
If the check_date is on or before the third discount date, take the third discount date.
If none of the above is true, the invoice will not be selected.
(d) Confirm the relevant discount date is within the date parameters.
If the always_take_disc_flag is Y, the discount date from Step (c) must be between the pay_from_date and the pay_thru_date, inclusive.
If the always_take_disc_flag is Y and the pay_from_date is null, the discount date from Step (c) just has to be on or before the pay_thru_date.
If the always_take_disc_flag is N, the discount date from Step (c) must be between the check date and the pay_thru_date, inclusive.
3. The payment schedule cannot be fully paid
Action: Confirm the payment_status_flag in ap_payment_schedules_all is N or P
Also confirm the payment_status_flag in ap_invoices_all is N or P
4. The invoice cannot be subject to forced revalidation
Action: Confirm the force_revalidation_flag in ap_invoices_all is N or Null.
5. The payment schedule's payment priority must be between the Payment Priority High and Payment Priority Low parameters designated for the PPR
Action: Find the payment_priority from ap_payment_schedules_all.
Null counts as 99.
Confirm this is between the hi_payment_priority and low_payment_priority, inclusive, from ap_inv_selection_criteria_all.
6. Cancelled invoices will not be selected
Action: Confirm the cancelled_date is null in ap_invoices_all.
7. There cannot be a hold on the payment schedule
Action: Confirm the hold_flag in ap_payment_schedules_all is null or N.
8. A supplier site level hold will prevent payment schedule selection
Action: Confirm the hold_all_payments_flag in ap_supplier_sites_all is null or N.
9. For Payment Request invoices there must be a corresponding record in hz_party_sites
Action: If the invoice_type_lookup_code in ap_invoices_all is PAYMENT REQUEST, and the party_site_id from ap_invoices_all is not null, it must match to a party_site_id from hz_party_sites.
10. Fv_econ_benf_disc.ebd_check must return Y for this invoice
This is a federal function that determines whether it is economically beneficial to pay the invoice now. If Federal is not installed, this will always return Y.
Action: Run the following to check for federal installs
set serveroutput on
begin
mo_global.set_policy_context('S',&org_id_from_invoice);
if (FV_INSTALL.ENABLED) then
dbms_output.put_line('Enabled');
else
dbms_output.put_line('Not Enabled');
end if;
end;
If this returns Not Enabled, continue to the next step. If it returns Enabled, you may need to check the federal rates setup for determining economically beneficial payments.
11. If the user specified an Invoice Batch Name, the invoice must be in that batch
Action: If invoice_batch_id is populated in ap_inv_selection_criteria_all, confirm the same invoice_batch_id is populated in ap_invoices_all.
12. If the user specified the Payee the invoice must be from that payee
Action: If the vendor_id is populated in ap_inv_selection_criteria_all, confirm the same vendor_id is populated in ap_invoices_all.
If the party_id is populated in ap_inv_selection_criteria_all, confirm the same party_id is populated in ap_invoices_all.
13. If the user specified an exchange rate type, it must not conflict with the exchange rate type on the invoice
Action: If an IS_USER inv_exchange_rate_type is populated in ap_inv_selection_criteria_all, confirm the exchange_rate_type on the invoice is also User.
If an IS_NOT_USER inv_exchange_rate_type is populated in ap_inv_selection_criteria_all, confirm the exchange_rate_type on the invoice is either null or a non user type.
14. If the user specified a payment method, it must match the payment method on the payment schedule
Action: If the payment_method_code is populated in ap_inv_selection_criteria_all, confirm the same payment_method_code is populated in ap_payment_schedules_all.
15. If the user specified a vendor type, it must match the vendor type of the invoice's supplier
Action: If the vendor_type_lookup_code is populated in ap_inv_selection_criteria_all, confirm the same vendor_type_lookup_code is populated in ap_suppliers.
16. If any legal entities were specified, the invoice must be in one of those legal entities
Action: If there are any rows in ap_le_group for this checkrun_id, the legal_entity_id in ap_invoices_all must match the legal_entity_id from one of those rows.
17. If any operating units were specified, the invoice must be in one of those operating units
Action: If there are any rows in ap_ou_group for this checkrun_id, the org_id in ap_invoices_all must match the org_id from one of those rows.
18. If any currencies were specified, the invoice's payment currency must match one of them
Action: If there are any rows in ap_currency_group for this checkrun_id, the payment_currency_code in ap_invoices_all must match the currency_code from one of those rows.
19. If any pay groups were specified, the invoice's pay group must match one of them
Action: If there are any rows in ap_pay_group for this checkrun_id, the pay_group_lookup_code in ap_invoices_all must match the vendor_pay_group from one of those rows.
20. For zero-amount invoices, the user must have checked the Include Zero Amount checkbox
Action: If the amount_remaining in ap_payment_schedules_all is 0, confirm the zero_invoices_allowed value in ap_inv_selection_criteria_all is Y.
21. Unapproved invoices will not be selected
Action: Confirm the wfapproval_status in ap_invoices_all and the wfapproval_status on each row in ap_invoice_lines_all is either APPROVED, MANUALLY APPROVED or NOT REQUIRED.
24. An invoice with holds will not be selected for payment
Action: Confirm there are no rows in ap_holds_all where release_lookup_code is null.
25. The invoice must be fully validated. A different query is executed based on whether purchase encumbrances are used for any of the included orgs
Action: Determine the value of the purch_encumbrance_flag in financials_system_parameters.
A. If the purch_encumbrance_flag is null or N, confirm none of the rows in ap_invoice_distributions_all have a match_status_flag of N or S.
B. If the purch_encumbrance_flag is Y, confirm none of the rows in ap_invoice_distributions_all have a match_status_flag of N, S or T.
26. Invoices without distributions will not get selected
Action: Confirm there are rows in ap_invoice_distributions_all.
27. For customers using CCR (a Federal functionality) the Invoice Supplier should not be CCR Expired
Action: See the statement on step 10 to determine if Federal is installed. If it is installed confirm there is not a row in FV_TPP_ASSIGNMENTS_V where the beneficiary_party_id and beneficiary_party_site_id match the party_id and party_site_id from ap_invoices_all and the fv_tpp_pay_flag equals N.
28. The check date must be in an open AP period
Action: Confirm the check_date from ap_inv_selection_criteria_all falls between the start_date and end_date of a row in gl_period_statuses with application_id equal to 200 and set_of_books_id equal to the set_of_books_id from ap_invoices_all and closing_status equal to O.
29. If the allow pre-date payables option is disabled, the payment date cannot be earlier than the current date
Action: If the post_dated_payments_flag is N in ap_system_parameters_all, confirm the check_date is not before the created_date on ap_inv_selection_criteria_all.
30. When third-party payments functionality is used the payee relationship must be active
Action: If the relationship_id in ap_invoices_all is not -1, verify the active value in iby_ext_payee_relationships is Y.
31. If exchange rates are mandatory or the exchange rate type is user, exchange rates must be available
Action: Check the status in ap_inv_selection_criteria_all and confirm it is not MISSING RATES.
32. If the maximize credits checkbox is not checked, credit memos will not be selected if they would cause the payment amount to be zero
Action: If the invoice that was not selected has a negative amount, confirm the check amount for the same supplier, site, currency, and payment method plus the credit memo amount would not be zero or less. If so, confirm zero_amounts_allowed is Y in ap_inv_selection_criteria_all.
Confirm the exclusive_payment_flag is not Y in ap_invoices_all for the other invoice selected to be paid.
No comments:
Post a Comment