Saturday, October 4, 2014

Payment Process Request (PPR) Status and Descriptions in R12

Payment Process Request is one of the new comers in R12, which normally transitions through the different stages of processing, the PPR will display a "Status" to let you know where in the process the PPR has progressed to, and what's going on with it. There are more number of statuses associated with PPR. Here in this post we tried to give most witnessed statuses and their details.
PPR PROCESS STATUSES
Description
NEW
This status indicates that the PPR has been successfully submitted for processing, and the AutoSelect program is digesting the criteria provided by the user on the header of the PPR in preparation of the automatic selection the invoices and memos related to that criteria.
SELECTING INVOICES
This status indicates that the AutoSelect program is selecting the eligible invoices/memos for the payment batch based on Due Date, Discount Date, Pay Group, and other criteria provided by the user on the header of the PPR
CANCELLED - NO INVOICES SELECTED
If no invoices or memos met the selection criteria provided by the user on the header of the PPR, the PPR is automatically terminated and the status changes to this status.
MISSING..." STATUSES
Other statuses may appear at this point in the process if the user failed to included required information on the PPR header, such as "Missing Exchange Rates", etc.
INVOICES SELECTED
After selecting the documents (invoices/memos), they are locked to prevent other checkruns from selecting the same documents
INVOICES PENDING REVIEW
This status will only appear if you selected the "Stop Process for Review After Scheduled Payment Selection" option on the Processing tab of the PPR header. This status means that the PPR process has stopped, and is waiting for you to review the invoices and memos that were selected for payment (and make any changes to the batch, as needed). Click on the Take Action icon to be taken to the Review Proposed Payments window
CALCULATING SPECIAL AMOUNTS
This status will only appear if you selected the "Calculate Payment Withholding and Interest During the Scheduled Payment Selection" option on the Processing tab of the PPR header. This status means that interest and withholding tax are being calculated and applied, as necessary, to the invoices and memos selected for this payment batch
ASSEMBLING/ASSEMBLED PAYMENTS
An "interim" status, it appears after the calculation for interest and withholding has been completed, and the Build Payments program is starting. It may appear again later after the user provides any required bank account and PPP information for the invoices/memos ("documents") selected
INFORMATION REQUIRED - PENDING ACTION
This status appears if you did not provide a default Internal (Disbursement) Bank Account and/or PPP on the header of the PPR. In that case, you need to click on the Take Action icon to be taken to a form where you can decide which internal bank account and PPP should be used for each invoice and memo selected for payment
PENDING PROPOSED PAYMENT REVIEW
This status will only appear if you selected the "Stop Process for Review After Creation of Proposed Payments" option on the Processing tab of the PPR header. In this case, the system is waiting for you to review (and modify, if needed) the proposed payments for this batch. Click on the Take Action icon to be taken to the "Review Proposed Payments" window
FORMATTING
This status indicates that the proposed payments have been turned into payment instruction files. At this point, you will want to click on the Show link to view the new associated payment instruction file(s). Each payment instruction file with have their own PI Reference Number. If you have both electronic and paper ("check") payments involved in this payment batch, you will see a payment instruction file for each type of payment method
CONFIRMED PAYMENT
Once the payment instructions have been transmitted/printed and confirmed, the Status of the PPR changes to this status to indicate a successfully completed payment batch (PPR)
TERMINATED
If the user terminates a PPR anytime prior to confirmation of the payments (using the Terminate icon), the status will change to "Terminated", and the PPR is permanently closed

The PPR status is Cancelled - No invoices selected.

Issue
A Payment Process Request (PPR) does not select any invoices. The PPR status is Cancelled - No invoices selected.

Solution
This issue was resolved by opening the accounting period.

Payment Process Requests Functional and Technical information

Payment Process Requests

Overview

Under Funds disbursement page, users can submit Payment Process Requests (PPR) to generate payments. There is an option to submit a single Payment Process Request or schedule Payment Process Requests.

There are four steps in the processing of a PPR.
a) Document selection
b) Build Payments
c) Format Payments
d) Confirm Payments

Document selection and Confirm Payments are handled by Payables (AP) code while Build Payments and Format payments are handled by Payments (IBY) code.

Submitting a Single Payment Process Request

Mandatory Fields - Payment process request name, pay through date
Under Payment Attributes tab – Payment Date, Payment Exchange rate type.
Payment Process Profile and Disbursement bank account are optional fields.

Under Processing tab, options are available to stop the process after document selection / payment and also how to create Payment Instruction.

Under Validation Failure Results tab, choose option that best suits the business needs regarding how to handle validation failure on document(s) or payment(s).

Click on Submit to submit the Payment process request.

Document Selection – Payables

Code: AP_AUTOSELECT_PKG

When a Payment Process request is submitted, a record is created in AP_INV_SELECTION_CRITERIA_ALL with a checkrun_name which is the same as the payment process request name.

