AR Customer Queries few

SELECT hca.account_number, hps.party_site_number, site_use_code,

       hcsua.LOCATION, hcsua.last_update_date, hcsua.org_id,

       hcsua.primary_flag correct_flag, stg.prd_flag, hcasa.cust_acct_site_id,

       stg.site_use_id

  FROM xxta_primaryflag_use_id18oct stg,

       hz_cust_site_uses_all hcsua,

       hz_cust_acct_sites_all hcasa,

       hz_cust_accounts hca,

       hz_party_sites hps

 WHERE stg.site_use_id = hcsua.site_use_id

   AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id

   AND hcasa.cust_account_id = hca.cust_account_id

   AND hps.party_site_id = hcasa.party_site_id

   AND stg.prd_status = hcsua.status

   AND hcsua.status <> 'I'

   AND stg.site_use_id NOT IN (

          SELECT site_use_id

            FROM bolinf.xxta_cust_site_bill_addr_stg

           WHERE status_stg <> 'LE'

          UNION

          SELECT site_use_id

            FROM bolinf.xxta_bio_sites_inactive_stg)

   AND stg.prd_flag <> hcsua.primary_flag

   AND hcsua.org_id <> 866

SELECT hou.NAME, hp.party_name, hca.account_number, hps.party_site_number,

       ca.status, su.LOCATION, su.site_use_code,

       su.primary_flag site_use_primary_flag, su.status site_use_status,

       su.cust_acct_site_id, su.site_use_id

  FROM hz_cust_site_uses_all su,

       hz_cust_acct_sites_all ca,

       hz_cust_accounts hca,

       hz_party_sites hps,

       hz_parties hp,

       hr_operating_units hou

 WHERE 1 = 1

   AND su.cust_acct_site_id = ca.cust_acct_site_id

   AND ca.cust_account_id = hca.cust_account_id

   AND ca.party_site_id = hps.party_site_id

   AND hca.party_id = hp.party_id

   AND hps.party_id = hp.party_id

   AND hou.organization_id = ca.org_id

   AND (   TRUNC (su.last_update_date) >= TRUNC (SYSDATE - 4)

        OR TRUNC (ca.last_update_date) >= TRUNC (SYSDATE - 4)

       )

   AND su.last_updated_by = 15062

   AND ca.org_id <> 866;

Navigating to Tools > Control from Requisition Summary Results in 'No control actions found for this document'

 SYMPTOMS

When attempting to cancel an Internal Requisition, the user encounters the following error:

No control actions found for this document

Steps:
1. Under the Purchasing responsibility, navigate to Requisitions > Requisition Summary
2. Query the Internal Requisition of interest
3. Navigate to Tools > Control
4. Encounter error: No control actions found for this document

CAUSE

There are records in the OM Interface tables (OE_HEADERS_IFACE_ALL and/or OE_LINES_IFACE_ALL) corresponding to the internal requisition.

SOLUTION

It is recommended users perform the following actions in a test environment first and verify the results prior to performing the actions in production.

1. Log in to the Oracle Application
2. Set Responsibility = Order Management Super User
3. Navigate to Orders, Returns > Import Orders > Corrections
4. Set
   a. Order Source = Internal
   b. Order Reference = requisition_header_id for the Requisition of interest
5. The Corrections form should display a record corresponding to the one in OE_HEADERS_IFACE_ALL. Click the Lines button.
6. The Lines form should display records corresponding to those in OE_LINES_IFACE_ALL. Delete the record(s) and close the Lines form, returning to the Corrections form.
7. In the Corrections form, delete the record.
8. Save
9. Return to the Purchasing application and cancel the desired Internal Requisition/Requisition Lines.

Here are some queries to assist with confirming if there is stuck data:

select * FROM OE_HEADERS_IFACE_ALL
WHERE order_source_id = 10
AND orig_sys_document_ref IN( SELECT REQUISITION_HEADER_ID
FROM PO_REQUISITION_HEADERS_ALL
where segment1 = '&req_number');

select * FROM OE_ORDER_HEADERS_ALL
WHERE order_source_id = 10
AND orig_sys_document_ref IN( SELECT REQUISITION_HEADER_ID
FROM PO_REQUISITION_HEADERS_ALL
where segment1 = '&req_number');


select * FROM OE_ORDER_LINES_ALL
WHERE order_source_id = 10
AND orig_sys_document_ref IN( SELECT REQUISITION_HEADER_ID
FROM PO_REQUISITION_HEADERS_ALL
where segment1 = '&req_number');

select * FROM OE_LINES_IFACE_ALL
WHERE order_source_id = 10
AND orig_sys_document_ref IN( SELECT REQUISITION_HEADER_ID
FROM PO_REQUISITION_HEADERS_ALL
where segment1 = '&req_number');

The Order Management corrections form allows the deletion of the header record without deleting
the corresponding records in oe_lines_iface_all.  If the above scripts return records for only the
oe_lines_iface_all its means that there are orphaned records for that requisition - which is further
impacting the control options in the requisition summary.

The only method to remove the stuck records in the oe_lines_iface_all is to delete them.

