How to load selected rows using Sqlloader

Use "load" option to load selected no of rows.

In below example first row will be loaded into employee table.

Oracle Payables Unmatched Invoice Hold

Date: 25-July-2018
EBS Version: 12.1.3


Business Case: 

Invoices that does not match to a PO (Unmatched Invoice) should go on HOLD.

Reason:

Approval or review not required for invoices that are matched to PO.
Because Purchase Orders documents are already approved.
However if the invoices that are not matched to a PO, business want to review them prior to payments.

How to achieve:

There are 2 options:

1. Custom Hold
2. Supplier Site hold.


Custom Hold:

Create new hold and apply hold using custom program. You may have logic to select invoice to apply hold.

Supplier Site Hold:

This is very standard approach which involves NO additional coding.

If you want to do for specific SUPPLIER then follow below steps:

Go to Payables Manager, Suppliers, Entry
Query supplier, go to Invoice Management Tab
Identify site that needs to go on hold.



















After invoice is created and validated, you will see hold got applied.

hold name is: Matching Required
hold reason: Invoice is not matched to any PO

You may manually release hold and process for payments.


Enable for all Suppliers:

If you want to do for all SUPPLIERS then enable the flag for all existing Supplier Sites.

To default the flag for future Suppliers pleas follow below steps:

Go to Payables Manager, Setup, Options, Payable System Options

Thanks for visiting my blog

Cheers

Suren

Steps to remove Related Application links from PO_PO_APPROVE_PDF_OAF notification

Date: 12-July-2018

Environment: R12.1.3

Agenda: Today will see how to remove "Open Document" link available under "Related Application Links" in Purchase Order Workflow Notification.

Follow below steps:

1. First open the PO Approval Workflow from the database using the Workflow Builder Client software

2. Then open the Messages menu and open the message with internal name “PO_PO_APPROVE_PDF_OAF”  

3. Expand the message and select on Open Document attribute and delete it.

4. Upload the workflow to the database.

5. Now create a new PO and submit for approval and confirm the Related Application links are not there. 

6. If you still see the link then the issue could due to "Preserve Customization" option enabled at the message. Uncheck the option and correct to correct access level, save the workflow and upload to database.


[Alternate approach]


1. Modify PO Approval workflow to call custom function to reset value to "OPEN_FORM_COMMAND" attribute.

2. "OPEN_FORM_COMMAND" get the value from PO_REQAPPROVAL_INIT1.Set_Startup_Values. So once you write with NULL then the link will disappear.

       wf_engine.SetItemAttrText ( itemtype   => itemType
                                 , itemkey    => itemkey
                                 , aname      => 'OPEN_FORM_COMMAND'
                                 , avalue     => 'NULL'

                                 );

Show OADialogPage with 1 button only

How to show OADialogPage page with only 1 button.?


By default OADialogPage will only 2 buttons. To make it only 1 button pass null at the end of dialogPage declaration.

1 2	OADialogPage dialogPage = new OADialogPage(OAException.WARNING, instrMesg, null, "", null);

Example:

1 2 3 4 5 6 7 8 9 10

OAException instrMesg = new OAException("ICX", "CVICX_FREIGHT_TAX_WARNING"); OADialogPage dialogPage = new OADialogPage(OAException.WARNING, instrMesg, null, "", null); dialogPage.setOkButtonItemName("xxYes"); dialogPage.setOkButtonToPost(true); dialogPage.setOkButtonLabel("Continue"); dialogPage.setPostToCallingPage(true);

Thanks for visiting my blog!

Oracle XML gateway cXML PO Direct to Supplier Results Status Code 500 PRIMARY AUTHENTICATION FAILED

EBS version: 12.1.3
Database version: 11.2.0.4


Agenda:

In this article we will see how to resolve "500 PRIMARY AUTHENTICATION FAILED" error.


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE cXML SYSTEM "http://xml.cxml.org/schemas/cXML/1.2.007/cXML.dtd"><cXML version="1.2.005" xml:lang="en-US" payloadID="3/27/2018 8:36:40 AM@www.xxx.com" timestamp="3/27/2018 8:36:40 AM">
  <Response>
    <Status code="500" text="Primary Authentication Failed."></Status>
  </Response>