Payment Profile and Internal Bank Account from which payments have to be made do not have to be specified during invoice selection. User who submits the PPR does not need know this information. These values can be provided by at a later stage by a Payments Manager or Administrator.

Selection: Invoices are then selected based on due date, discount date, paygroup and other criteria provided by the user while submitting the PPR. The selection process is handled by the calling product

The table AP_SELECTED_INVOICES_ALL is populated with selected invoices.
AP_UNSELECTED_INVOICES_ALL is populated with unselected invoices.

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.

Review:
If the Payment Process Request has been setup to ‘Stop Process for Review After Scheduled Payment Selection’, the process stops for user review. The status of the PPR is set to Invoices Pending Review.

If the ‘Stop Process for Review After Scheduled Payment Selection’ was not enabled, at the end of invoice selection, build program is submitted automatically.

If no invoices met the selection criteria and no payment schedules selected for payment, the PPR is cancelled automatically and the status of the PPR is set to “Cancelled - No Invoices Selected”

If user review required, after the user reviews the selected payment schedules and clicks on Submit, AP calls the IBYBUILD program.

Valid Statuses and actions
At the end of this step, the valid statuses are
a) Invoices Pending Review or
b) Cancelled - No Invoices Selected or
c) Other statuses from missing information such as Missing Exchange rates

If PPR status is Cancelled-No Invoices Selected, there are no valid actions available.
For others, the actions available are
a) Terminate the PPR or
b) Modify / proceed to submit the PPR and start the build process.

Build Payments - Payments

Code: IBY_DISBURSE_SUBMIT_PUB_PKG

Build Payments creates record in IBY_PAY_SERVICE_REQUESTS with call_app_pay_service_req_code = checkrun_name.

Primary Key: PAYMENT_SEVICE_REQUEST_ID

Key Columns:
CALL_AP_PAY_SERVICE_REQ_CODE -> PPR name
CALLING_APP_ID
PAYMENT_SERVICE_REQUEST_STATUS
INTERNAL_BANK_ACCOUNT_ID
MAXIMUM_PAYMENT_AMOUNT
MINIMUM_PAYMENT_AMOUNT
DOCUMENT_REJECTION_LEVEL_CODE
PAYMENT_REJECTION_LEVEL_CODE
REQUIRE_PROP_PMTS_REVIEW_FLAG
CREATE_PMT_INSTRUCTIONS_FLAG

Note: The displayed status of the PPR is generated by ibyvutlb.pls
There is a get_psr_status function that derives the display sttaus of the PPR on the dashboard.

Some of the values for PAYMENT_SERVICE_REQUEST_STATUS in the table are
PAYMENT_SERVICE_REQUEST_STATUS
------------------------------
DOCUMENTS_VALIDATED
INFORMATION_REQUIRED
INSERTED
PAYMENTS_CREATED
PENDING_REVIEW
TERMINATED
VALIDATION_FAILED

The build program populates the IBY_DOCS_PAYABLE_ALL table with the payments. Link to the payment service request table is through PAYMENT_SERVICE_REQUEST_ID.

Key Columns:
Payment_service_request_id
Calling_app_doc_ref_number -> invoice_number
Document_payable_id
Document_status
Payment_currency_code
Payment_amount
Document_amount
Exclusive_payment_flag
Payment_method_code
Payment_id
Formatting_payment_id
Ext_payee_id
Payee_party_id
Payment_profile_id
Internal_bank_account_id
Calling_app_doc_unique_ref2 -> invoice_id
Calling_app_doc_unique_ref3 -> payment number


a) Internal Bank Account/Payment Process Profile Assignment:
Code: IBY_ASSIGN_PUB

If the payment process request has the internal bank account and payment profile assigned to it, the same is assigned to all the documents in the PPR.

If a default internal bank account and PPP were not provided when submitting the PPR, Oracle Payments attempts to default the values. If it cannot find a default value for all the documents, the PPR is set to INFORMATION REQUIRED status. The display status of the PPR is “Information Required - Pending Action”
User should complete the missing information and Run Payment Process to continue.

b) Document Validation
Code: IBY_VALIDATIONSETS_PUB

During this step, Oracle Payments validates all the documents using Payment Method based validations and then payment format based validations..

b.1 - If all the documents pass validation, all the documents are set to a status of VALIDATED and the request status is set to ‘Documents Validated’.

b.2 – If there are any validation failures, Oracle Payments uses the system option used while submitting the PPR to determine the next action.

The DOCUMENT_REJECTION_LEVEL_CODE of the PPR can have the following values which determine how the document processing will continue when there is a validation failure
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 request for review

b.2.1 – REQUEST
The status of the payment process request is updated to ‘Failed Document Validation’. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request.

b.2.2 – DOCUMENT
Oracle Payments rejects all documents that failed validation. Oracle Payments then calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.