The following script can be used - and is recommended only in a test instance first.

delete FROM OE_LINES_IFACE_ALL
WHERE order_source_id = 10
AND orig_sys_document_ref IN( SELECT REQUISITION_HEADER_ID
FROM PO_REQUISITION_HEADERS_ALL
where segment1 = '&req_number');

commit;

APP-PAY-06153 per_pdp_bus.chk_actual_termination_date Error in HR

APP-PAY-06153   per_pdp_bus.chk_actual_termination_date


select * from per_all_people_f
where 1=1
and first_name like '%AXXXX%'
and last_name like '%DaAAAAA%'


select *  from per_all_assignments_f
where 1=1
and person_id =XXX
and BUSINESS_GROUP_ID=XX

--create table bolinf.xxta_per_all_assignments_f as (select *  from per_all_assignments_f
where 1=1
and person_id =XXXXX
and BUSINESS_GROUP_ID=81
and ASSIGNMENT_NUMBER='053202')

commit;

--update per_all_assignments_f
set PERIOD_OF_PLACEMENT_DATE_START = to_date('09-JUN-2014','DD-MON-RRRR')
where 1=1
and person_id =XXXX
and BUSINESS_GROUP_ID=XXX
and ASSIGNMENT_NUMBER='XXXX'

commit;

How does the PPR Process work?

Once the user has entered their desired parameters on the header of the PPR, and clicks on the Submit button, the PPR process follows the following four (4) program steps:

3.1  DOCUMENT SELECTION ("AUTOSELECT") (Code: AP_AUTOSELECT_PKG): The Selection process is handled by Payables (AP), the calling product.  When a PPR is submitted, a record is created in AP_INV_SELECTION_CRITERIA_ALL with a checkrun_name, which is the same as the PPR Name. Selection: Invoices are then selected based on Due Date, Discount Date, Pay Group, and other criteria provided by the user while completing the PPR header. The table AP_SELECTED_INVOICES_ALL is populated with selected invoices. The table AP_UNSELECTED_INVOICES_ALL is populated with the invoices that were not selected. Locking:After selecting the documents, the invoices are locked to prevent other check runs from selecting the same invoices. AP_PAYMENT_SCHEDULES_ALL.checkrun_id is populated on the selected documents (invoices). Review:If the PPR has been setup to Stop Process for Review After Schedule Payment Selection (option available on the header of the PPR), the process stops for user review after the initial selection of payables documents has been completed. The status of the PPR is set to "Invoices Pending Review". After the user reviews and/or modifies the selected documents, and clicks on the Submit button, AP calls the IBYBUILD program. If the Stop Process for Review After Schedule Payment Selection parameter was not enabled, then at the end of invoice selection, the Build program is submitted automatically. If no invoices met the selection criteria, the PPR is canceled automatically and the status of the PPR is set to "Canceled - No Invoices Selected".

3.2  BUILD PAYMENTS (Code: IBY_DISBURSE_SUBMIT_PUB_PKG): The Build Payments process is handled by Oracle Payments (IBY). The Build Payments program first creates a record in IBY_PAY_SERVICE_REQUESTS with call_app_pay_service_req_code = checkrun_name. The Build Payments program goes on to populate the IBY_DOCS_PAYABLE_ALL table with the proposed payments. The link to the payment service request table is through the PAYMENT_SERVICE_REQUEST_ID. Internal Bank Account / Payment Process Profile Assignment(Code: IBY_ASSIGN_PUB): If the PPR has a default internal bank account and Payment Process Profile (PPP) assigned to it on the header of the PPR, the values are assigned to all of the selected documents in the PPR. If a default internal bank account and PPP were not provided by the user on the header of the PPR, Oracle Payments attempts to default the values. If it cannot find a default value for all of the selected documents, the PPR status is set to "INFORMATION REQUIRED". The user display shows it as "Information Required - Pending Action". The user will need to use the Information Required window to provide the missing internal bank account(s) and PPP(s) for each selected document. Document Validation (Code: IBY_VALIDATIONSETS_PUB): During this step, Oracle Payments validates all the documents (selected invoices & memos) using Pre-Defined and User-Defined Validations assigned to Payment Methods assigned to the selected documents. Afterward, the program validates all the documents again, using the Pre-Defined and User-Defined Validations assigned to Payment Formats associated with the PPPs specified on the PPR. If all the documents pass validation, all the documents are set to a status of VALIDATED in the tables and the request status is displayed as "Documents Validated". If there any document validation failures, Oracle Payments uses the parameter setting for "Documents" on the Validation Failure Results tab on the PPR header (the DOCUMENT_REJECTION_LEVEL_CODE) to determine the next action. REQUEST: Reject all documents in this PPR DOCUMENT: Reject only the document in error PAYEE: Reject all the documents related to the supplier NONE: Stop the PPR for review Create Payments (Code: IBY_PAYGROUP_PUB): The validated documents are then grouped into "proposed" payments based on the grouping rules - both User-Defined and hard-coded. It then numbers the proposed payments with an internal identifier (not "the" check number) and validates the payments. Records are inserted into IBY_PAYMENTS_ALL that holds the payment information for the selected documents (invoices). The Build Payments program then updates the IBY_DOCS_PAYABLE_ALL table with the payment_id and formatting_payment_id values of the payment associated with each document. If there any payment validation failures, Oracle Payments uses the parameter setting for "Payments" on the Validation Failure Results tab on the PPR header (the PAYMENT_REJECTION_LEVEL_CODE) to determine the next action. REQUEST: Reject all documents in this PPR DOCUMENT: Reject only the document in error PAYEE: Reject all the documents related to the supplier NONE: Stop the PPR for review If the PPR setup Stop Process for Review After Creation of Proposed Payments is enabled on the Process tab of the PPR header, the displayed PPR status is set to "Pending Proposed Payment Review". This status prevents further processing until user takes action. If this option to stop for a review is not enabled, the displayed status of the PPR is set to "Payments Created". In this status, payment instructions can be created for the PPR.