</cXML>


This issue has mainly caused due to couple of reasons:

Reason: A

Note there could be other reasons also but i am listing what I did to fix.

Get .xml file which was transmitted to trading partner:

Go to system administrator responsibility, Workflow, Transaction Monitor, Transaction Monitor
select Outbound Messages and hit Go to perform search.

Click on xml to view the xml sent to trading partner.

Sample below:

<?xml version="1.0" encoding="US-ASCII" standalone='no'?>
<!DOCTYPE cXML SYSTEM "cXML.dtd">
<!-- Oracle eXtensible Markup Language Gateway Server  -->
<cXML version="1.2.007" payloadID="2018-03-27-09-50-27.914161:0:83.258178051217931149619384968413032891341@" timestamp="2018-03-27T09:50:27+00:00" xml:lang="en">
  <Header>
    <From>
      <Credential domain="DUNS">
        <Identity>Source Company Name</Identity>
      </Credential>
    </From>
    <To>
      <Credential domain="DUNS">
        <Identity>123456789</Identity>
      </Credential>
    </To>
    <Sender>
      <Credential domain="DUNS">
        <Identity>Source Company Name</Identity
 

Ask the trading partner what should be the FROM and TO values populated in the xml file.
If the values are not correct then it won't work.

How to setup From domain, identify and To domain, identify can be found the below oracle document.

IProcurement Punchout CXML Is Showing Wrong 'to Domain Credential' (Doc ID 1965013.1)

Bug: 12818333

<From>
<Credential domain="from domain">                    ===> from profile option PO: cXML From Domain
<Identity>from identity</Identity> <!---                  ===> from profile option PO: cXML From Identity
</Credential>
</From>

<To>
<Credential domain="to domain"> <!---                 ===> trading partner's username
<Identity>to identity</Identity> <!---                      ===> trading partner's source_tp_location_code
</Credential>

Reason: B

Shared Secret key: 

Other reason for the failure can be shared secret key. Check with trading partner if the secret key is required? seeded map (cXML_OrderRequest) does contain secret key by default.

If secret key is not required then it will be a customization:

Highlighted below customization list if secret key is not required:

Create map file by copying from standard (cXML_OrderRequest).
Create package by copying from standard PO_XML_DELIVERY
Modify get_cxml_header_info procedure to not return secret key.
Call the custom package in the map file and
Load the mp file using oracle.apps.ecx.loader.LoadMap command
Modify trading partner setup to use new mapping.

Sample below:

      <Credential domain="DUNS">
        <Identity>Cablevision</Identity>
        <SharedSecret>#WF_DECRYPT#_@#$@49#866@$!!0$9!8^0898469!$^0$0#$840
                               @`B*&+B00}@@0%|#@@#WF_DECRYPT#</SharedSecret>
      </Credential>


In the next blog i will discuss more about SharedSecret key.

PART - 2


Thanks for visiting my blog.

How To Get The Shared Secret For CXML Transactions In Clear Text Rather Than Encrypted Format

Agenda:

In this blog we will discuss how to send shared secret key in clear text.

In 11i by default password will be encrypted. In order to send clear text you may need to do customization. However in r12 you do not have to worry.

Please refer below Oracle support document

How To Get The Shared Secret For CXML Transactions In Clear Text Rather Than Encrypted Format ? (Doc ID 553000.1)
The Shared Secret is Sent Encrypted on cXML Purchase Orders to the Supplier (Doc ID 563954.1)


By default r12 sends the password in plain text. You want to know how? Let's find out.

Check out PO_XML_DELIVERY.get_cxml_header_info package it calls ecx_eng_utils.get_tp_pwd API to get the encrypted password. Hence the order xml file will have the encrypted password available in the payload.

But what happens is before the payload goes into queue process.
ecx_eng_utils.convertEncryCodeClob is called to convert to plan text and then queued. After it is queued again password is encrypted and stored in ECX_DOCLOGS table.

To validate if right password is sent, stop Oracle Transport Agent and test the process.

Since OTA is down the order will be still in the queue table (ECX_OUTQUEUE).

In the user_data column you will see password in plain text.


Thanks for visiting the blog.