b.2.3 – PAYEE
Oracle Payments rejects all documents for the supplier that had one or more documents that failed validation. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.

c) 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.

Example: If exclusive_payment_flag = Y on a document, its paid on a separate payment.
It then numbers the payments (internal identifier not the check numbering) and validates the created payments.

Records are inserted into IBY_PAYMENTS_ALL that holds the payment information for the selected documents.

The build program then updates the IBY_DOCS_PAYABLE_ALL table with the payment_id and formatting_payment_id values that corresponding to the payment that pays the document.

IBY_PAYMENTS_ALL links through payment_service_request_id.

Key Columns:
Payment_service_request_id
Payment_id
Payment_method_code
Payment_status
Payments_complete_flag
Payment_amount,
Dicount_amount_taken
Internal_bank_Account_id
Ext_payee_id
Payment_instruction_id
Payment_profile_id
Void_date

The PAYMENT_REJECTION_LEVEL_CODE can have the following values which determine how the payment processing will continue when there is a validation failure
REQUEST – Reject all payments in the request
PAYMENT – Reject only those payments in error
NONE – Stop the request for review

Request – Entire PPR is rejected. Oracle Payments raises a business event that calls AP to release the documents. The status of the payment process request and proposed payments is updated to ‘REJECTED’.

Payment – Payments that failed validation are rejected and AP releases the documents that belong to the payment that failed validation. The other payments are accepted. The accepted payments get a status of ‘CREATED’.

None – Payments that failed Validation are set to ‘Failed Validation’ and allows for user intervention. Status of the PPR is set to ‘PENDING REVIEW’

If in the PPR setup, ‘Stop Process for Review After Creation of Proposed Payments’ is enabled, the PPR status is set to ‘Pending Proposed Payment Review’. This status prevents further processing until user takes action. If this option to stop for review is not enabled, the status of the PPR is set to ‘Payments Created’. In this status, payment instruction can be created for the PPR.

Format Payments - Payments

Code: IBY_PAYINTSR_PUB, IBY_CHECKNUMBER_PUB

When a PPR is submitted, there are two options

The CREATE_PMT_INSTRUCTIONS_FLAG can be a Y or N
Y – Payment Instruction will be automatically created after payments are created.
N – Application waits for standard request submission for Payment Instruction.

IBY_PAY_INSTRUCTIONS_ALL stores the payment instruction information.

If the PPR is setup to automatically submit instruction, the payment_service_request_id will be populated in iby_pay_instructions_all because the instruction will be specific to the PPR In this case, the instruction can be linked to the PPR using PAYMENT_SERVICE_REQUEST_ID

If the PPR processing is setup for the user to submit the instruction as a standard request, then when the instruction is submitted, then the instruction is linked to the PPR through the payments selected by the instruction.
The link in this case will be through iby_payments_all.payment_instruction_id


Key Columns in IBY_PAY_INSTRUCTIONS_ALL
Payment_instruction_id
Payment_profile_id
Payment_instruction_status
Payments_complete_code
Payment_count
Print_instruction_immed_flag
Transmit_instr_immed_flag
Internal_bank_account_id
Payment_document_id
Payment_date
Payment_reason_code
Payment_currency_code

Format:
The following processing occurs during the format step.

a) Number the payments – Check Numbering
b) Create XML Extract message
c) Pass the extract to XML publisher
d) Oracle XML Publisher (BI publisher) applies the format template
e) BI publisher formats and stores the output
f) Oracle Payments then updates the status of the Payment Instruction and the Payments. If successful, the status of Payments and Instruction is ‘Formatted’.

Print Checks:
a) Users can load stationery into the printer and print checks at this stage.
b) Determine if the checks printed ok. If not reprint

Confirm Payments - Payables

Code: AP_PMT_CALLOUT_PKG

Record Print Status of the checks to confirm the payments. Oracle Payments calls ap_pmt_callout_pkg.payment_completed to confirm the payments.

This does the following:
a) Assigns sequence/values – Document sequencing.
b) Creates data in AP_CHECKS_ALL with appropriate data from IBY tables.
Checkrun_name = ppr name and checkrun_id = checkrun_id from IBY table.
c) Data inserted into AP_INVOICE_PAYMENTS_ALL for the corresponding checks.
d) AP_PAYMENT_SCHEDULES_ALL for the invoices are updated to indicate the payment details and status.
e) The documents paid in this PPR are released by setting the checkrun_id on the payment schedules to null.
f) AP_INVOICES_ALL is udpated to show payment status
g) Data is deleted from the AP_SELECTED_INVOICES_ALL
h) Data is deleted from AP_UNSELECTED_INVOICES_ALL

Monday, September 29, 2014

Format Payment Instructions Useful Scripts