3.3  FORMAT PAYMENTS (Codes: IBY_PAYINTSR_PUB, IBY_CHECKNUMBER_PUB): The Format Payments process is handled by Oracle Payments (IBY). When a PPR is submitted, the program checks the setting for the Create Payment Instructions parameter on the Process tab of the PPR header to determine if the associated payment instruction(s) (PI) should be created automatically after the payments are created (the CREATE_PMT_INSTRUCTIONS_FLAG = Y), or if the program is to wait for a manual kick-off of the Format Payment Instructions program through the Standard Request Submission form (SRS) (the CREATE_PMT_INSTRUCTIONS_FLAG = N). If the PPR is set up to automatically submit instruction(s), the payment_service_request_id will be populated in IBY_PAYMENT_INSTRUCTIONS_ALL because the instruction will be specific to the PPR. In this case, the instruction(s) can be linked to the PPR using PAYMENT_SERVICE_REQUEST_ID. If the PPR is set up for the user to submit the instruction program manually on the SRS form, then when the instruction(s) is submitted, the instruction(s) is linked to the PPR through the payments selected by the instruction(s). The link in this case will be through the payment_instruction_id in IBY_PAYMENTS_ALL. Sort and number the payments (paper checks and possibly, electronic payments) Create XML extracted message Pass the extract to Oracle XML Publisher (also known as "BI Publisher") XML Publisher applies the formatted template to the payments XML Publisher formats and stores the output Oracle Payments then updates the status of the Payment Instruction(s) and the PPR. If successful, the displayed status of the PPR is "Formatted", and the status of the Payment Instruction(s) will be "Formatted" for electronic payments and "Formatted - Ready for Printing" for check payments Print checks: Users can load stationery into the printer and print checks at this stage by clicking on the Take Action icon for the related Payment Instruction on the Search PPRs window. Determine if the checks printed OK, and if so, click on the Take Action icon again to be taken to the "Record Print Status" window, and click on the Record Print Status button. If there were problems with the printing process (paper jams, skipped checks, etc.) -- especially if you are using pre-numbered check stock -- use the Reprint button to reprint the batch and record any spoiled (ruined) and/or skipped check numbers. Transmit electronic payments: Electronic payments can be transmitted at this point.

3.4  CONFIRM PAYMENTS (Code: AP_PMT_CALLOUT_PKG): The Selection process is handled by Payables (AP). In order to confirm the printing of paper checks, the user needs to use the Record Print Status window to confirm which pre-numbered paper stock printed OK, and which (if any) were skipped or were damaged beyond repair ("spoiled"). Oracle Payments calls ap_pmt_callout_pkg.payment_completed to confirm the payments. During this step, the program does the following: Assigns sequence values for Document Sequencing (Vouchering). Creates data in the AP_CHECKS_ALL table with the appropriate data from the IBY tables. Inserts data into the AP_INVOICE_PAYMENTS_ALL table for the corresponding checks. The documents (invoices) are updated in the AP_PAYMENT_SCHEDULES_ALL table to indicate in the Invoices Workbench the payment details and status. The documents not paid in this PPR are released by setting the checkrun_id on the Payment Schedules to NULL. The AP_INVOICES_ALL table is updated to show the payment status in the Invoices Workbench for those documents that were paid by the PPR. Data for this PPR is deleted from the AP_SELECTED_INVOICES_ALL table. Data for this PPR is deleted from the AP_UNSELECTED_INVOICES_ALL table. "Completing" Electronic Payments: Electronic payments are not "confirmed" in the same way that paper documents are handled. The system will automatically mark electronic payments as "completed" based on the setting you chose for the "Completion Point" field on the header of the associated Payment Process Profile (PPP): "Built" = the payments will be marked as "complete" when the Build process completes "Payment Instruction is Created" = the payments will be marked as "complete" when the PI is created Payment Instruction is Formatted" = the payments will be marked as "complete" when the PI has successfully completed the Formatting process "Payment Instruction is Transmitted" = the payments will be marked as "complete" when they are transmitted"

Reasons for PPR are not selecting the invoices.

Reasons for PPR are not selecting the invoices.

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.