Payment Templates – Details of Payment Templates defined:

 SELECT apt.template_id, apt.template_name,
 ipmv.payment_method_name pmt_method_name,
 cba.bank_account_name bank_acct_name,
 ipp.payment_profile_name pmt_profile_name,
 cpd.payment_document_name pmt_doc_name, apt.pay_group_option pay_grp_optn,
 apt.ou_group_option ou_grp_optn, apt.currency_group_option curr_grp_optn,
 apt.description ppp_description, apt.inactive_date, apt.addl_pay_thru_days,
 apt.addl_pay_from_days, apt.low_payment_priority, apt.hi_payment_priority,
 apt.vendor_id, apt.pay_only_when_due_flag,
 apt.vendor_type_lookup_code vdr_type_lcode, apt.bank_account_id,
 apt.payment_profile_id, apt.zero_inv_allowed_flag, apt.payment_method_code,
 apt.inv_exchange_rate_type, apt.payment_date_option, apt.addl_payment_days,
 apt.payment_exchange_rate_type, apt.zero_amounts_allowed,
 apt.payables_review_settings, apt.calc_awt_int_flag, apt.payments_review_settings,
 apt.document_rejection_level_code doc_reject_lvl, apt.create_instrs_flag,
 apt.payment_rejection_level_code pmt_reject_lvl, apt.payment_document_id,
 plc.displayed_field supplier_type, pv.vendor_name payee,
 alc1.displayed_field template_type_name, gdct.user_conversion_type user_rate_type,
 fu.user_name
 FROM ap_payment_templates apt, po_lookup_codes plc, iby_payment_methods_vl ipmv,
 iby_payment_profiles ipp, ce_bank_accounts cba, ap_lookup_codes alc1,
 gl_daily_conversion_types gdct, po_vendors pv, fnd_user fu, ce_payment_documents cpd
 WHERE fu.user_id = apt.last_updated_by
 AND plc.lookup_code(+) = apt.vendor_type_lookup_code
 AND plc.lookup_type(+) = 'VENDOR TYPE'
 AND cba.bank_account_id(+) = apt.bank_Account_id
 AND ipmv.payment_method_code(+) = apt.payment_method_code
 AND alc1.lookup_type(+) = 'PAYMENT_TEMPLATE_TYPE'
 AND alc1.lookup_code(+) = apt.template_type
 AND gdct.conversion_type(+) = apt.payment_exchange_rate_type
 AND ipp.payment_profile_id(+) = apt.payment_profile_id
 AND pv.party_id(+) = apt.party_id
 AND apt.payment_document_id = cpd.payment_document_id(+)
 -- Uncomment to limit to specific Payment Templates
 -- AND apt.TEMPLATE_NAME LIKE 'J%'
ORDER BY apt.template_name, ipmv.payment_method_name;

 Payment Process Profiles – Verification of your PPP setup:

SELECT app.payment_profile_name, ppp.system_profile_code, ppp.payment_format_code,
 pm.payment_method_name, ppp.periodic_sequence_name_1 seq_name,
 app.payment_profile_id pmt_prof, app.reset_value_1 reset_val,
 app.last_used_number_1 last_used, ppp.processing_type,
 ppp.mark_complete_event mrk_cmplt, ppp.outbound_pmt_file_directory,
 ppp.outbound_pmt_file_prefix, ppp.outbound_pmt_file_extension ext,
 ppp.positive_pay_file_directory, ppp.positive_pay_file_prefix,
 ppp.positive_pay_file_extension pp_ext, ppp.default_payment_document_id def_pmt_docid,
 ppp.last_update_date, sra.sra_override_payee_flag sra_ovrd_payee,
 fmt.format_name sra_fmt, sra.automatic_sra_submit_flag sra_autosub
 FROM iby_sys_pmt_profiles_b ppp, iby_acct_pmt_profiles_vl app,
 iby_applicable_pmt_profs appl_prof, iby_payment_methods_vl pm,
 iby_remit_advice_setup sra, iby_formats_vl fmt
WHERE 1 = 1
 AND ppp.system_profile_code = app.system_profile_code
 AND ppp.system_profile_code = appl_prof.system_profile_code(+)
 AND appl_prof.applicable_value_to = pm.payment_method_code(+)
 AND NVL (appl_prof.applicable_type_code, 'PAYMENT_METHOD') = 'PAYMENT_METHOD'
 AND ppp.system_profile_code = sra.system_profile_code(+)
 AND sra.remittance_advice_format_code = fmt.format_code(+)
 -- Only custom PPP’s, if this is your naming convention
 AND ppp.system_profile_code LIKE 'XX%';

Payment Documents – Setup data, by internal disbursing Bank Account:

 SELECT acct.bank_account_name, acct.bank_account_num, acct.currency_code,
 acct.multi_currency_allowed_flag multi_curr, acct.payment_multi_currency_flag pmt_mc,
 pmtdoc.payment_document_name pmt_doc_name, fmt.format_name,
 pmtdoc.first_available_document_num first_avail_docno,
 pmtdoc.last_available_document_number last_avail_docno,
 pmtdoc.last_issued_document_number last_issued_docno,
 pmtdoc.payment_document_id pmt_doc_id, pmtdoc.internal_bank_account_id,
 pmtdoc.paper_stock_type, pmtdoc.attached_remittance_stub_flag attchd_stub,
 pmtdoc.number_of_lines_per_remit_stub lines_per_stub, pmtdoc.format_code,
 pmtdoc.inactive_date, fu1.user_name created_by, pmtdoc.creation_date,
 fu2.user_name last_updated_by, pmtdoc.last_update_date,
 DECODE (inactive_date, NULL, 'Y', 'N') status
 FROM ce_payment_documents pmtdoc, iby_formats_vl fmt, ce_bank_accounts acct,
 fnd_user fu1, fnd_user fu2
 WHERE pmtdoc.format_code = fmt.format_code
 AND pmtdoc.internal_bank_account_id = acct.bank_account_id
 AND pmtdoc.created_by = fu1.user_id
 AND pmtdoc.last_updated_by = fu2.user_id
 -- Uncomment to limit to a certain document name
 -- AND payment_document_name LIKE '%'
ORDER BY acct.bank_account_name, pmtdoc.payment_document_name;

Layout Templates - Metadata and RTF/eText files for all Outbound Payment Instruction Layout Templates:

SELECT xtv.application_short_name tmpl_app, xtv.template_code,
 xtv.ds_app_short_name ds_app, xtv.data_source_code, xtv.template_type_code tmpl_type,
 xtv.default_language def_lang, xtv.default_territory def_terr, xtv.template_status,
 xtv.start_date, xtv.end_date, xtv.template_name, xtv.description, xtv.created_by,
 xtv.creation_date, xtv.last_updated_by, xtv.last_update_date, xtv.last_update_login,
 (SELECT application_name
 FROM fnd_application_vl
 WHERE application_short_name = xtv.application_short_name) application_name,
 (SELECT meaning
 FROM fnd_lookups
 WHERE lookup_type = 'XDO_TEMPLATE_TYPE'
 AND lookup_code = xtv.template_type_code) template_type,
 (SELECT data_source_name
 FROM xdo_ds_definitions_vl
 WHERE data_source_code = xtv.data_source_code
 AND application_short_name = xtv.ds_app_short_name) data_source_name,
 (SELECT file_name
 FROM xdo_lobs
 WHERE ( ( lob_type = 'TEMPLATE'
 AND xdo_file_type != 'RTF'
 AND xdo_file_type = xtv.template_type_code
 AND xdo_lobs.LANGUAGE = xtv.default_language
 AND xdo_lobs.territory = xtv.default_territory)
 OR ( lob_type = 'TEMPLATE_SOURCE'
 AND xdo_file_type IN ('RTF', 'RTF-ETEXT')
 AND xdo_lobs.LANGUAGE = xtv.default_language
 AND xdo_lobs.territory = xtv.default_territory)
 OR ( xdo_file_type = 'RTF'
 AND lob_type = 'TEMPLATE_SOURCE'
 AND LANGUAGE = xtv.mls_language
 AND territory = xtv.mls_territory
 AND EXISTS
 (SELECT mls.lob_code
 FROM xdo_lobs mls
 WHERE mls.lob_type = 'MLS_TEMPLATE'
 AND mls.lob_code = xtv.template_code
 AND mls.application_short_name = xtv.application_short_name
 AND mls.LANGUAGE = xtv.default_language
 AND mls.territory = xtv.default_territory)
 AND NOT EXISTS
 (SELECT LOCAL.lob_code
 FROM xdo_lobs LOCAL
 WHERE LOCAL.lob_type = 'TEMPLATE_SOURCE'
 AND LOCAL.lob_code = xtv.template_code
 AND LOCAL.application_short_name = xtv.application_short_name
 AND LOCAL.LANGUAGE = xtv.default_language
 AND LOCAL.territory = xtv.default_territory)))
 AND lob_code = xtv.template_code
 AND xdo_lobs.application_short_name = xtv.application_short_name)
 AS default_template_file,
 (SELECT file_name FROM xdo_lobs
 WHERE lob_type = 'TEMPLATE_SOURCE' AND lob_code = xtv.template_code
 AND xdo_lobs.application_short_name =xtv.application_short_name
 AND xdo_lobs.LANGUAGE = xtv.mls_language
 AND xdo_lobs.territory = xtv.mls_territory) AS mls_template_file,
 (SELECT NAME
 FROM fnd_iso_languages_vl
 WHERE iso_language_2 = xtv.default_language) AS default_file_lang,
 DECODE (xtv.default_territory, '00', '', ftv.territory_short_name) AS default_file_terr,
 xtv.mls_language, xtv.mls_territory, xtv.default_output_type
 FROM xdo_templates_vl xtv, fnd_application_vl fav, fnd_territories_vl ftv
WHERE 1 = 1
 AND fav.application_short_name = xtv.application_short_name
 AND ftv.territory_code(+) = xtv.default_territory
 -- Only Outbound Payment Instructions
 AND xtv.application_short_name = 'IBY'
 AND data_source_code = 'IBY_FD_INSTRUCTION_1_0';

PPR and Source XML – Payment Process Requests formatted today, with the source XML:

SELECT trxnmid,
 EXTRACTVALUE (
 XMLType (document),
 '/OutboundPaymentInstruction/PaymentInstructionInfo/UserAssignedRefCode') ppr_name,
 EXTRACTVALUE (
 XMLType (document),
 '/OutboundPaymentInstruction/PaymentInstructionInfo/FormatProgramRequestID')
 fmt_request_id,
 EXTRACTVALUE (
 XMLType (document),
 '/OutboundPaymentInstruction/PaymentProcessProfile/PaymentProcessProfileName')
 ppp_name,
 EXTRACTVALUE (
 XMLType (document),
 '/OutboundPaymentInstruction/CheckFormatInfo/PaymentDocumentName')
 pmt_doc_name,
 EXTRACTVALUE (
 XMLType (document),
 '/OutboundPaymentInstruction/InstructionTotals/PaymentCount') pmt_cnt,
 document raw_xml
 FROM iby_trxn_documents itd
WHERE 1 = 1
 AND itd.creation_date > TRUNC (SYSDATE);

Monday, September 8, 2014

Script to Fix Items showing Negative Qty on ONhand Quantity Form in Oracle.


Script to Fix Items showing Negative Qty on ONhand Quantity Form in Oracle.

The Script will perform
 script will get rid of outstanding reservation records that exist for order lines which are closed or cancelled.For both external sales orders and internal orders.Also script cleans reservations left after Order line is deleted but reservations exist.Script deletes from mtl_demand which are not in sync with Mtl_reservations.

SELECT  V.PROFILE_OPTION_VALUE OE_PROFILE
FROM    FND_PROFILE_OPTION_VALUES V
WHERE   (V.PROFILE_OPTION_ID, V.APPLICATION_ID, V.LEVEL_ID) =
                (SELECT V2.PROFILE_OPTION_ID,V2.APPLICATION_ID,MAX(V2.LEVEL_ID)
                FROM FND_PROFILE_OPTIONS OO,
                     FND_PROFILE_OPTION_VALUES V2
                WHERE ((V2.LEVEL_ID = 10001 AND V2.LEVEL_VALUE=0)
                          OR (V2.LEVEL_ID = 10002 AND V2.LEVEL_VALUE=660))
                AND   OO.PROFILE_OPTION_ID = V2.PROFILE_OPTION_ID
                AND   OO.APPLICATION_ID = V2.APPLICATION_ID
                AND   OO.APPLICATION_ID = 660
                AND   UPPER(OO.PROFILE_OPTION_NAME) = 'ONT_SOURCE_CODE'
                GROUP BY V2.PROFILE_OPTION_ID, V2.APPLICATION_ID)
AND   V.LEVEL_VALUE = DECODE(V.LEVEL_ID, 10001, 0, 10002, 660);

PROMPT ** This gets rid of extraneous demand and reservations that are left
PROMPT ** after a line has been cancelled  

SELECT L.LINE_ID, L.HEADER_ID
FROM OE_ORDER_LINES_ALL L, MTL_RESERVATIONS M
WHERE  M.PRIMARY_RESERVATION_QUANTITY>0
AND nvl(L.CANCELLED_FLAG,'N')='Y'
AND L.CANCELLED_QUANTITY IS NOT NULL
AND L.LINE_ID = M.DEMAND_SOURCE_LINE_ID
AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI
                WHERE  MTI.TRX_SOURCE_LINE_ID = L.LINE_ID
                AND    MTI.SOURCE_HEADER_ID = L.HEADER_ID
                AND    MTI.SOURCE_CODE = '&OE_SOURCE_CODE');


UPDATE MTL_RESERVATIONS
SET    PRIMARY_RESERVATION_QUANTITY = 0
,      RESERVATION_QUANTITY = 0
,      LAST_UPDATED_BY=-2471362
WHERE  PRIMARY_RESERVATION_QUANTITY>0
AND    EXISTS (SELECT 'X'
               FROM   OE_ORDER_LINES_ALL L
               WHERE  nvl(L.CANCELLED_FLAG,'N')='Y'
               AND L.CANCELLED_QUANTITY IS NOT NULL
               AND L.LINE_ID = MTL_RESERVATIONS.DEMAND_SOURCE_LINE_ID
               AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI
                               WHERE  MTI.TRX_SOURCE_LINE_ID = L.LINE_ID
                               AND    MTI.SOURCE_HEADER_ID = L.HEADER_ID
                               AND    MTI.SOURCE_CODE = '&OE_SOURCE_CODE'));



PROMPT ** This gets rid of extraneous demand and reservations that are left
PROMPT ** after a line has been closed or delivery detail is cancelled

SELECT L.LINE_ID, L.HEADER_ID
FROM OE_ORDER_LINES_ALL L, MTL_RESERVATIONS M
WHERE   M.PRIMARY_RESERVATION_QUANTITY>0
AND nvl(L.OPEN_FLAG,'Y')='N'
AND L.LINE_ID = M.DEMAND_SOURCE_LINE_ID
AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI
                WHERE  MTI.TRX_SOURCE_LINE_ID = L.LINE_ID
                AND    MTI.SOURCE_HEADER_ID = L.HEADER_ID
                AND    MTI.SOURCE_CODE = '&OE_SOURCE_CODE')
AND NOT EXISTS    (SELECT 1 FROM WSH_DELIVERY_DETAILS WDD
                 WHERE WDD.SOURCE_LINE_ID=L.LINE_ID
                   AND WDD.SOURCE_CODE ='OE'
                   AND WDD.INV_INTERFACED_FLAG IN ('N','P')
                   AND WDD.RELEASED_STATUS <> 'D');


UPDATE MTL_RESERVATIONS
SET    PRIMARY_RESERVATION_QUANTITY = 0
,      RESERVATION_QUANTITY = 0
,      LAST_UPDATED_BY=-2471362
WHERE  PRIMARY_RESERVATION_QUANTITY>0
AND    EXISTS (SELECT 'X'
               FROM   OE_ORDER_LINES_ALL L
               WHERE  nvl(L.OPEN_FLAG,'Y')='N'
               AND L.LINE_ID = nvl(MTL_RESERVATIONS.DEMAND_SOURCE_LINE_ID,-99)
               AND NOT EXISTS (SELECT NULL FROM MTL_TRANSACTIONS_INTERFACE MTI
                               WHERE  MTI.TRX_SOURCE_LINE_ID = L.LINE_ID
                               AND    MTI.SOURCE_HEADER_ID = L.HEADER_ID
                               AND    MTI.SOURCE_CODE = '&OE_SOURCE_CODE')
               AND NOT EXISTS    (SELECT 1 FROM WSH_DELIVERY_DETAILS WDD
                 WHERE WDD.SOURCE_LINE_ID=L.LINE_ID
                   AND WDD.SOURCE_CODE ='OE'
                   AND WDD.INV_INTERFACED_FLAG IN ('N','P')
                   AND WDD.RELEASED_STATUS <> 'D'));



PROMPT ORDER DETAILS WITH ORPHAN RESERVATIONS AFTER LINE IS DELETED

SELECT MSO.SEGMENT1 ORD_NUMBER,
       MSO.SEGMENT2 ORD_TYPE,
       MSO.SALES_ORDER_ID sALES_ORDER_ID,
       MR.DEMAND_SOURCE_LINE_ID oRDER_LINE_ID,
       MR.iNVENTORY_ITEM_ID iTEM_ID,
       MR.ORGANIZATION_ID ORGANIZATION_ID,
       MR.PRIMARY_RESERVATION_QUANTITY pRSV_QTY,
       MR.RESERVATION_QUANTITY RSV_QTY
 FROM MTL_RESERVATIONS MR,
      MTL_SALES_ORDERS MSO
WHERE MSO.SALES_ORDER_ID=MR.DEMAND_SOURCE_HEADER_ID
  AND MR.DEMAND_SOURCE_TYPE_ID IN (2,8)
  AND MR.DEMAND_SOURCE_LINE_ID NOT IN (SELECT LINE_ID FROM OE_ORDER_LINES_ALL
                                         WHERE LINE_ID=MR.DEMAND_SOURCE_LINE_ID );


PROMPT UPDATING MTL_RESERVATIONS

UPDATE MTL_RESERVATIONS MR
SET PRIMARY_RESERVATION_QUANTITY=0,
    RESERVATION_QUANTITY=0,
    LAST_UPDATED_BY=-2471362
   WHERE MR.DEMAND_SOURCE_TYPE_ID IN (2,8)
     AND MR.DEMAND_SOURCE_LINE_ID NOT IN (SELECT LINE_ID FROM OE_ORDER_LINES_ALL
                                            WHERE LINE_ID=MR.DEMAND_SOURCE_LINE_ID);
COMMIT;

PROMPT **Updating records for closed /cancelled lines in mtl_demand where records are not in sync
UPDATE MTL_DEMAND D
SET    PRIMARY_UOM_QUANTITY = 0
,      LINE_ITEM_QUANTITY   = 0
,      COMPLETED_QUANTITY   = 0
,      LINE_ITEM_RESERVATION_QTY = 0
,      RESERVATION_QUANTITY = 0
,      LAST_UPDATED_BY=-2471362
WHERE  DEMAND_SOURCE_TYPE IN (2,8)
AND RESERVATION_TYPE = 2
AND    DEMAND_SOURCE_LINE = ( SELECT O.LINE_ID
                                FROM OE_ORDER_LINES_ALL O
                                WHERE  ( NVL(o.OPEN_FLAG,'Y') = 'N'
                                         OR NVL(O.CANCELLED_FLAG,'N') ='Y')
                                AND  O.LINE_ID =D.DEMAND_SOURCE_LINE )
AND   DEMAND_SOURCE_LINE NOT IN  ( select TRX_SOURCE_LINE_ID
                    FROM  MTL_TRANSACTIONS_INTERFACE MTI
                    WHERE  MTI.SOURCE_LINE_ID = D.DEMAND_SOURCE_LINE)
AND DEMAND_SOURCE_LINE NOT IN (SELECT SOURCE_LINE_ID
                   FROM WSH_DELIVERY_DETAILS WDD
                 WHERE WDD.SOURCE_LINE_ID=D.DEMAND_SOURCE_LINE
                   AND WDD.SOURCE_CODE='OE'
                   AND WDD.INV_INTERFACED_FLAG IN ('N','P'));

PROMPT FINAL UPDATING MTL_DEMAND

UPDATE MTL_DEMAND SET PRIMARY_UOM_QUANTITY=0,
                      COMPLETED_QUANTITY=0,
                      RESERVATION_QUANTITY=0,
                      LAST_UPDATED_BY=-2471362
       WHERE DEMAND_SOURCE_TYPE IN (2,8)
         AND RESERVATION_TYPE=2
         AND DEMAND_ID IN (SELECT N_COLUMN1 FROM MTL_RESERVATIONS WHERE
                                    DEMAND_SOURCE_TYPE_ID IN (2,8)
                                AND PRIMARY_RESERVATION_QUANTITY=0
                                AND RESERVATION_QUANTITY=0
                                AND LAST_UPDATED_BY=-2471362);



PROMPT **Deleting reservations which are complete

COMMIT;

DELETE FROM MTL_RESERVATIONS WHERE
       DEMAND_SOURCE_TYPE_ID IN (2,8)
 AND   LAST_UPDATED_BY=-2471362;


PROMPT **Deleting records from mtl_demand where reservations are complete

DELETE FROM MTL_DEMAND WHERE
    DEMAND_SOURCE_TYPE IN (2,8)
AND RESERVATION_TYPE=2     
AND LAST_UPDATED_BY=-2471362;

Monday, August 11, 2014

How to change vacancy number on Employee Person Record - Data Fix

How to change  vacancy number on Employee Person Record - Data Fix

There is no way to get the vacancy number changed or update via application.
Best option here would be to cancel the current hire done and rehire the person against the correct vacancy number.
Since the cancel hire option is available via application, it is suggested to use the same to get the data corrected.

select * from per_all_people_f where employee_number='100124';

select * from per_vacancies
where name in ('IRC18200','IRC10722');

update per_all_assignments_f
set job_id=553,grade_id=76,organization_id=1911,position_id=264233,vacancy_id=18200,
recruiter_id=2881
where vacancy_id=10722
and effective_start_date = to_date('28-JUL-2014','DD-MON-YYYY')

Tuesday, July 15, 2014

SQL Query to print Column Values in Row wise

SELECT
   XXHeader_seq,JOB1,value
FROM
   Q_GTF_AUTOCLAVE_LOAD_DATA_V
UNPIVOT
EXCLUDE NULLS
(
   VALUE
   FOR
     JOB1
   IN
   (
      XX_JOB_1,
      XX_JOB_2,
      XX_JOB_3,
      XX_JOB_4,
      XX_JOB_5
   )
)
WHERE
   XXHeader_seq=8980;
  
  
   select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
  connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null;

Wednesday, July 9, 2014

Oracle WebCenter Sites Check Patch



Applies to:
Oracle WebCenter Sites - Version 7.6.2 and later


Goal
We have installed a patch on WebCenter Sites, how can we validate that the patch has been successfully applied?
 
Solution
There are a few ways to validate:
  1. Check HelloCS (open a browser to http://{host}:{port}/cs/HelloCS), it should report jars as being at the revision stated in the patch Readme.txt
  2. Log into Admin UI, on the lefthand tree applet under the Admin tab use the System Tools -> System Information -> Sites Info to report all jar versions. This should show all jars, and the jars listed in the patch's Readme with their respective build dates / revisions numbers, should match the build dates / revision numbers jars you see here.
  3. There is no simple way to check other steps (e.g. that elements were imported correctly, or that files were copied into the installation directly), except to review the output of having done each of these to be sure there were no errors.