Oracle ERP cloud Receivables transactions- Usage and sample payload

Hello All,

I have recently working on Oracle Cloud ERP and used web services to create transactions in Receivables module.
I am sharing some sample web service payload as it is extremely difficult to find them online

Invoice Service

URL https//yourserver/fscmService/RecInvoiceService

Operation: createInterfaceLine

Usage: Use this service to create one billing transaction at a time. This will give you more control over data created but this will create one billing transaction at a time. You can use it to create both Invoice or Credit memo (without invoice reference) in real time.

Sample Payload for Invoice: 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/receivables/transactions/invoices/invoiceService/types/" xmlns:inv="http://xmlns.oracle.com/apps/financials/receivables/transactions/invoices/invoiceService/" xmlns:tran="http://xmlns.oracle.com/apps/flex/financials/receivables/transactions/autoInvoices/TransactionLineInterfaceLineDff/" xmlns:tran1="http://xmlns.oracle.com/apps/financials/receivables/transactions/autoInvoices/model/flex/TransactionInterfaceGdf/" xmlns:tran2="http://xmlns.oracle.com/apps/financials/receivables/transactions/autoInvoices/model/flex/TransactionLineInterfaceGdf/" xmlns:tran3="http://xmlns.oracle.com/apps/flex/financials/receivables/transactions/autoInvoices/TransactionInterfaceLinkToDff/" xmlns:tran4="http://xmlns.oracle.com/apps/flex/financials/receivables/transactions/autoInvoices/TransactionInterfaceReferenceDff/" xmlns:tran5="http://xmlns.oracle.com/apps/flex/financials/receivables/transactions/autoInvoices/TransactionLineDff/" xmlns:tran6="http://xmlns.oracle.com/apps/flex/financials/receivables/transactions/autoInvoices/TransactionInterfaceHeaderDff/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <soapenv:Header/>
   <soapenv:Body>
      <typ:createInterfaceLine>
          <typ:interfaceLine>
             <inv:OrgId>300000001751545</inv:OrgId>
            <inv:Amount currencyCode="USD">100</inv:Amount>
             <inv:BatchSourceName>Invoice Source</inv:BatchSourceName>
            <inv:CustomerTrxTypeName>Invoice Trans</inv:CustomerTrxTypeName>
            <inv:TrxDate>2020-06-05</inv:TrxDate>
            <inv:CurrencyCode>USD</inv:CurrencyCode>
           <inv:ConversionType>Corporate</inv:ConversionType>
              <inv:Description>CAT Dollars</inv:Description>
            <inv:GlDate>2020-06-05</inv:GlDate>
            <inv:LineType>LINE</inv:LineType>
            <inv:PaymentTermsName>Net 30</inv:PaymentTermsName>
           <inv:BillCustomerAccountNumber>100043514</inv:BillCustomerAccountNumber>
            <inv:BillCustomerSiteNumber>25015</inv:BillCustomerSiteNumber>
            <inv:Quantity unitCode="Ea">1</inv:Quantity>
           <inv:UnitSellingPrice currencyCode="USD">100</inv:UnitSellingPrice>
             <inv:TrxNumber>100043514-7001</inv:TrxNumber>
            <inv:UOMName>Each</inv:UOMName>
           <inv:TransactionInterfaceLineDff  xsi:type="tran:context">
              <tran:__FLEX_Context>Context</tran:__FLEX_Context>
<tran:id>100043514</tran:id>
<tran:number>7002</tran:number>
            </inv:TransactionInterfaceLineDff>
            <inv:TransactionInterfaceHeaderDff  xsi:type="tran6:headerContext">
               <tran6:__FLEX_Context>Header Context</tran6:__FLEX_Context>
    <tran6:id></tran6:id>
   <tran6:code></tran6:code>
            </inv:TransactionInterfaceHeaderDff>        
         </typ:interfaceLine>
      </typ:createInterfaceLine>
   </soapenv:Body>
</soapenv:Envelope>

Please note that payment method is required for invoice but not for credit memo as due date calculation will be based on payment method.
In case you need more details on DFF update, please refer my other blog for DFF value update.

Sample Payload for Credit Memo:

 <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/receivables/transactions/invoices/invoiceService/types/" xmlns:inv="http://xmlns.oracle.com/apps/financials/receivables/transactions/invoices/invoiceService/" xmlns:tran="http://xmlns.oracle.com/apps/flex/financials/receivables/transactions/autoInvoices/TransactionLineInterfaceLineDff/" xmlns:tran1="http://xmlns.oracle.com/apps/financials/receivables/transactions/autoInvoices/model/flex/TransactionInterfaceGdf/" xmlns:tran2="http://xmlns.oracle.com/apps/financials/receivables/transactions/autoInvoices/model/flex/TransactionLineInterfaceGdf/" xmlns:tran3="http://xmlns.oracle.com/apps/flex/financials/receivables/transactions/autoInvoices/TransactionInterfaceLinkToDff/" xmlns:tran4="http://xmlns.oracle.com/apps/flex/financials/receivables/transactions/autoInvoices/TransactionInterfaceReferenceDff/" xmlns:tran5="http://xmlns.oracle.com/apps/flex/financials/receivables/transactions/autoInvoices/TransactionLineDff/" xmlns:tran6="http://xmlns.oracle.com/apps/flex/financials/receivables/transactions/autoInvoices/TransactionInterfaceHeaderDff/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <soapenv:Header/>
   <soapenv:Body>
      <typ:fscmService/RecInvoiceService>
          <typ:interfaceLine>
            <inv:OrgId>300000001751545</inv:OrgId>
            <inv:Amount currencyCode="USD">-1000</inv:Amount>
             <inv:BatchSourceName>Credit Memo Source</inv:BatchSourceName>
            <inv:CustomerTrxTypeName>CreditMemo Trans</inv:CustomerTrxTypeName>
            <inv:TrxDate>2020-06-05</inv:TrxDate>
             <inv:CurrencyCode>USD</inv:CurrencyCode>
             <inv:Description>Dollars</inv:Description>
            <inv:GlDate>2020-06-05</inv:GlDate>
            <inv:LineType>LINE</inv:LineType>
           <inv:BillCustomerAccountNumber>10004</inv:BillCustomerAccountNumber>
             <inv:BillCustomerSiteNumber>25015</inv:BillCustomerSiteNumber>
            <inv:Quantity unitCode="Ea">1</inv:Quantity>
            <inv:TrxNumber>7002</inv:TrxNumber>
            <inv:UOMName>Each</inv:UOMName>
           <inv:TransactionInterfaceLineDff  xsi:type="tran:context">
              <tran:__FLEX_Context>Context</tran:__FLEX_Context>
<tran:id>100043514</tran:id>
<tran:number>7002</tran:number>
            </inv:TransactionInterfaceLineDff>
            
            <inv:TransactionInterfaceHeaderDff  xsi:type="tran6:headerContext">
               <tran6:__FLEX_Context>Header Context</tran6:__FLEX_Context>
    <tran6:id></tran6:id>
   <tran6:code></tran6:code>
            </inv:TransactionInterfaceHeaderDff>      
         </typ:interfaceLine>
      </typ:createInterfaceLine>
   </soapenv:Body>
</soapenv:Envelope>

Please note that payment method is required for invoice but not for credit memo as due date calculation will be based on payment method.
In case you need more details on DFF update, please refer my other blog for DFF value update.

Operation: updateInterfaceLine

Usage: Use this service to create billing transactions in bulk. This is more useful for batch operations but provide you limited results in return. You can use it to create both Invoice or Credit memo (without invoice reference)

Sample Payload for Invoice: 

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 

xmlns:typ="http://xmlns.oracle.com/apps/financials/receivables/transactions/invoices/invoiceService/types/" 

xmlns:inv="http://xmlns.oracle.com/apps/financials/receivables/transactions/invoices/invoiceService/" 

xmlns:tran="http://xmlns.oracle.com/apps/flex/financials/receivables/transactions/autoInvoices/TransactionLineInterfaceLineDff/" 

xmlns:tran1="http://xmlns.oracle.com/apps/financials/receivables/transactions/autoInvoices/model/flex/TransactionInterfaceGdf/" 

xmlns:tran2="http://xmlns.oracle.com/apps/financials/receivables/transactions/autoInvoices/model/flex/TransactionLineInterfaceGdf/" 

xmlns:tran3="http://xmlns.oracle.com/apps/flex/financials/receivables/transactions/autoInvoices/TransactionInterfaceLinkToDff/" 

xmlns:tran4="http://xmlns.oracle.com/apps/flex/financials/receivables/transactions/autoInvoices/TransactionInterfaceReferenceDff/" 

xmlns:tran5="http://xmlns.oracle.com/apps/flex/financials/receivables/transactions/autoInvoices/TransactionLineDff/" 

xmlns:tran6="http://xmlns.oracle.com/apps/flex/financials/receivables/transactions/autoInvoices/TransactionInterfaceHeaderDff/" 

xmlns:typ1="http://xmlns.oracle.com/adf/svc/types/"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

   <soapenv:Header/>

   <soapenv:Body>

      <typ:processInterfaceLine>

         <typ:changeOperation>Create</typ:changeOperation>

         <!--Zero or more repetitions:-->

         <typ:interfaceLine>

              <inv:OrgId>300000001751545</inv:OrgId>

            <inv:Amount currencyCode="USD">100</inv:Amount>

             <inv:BatchSourceName>Invoice Source</inv:BatchSourceName>

            <inv:CustomerTrxTypeName>Invoice Trans</inv:CustomerTrxTypeName>

            <inv:TrxDate>2020-06-05</inv:TrxDate>

            <inv:CurrencyCode>USD</inv:CurrencyCode>

           <inv:ConversionType>Corporate</inv:ConversionType>

              <inv:Description>CAT Dollars</inv:Description>

            <inv:GlDate>2020-06-05</inv:GlDate>

            <inv:LineType>LINE</inv:LineType>

            <inv:PaymentTermsName>Net 30</inv:PaymentTermsName>

           <inv:BillCustomerAccountNumber>100043514</inv:BillCustomerAccountNumber>

            <inv:BillCustomerSiteNumber>25015</inv:BillCustomerSiteNumber>

            <inv:Quantity unitCode="Ea">1</inv:Quantity>

           <inv:UnitSellingPrice currencyCode="USD">100</inv:UnitSellingPrice>

             <inv:TrxNumber>100043514-7001</inv:TrxNumber>

            <inv:UOMName>Each</inv:UOMName>

<inv:TransactionInterfaceLineDff  xsi:type="tran:context">

              <tran:__FLEX_Context>Context</tran:__FLEX_Context>

<tran:id>100043514</tran:id>

<tran:number>7002</tran:number>

            </inv:TransactionInterfaceLineDff>

            <inv:TransactionInterfaceHeaderDff  xsi:type="tran6:headerContext">

               <tran6:__FLEX_Context>Header Context</tran6:__FLEX_Context>

    <tran6:id></tran6:id>

   <tran6:code></tran6:code>

            </inv:TransactionInterfaceHeaderDff>              

         </typ:interfaceLine>

         <typ:interfaceLine>

              <inv:OrgId>300000001751545</inv:OrgId>

            <inv:Amount currencyCode="USD">100</inv:Amount>

             <inv:BatchSourceName>Invoice Source</inv:BatchSourceName>

            <inv:CustomerTrxTypeName>Invoice Trans</inv:CustomerTrxTypeName>

            <inv:TrxDate>2020-06-05</inv:TrxDate>

            <inv:CurrencyCode>USD</inv:CurrencyCode>

           <inv:ConversionType>Corporate</inv:ConversionType>

              <inv:Description>CAT Dollars</inv:Description>

            <inv:GlDate>2020-06-05</inv:GlDate>

            <inv:LineType>LINE</inv:LineType>

            <inv:PaymentTermsName>Net 30</inv:PaymentTermsName>

           <inv:BillCustomerAccountNumber>100043514</inv:BillCustomerAccountNumber>

            <inv:BillCustomerSiteNumber>25015</inv:BillCustomerSiteNumber>

            <inv:Quantity unitCode="Ea">1</inv:Quantity>

           <inv:UnitSellingPrice currencyCode="USD">100</inv:UnitSellingPrice>

             <inv:TrxNumber>100043514-7002</inv:TrxNumber>

            <inv:UOMName>Each</inv:UOMName>

<inv:TransactionInterfaceLineDff  xsi:type="tran:context">

              <tran:__FLEX_Context>Context</tran:__FLEX_Context>

<tran:id>100043514</tran:id>

<tran:number>7002</tran:number>

            </inv:TransactionInterfaceLineDff>

            <inv:TransactionInterfaceHeaderDff  xsi:type="tran6:headerContext">

               <tran6:__FLEX_Context>Header Context</tran6:__FLEX_Context>

    <tran6:id></tran6:id>

   <tran6:code></tran6:code>

            </inv:TransactionInterfaceHeaderDff>              

</typ:interfaceLine>

         <typ:processControl>

            <typ1:returnMode>Key</typ1:returnMode>

            <typ1:exceptionReturnMode>Key</typ1:exceptionReturnMode>

            <typ1:partialFailureAllowed>true</typ1:partialFailureAllowed>

         </typ:processControl>

      </typ:processInterfaceLine>

   </soapenv:Body>

</soapenv:Envelope>

Please refer Oracle Documentation for more details.

Calling Web Services Dynamically

In normal SOA architecture projects, we have static web services which get consumed by clients but in some cases ( especially for Orchestration) we may need to call web services dynamically through client.

There are several ways available to consume services dynamically and you may get lots of material on Google for these methods. I am collecting some of the ways I have tried out, for reference purpose and will let you know pros and cons of each of them.

Following are the ways to call web services dynamically
1) Call through a Java client
2) Call through a PL/SQL procedure
3) Call through a BPEL process.

1) Call through a Java Client: As I am from Java back ground, I used it as my first choice.
With use of javax.xml and javax.jws package, one can invoke web services.
Advantage of calling web services through Java client is, it is very easy to maintain code. Exception handling is also very rich. Formatting and altering xml input and output data is also quite easy.
Limitations/issues with this approach are, though it supports all types of protocols but it require to write code separately for each format. Second issue with this approach is, it require XML to object and object to XML conversion, which make it a bit slow.
Following is a code snippet for your help. I am calling a service support SOAP1.2 RPC.

public String callService(String namespace, String Servicename,
String porttype, URL WSDLURL, String dataload) {
String targetNS = namespace;
QName serviceName = new QName(targetNS, Servicename);
QName portName = new QName(targetNS, porttype);
URL wsdlURL = WSDLURL;

Service service = Service.create(wsdlURL, serviceName);
service.addPort(portName, SOAPBinding.SOAP11HTTP_BINDING,
wsdlURL.toExternalForm());
Dispatch

dispatch = null;
StreamSource xmlSource = null;
ByteArrayOutputStream xmlByteArray = new ByteArrayOutputStream();
TransformerFactory fac = TransformerFactory.newInstance();
dataload = addSoapEnvelope(dataload);

try {
Transformer x = fac.newTransformer();
x.transform(new StreamSource(new StringReader(dataload)),
new StreamResult(xmlByteArray));
xmlSource =
new StreamSource(new StringReader(xmlByteArray.toString()));

dispatch =
service.createDispatch(portName, Source.class, Mode.MESSAGE);
} catch (TransformerConfigurationException e) {
e.printStackTrace();
} catch (TransformerException e) {
e.printStackTrace();
}
//calling WS with help of Dispatcher and getting Result as Source
Source result = dispatch.invoke(xmlSource);

StringWriter outputxml = new StringWriter();
Transformer outx;
try {
outx = fac.newTransformer();
outx.transform(result, new StreamResult(outputxml));
} catch (TransformerConfigurationException e) {
e.printStackTrace();
} catch (TransformerException e) {
e.printStackTrace();
}
String resultstr = outputxml.toString();
if (resultstr.indexOf("?>") > 0) {
resultstr = resultstr.substring(resultstr.indexOf("?>") + 2);
}
MessageFactory factory;
StringWriter output = null;
//Converting Source result to SOAP Message
try {
factory = MessageFactory.newInstance();
SOAPMessage message = factory.createMessage();
message.getSOAPPart().setContent(new StreamSource(new StringReader(resultstr)));
message.saveChanges();
NodeList list = message.getSOAPBody().getChildNodes();
Node node = list.item(0);
output = new StringWriter();
Transformer out;
out = fac.newTransformer();
out.transform(new DOMSource(node), new StreamResult(output));

} catch (TransformerConfigurationException e) {
e.printStackTrace();
} catch (TransformerException e) {
e.printStackTrace();
} catch (SOAPException e) {
e.printStackTrace();
}
//Converting SOAP Return to String for processing
String outputstr = output.toString();
//removing from returned output
if (outputstr.indexOf("?>") > 0) {
outputstr = outputstr.substring(outputstr.indexOf("?>") + 2);
}
return (outputstr);
}
Please note that it is not the best way to do it. You may find better way to do the same like using WS-I APIs


2) Call through a PL/SQL procedure: If you are using event based program where events are getting stored in DB, you may need to call services from DB side. I am aware of Oracle Database, so I am using it only but I think similar option must be available with other databases as well.
Advantage is, you need not have additional program call to invoke WS and you can secure some n/w bandwidth and processing time. It is hard to believe but database too have some very good API's to handle XML data.
Limitation is, it doesn't support security and oracle standards/recommendations do not suggest for this approach. But in some places it may work as life saver.
FUNCTION new_request(method IN VARCHAR2,
namespace IN VARCHAR2)
RETURN request AS
req request;
BEGIN
req.method := method;
req.namespace := namespace;
RETURN req;
END;
Following is the code snippet from soap_rpc package for raising new request:

PROCEDURE add_parameter(req IN OUT NOCOPY request,
name IN VARCHAR2,
type IN VARCHAR2,
value IN VARCHAR2) AS
BEGIN
req.body := req.body ||
'<' xsi:type="'||type||'">'||value||'';
END;

PROCEDURE generate_envelope(req IN OUT NOCOPY request,
env IN OUT NOCOPY VARCHAR2) AS
BEGIN

env := '

<' '||req.namespace|| ' SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">'||
req.body||'';
END;

For more details you may refer to http://www.oracle.com/technology/tech/webservices/htdocs/samples/dbwebservice/DBWebServices_PLSQL.html page.


3) Call through a BPEL process This is the most declarative approach where the services of similar input and output type can be called dynamically. Here I have used Oracle SOA Suite 11g and again I am pretty much sure that other SOA Suite must have similar feature.
Here I have created a dummy WSDL with request and response and associated with BPEL Process. Now at Run time, we can assign endpoint reference to dummy partner link to invoke the service.
Advantage is, it is purely declarative in nature; it is very useful in case of load balancing where we can use multiple end points for similar feature and redirect them to other endpoint if one service end point is down.
Limitation is, it will work only in case of similar messages as the message types are bind with BPEL.

Following is the code of Dynamic BPEL. bpel file snippet for your reference:

Essential Features for an Orchestration Project for inbound and outbound data

For an orchestration project it is required that the defined system have capability to communicate with other external systems. There are multiple ways defied by which two (homogeneous or heterogeneous) systems can communicate with each other like event driven communication on Push –pull model or peer to peer architecture on tightly coupled model .

Major challenge for any Orchestration systems is
• Multiple model and architecture support.
• Dynamic addition of models and events in system.

In summary, an orchestration system should capable to listen external system(s) and perform predefine or registered action(s). These external system triggers or events which may be pre seeded or user may register them after implementing orchestration system.
All these communication can be broadly divided in to two categories.
A. Outbound communication
B. Inbound communication
A) Outbound and related inbound communication
In outbound communication, it is possible that the external system will pass some data back to original system. This related inbound may be in form of confirmation note, some payload (in case of synchronous service call) or exception or error message. We will discuss features related to outbound communication and related inbound communication in following section.
For an outbound communication system, system should provide, but not limited to, following features.
Routing
Routing is an essential part of outbound communication. It will provide flexibility of calling different external system or different services in same external system on the basis of provided conditions.

Transformation of Data
While communicating to different system we need to transform the data load before passing to other system as per their understandable format. To achieve this orchestration system should have provision to transform data as per user setup.
Business rule connectivity
As the business needs are changing day to day basis, a system should be scalable enough to accommodate new and change business requirements without stopping the current business operations. To achieve it system should support business rule connectivity so that they can add new routing rules or change pre existing rules without downtime.
Filtration criteria
Audit Trails
An Orchestration system should have a mechanism to check the records of communication done by the system and the status of these communications.
To achieve it, there should be a Audit trail mechanism to record all these communications for future references.
Exception Handling
In case of any abrupt behavior in system, a system should be fail safe and capable to record the incident for auditing purpose.
Component as service
Orchestration system has its own data which may be required by external system for their references. This data may be in form of rules, audit trails or setups got created by user setup or actions.
There should be some open endpoints available for other systems to request for this information and orchestration system should provide the response with proper authentication and authorization.
Data Security
An orchestration system access and pass data to external system(s). This data may be confidential and may have viewing restrictions. In this case, an Orchestration system should have proper authentication and authorization mechanism for data security.
Ability to call both synchronous and a synchronous process
An orchestration system may call services from heterogeneous system and these services may be defined as synchronous (will provide reply at the same time) or a synchronous (will process the data and will reply later).
An Orchestration system should facilitate both types of services without any loss of data.
Ability to place payload in persistent model
An external system may not be available for an instance of time. An Orchestration system should have a provision to store request in persistent model like message queues or database in such case so that it can be passed to external system once it came back from down time.
Related inbound data load
An inbound related to outbound of orchestration system should have following features
No Duplicity
In case of duplication of input data, system should intelligent enough to filter these inputs and accept only one of them.
Response handling
For a synchronous communication from out bound, related response will be handled by inbound. This inbound will be specific to the instance of outbound call and inbound should have mechanism to relate it and associate it with correct outbound instance.
Exception handling for response
In case of any exception thrown by external system for outbound call, inbound system should capture it and record it for audit trail purpose.

B) Inbound communication and its data load
In inbound communication, an Orchestration system has to listen from heterogeneous systems and perform on the basis of these events. As all the inbound data may not be useful for orchestration system so there is a challenge of filtering out only those events which are useful for business and registered with orchestration system.
For an inbound communication system, system should provide, but not limited to, following features.

Ability to push event definition and related payload to persistent mode (such as DB)
As the data coming to system is valuable and any business cannot afford to lose this data, it is essential to keep inbound data in any persistent model so that it can be retrieved back for reference and audit trail.
An Orchestration system should have a provision to persist inbound data in any persistent model like Database.
Ability to identify the incoming events and registration of new events
As the business needs and requirements are changing very frequently with business growth and time line, an orchestration system should have provision to listen new business events that may come from any new system or any preregistered system.
There should be a provision to make change in already registered events so that they can change with change in external system.

Filtration of events and see them as inbound
As all incoming data to inbound system may not be useful for orchestration system, an orchestration system should have provision to filter the required events and ignore other events.
Audit trails
In case of any error or failure in system at inbound side, it is essential to record the incident and keep the system up for other request. An orchestration system should be robust enough to keep the system up in case of any failure and recode the incident in audit trail to reference.
Ability to define and use relationship between events
As business events may interrelated and/or dependent on each other, it is essential for an orchestration system to keep a track of relationship between business events so that the system can behave properly and maintain its data integrity.
Transformation capability of inbound payload
Like outbound, inbound communication may involve heterogeneous system and it is essential to transform the data for further processing. In orchestration system, there should be a provision by which user can define the transformation process of inbound data so that system can transform and understand it.

Exception handling with notification feature
If there is any discrepancy in inbound data because of which orchestration system cannot process the event further, it should be tracked as an error in system and orchestration system should have provision to send notification to defined user(s) so that the error can be rectified

MultiOrg and MOAC: Introduction

In Oracle Apps practice, I have seen people using MOAC and Multi Org Concept as one and the same thing. In one way it is actually same but there is some basic difference as well.
Let's discuss about them one by one.
Multi Org concept was introduced in Oracle Apps in 11i and still in use. Multi Org feature is facilitating single instance deployment for multiple organizations in place of multiple instances for each org.
All the records will be logically partitioned with org_id in same table which is unique identifier for an organization. Data retrieval is done on the basis of Views having additional filter condition of
ORG_ID = USERENV('CLIENT_INFO');
Where USERENV('CLIENT_INFO') will be replaced with Org_ID retrieved from session.
While creating responsibilities, we can assign an Org to it and end user can use this responsibility to create data for respective org.
There was a drawback of this approach.
1) To provide access for multiple org, we have to create multiple responsibilities for same menu and assign it to user.
2) While entering data for multiple org, user has to switch the responsibility.
In real life scenario, a single user has access on multiple organization and he/she need to enter data for these organization at the same time.
To overcome above issues, Oracle comes up with new concept called MOAC (Multi Org access Control).
With MOAC, a user can have access on multiple organizations at same time with help of security profile.
While Application Deployment, administrator can create security profiles in HRMS responsibility which he can associate to Site, application, responsibility or user.
If you create new Business Group, system will create Security profile for new BU having access on all OUs associated with it automatically.
Once the security profile attached to profile value, user can see data for only those organizations for which security profiles provide access to him.
This is done with the help of secure synonyms where policies associated to it restrict the data.

Tips to work on the payment accounting error
There are majorly two reasons for failure of payment accounting:
1) Related invoice is not acounted completely
2) Previous payment accounting not accounted completely
Related invoice is not accounted completely
There are following most occouring cases:
* Upgraded invoices do not have complete or appropriate data in XLA_DISTRIBUTION_LINKS and payment time bflow will fail to get upstream.
* Matching records for invoice_distribution_id available in AP_INVOICE_DISTRIBUTIONS_ALL are not present in source_distribution_id_num_1 in XLA_DISTRIBUTION_LINKS.
* Some rows in ap_invoice_distributions_all are not accounted at all
* Invoice is having DIST_VARIANCE hold due to tax reversal have not generated properly.
* For upgrade invoice is on DIST VARIANCE hold because Tax reversal is in different invoice line and line amonut is set to 0

Tips to work in such cases:

1)Run Following Query:
select distinct aid.invoice_id from ap_invoice_distributions_all aid
,xla_ae_headers xah,xla_ae_lines xal
where aid.invoice_id in ( select distinct invoice_id
from ap_invoice_payments_all
where check_id = &check_id )
and aid.accounting_event_id = xah.event_id
and xah.ae_header_id = xal.ae_header_id
and xah.application_id =200
and xal.application_id =200
and not exists
( select 1 from xla_distribution_links xdl
where xal.ae_header_id = xdl.ae_header_id
and xal.ae_line_num = xdl.ae_line_num
and aid.accounting_event_id = xdl.event_id
and xdl.application_id =200);

This query is returning result in case of upgraded data having no/partial records in XlA_DSITRIBUTION_LINKS.
If this query returns result, check the data in XLA_AE_LINES and XLA_DISTRIBUTION_LINKS.

a)In case of upgraded data if accounting_class_code in XLA_AE_LINES is ’CHARGE’, ’RECOVERABLE TAX’ or ’NONRECOVERABLE TAX’, raise an datafix SR.
b)In canse of upgraded data, if BUSINESS_CLASS_CODE in XLA_AE_LINES is null for ACCOUNTING_CLASS_CODE ’LIABILITY’, and UPG_BATCH_ID is –5672 or it is null , raise an datafix SR.

c) In case of Upgraded Data, if ’SOURCE_ID’ and ’SOURCE_TABLE’ is null and UPG_BATCH_ID is –5672, raise an datafix SR.

SOURCE ID is invoice_distribution_id or invoice_id on the basis of ACCOUNTING_CLASS_CODE .SOURCE_TABLE is ’AP_INVOICE_DISTRIBUTIONS’ or ’AP_INVOICES’ on the basis of ACCOUNTING_CLASS_CODE.

2)Run Following query:

select distinct invoice_id ,
accounting_event_id,line_type_lookup_code,
amount,invoice_distribution_id,parent_reversal_id,
charge_applicable_to_dist_id,summary_tax_line_id
from ap_invoice_distributions_all aid
where aid.invoice_id in
(select invoice_id from ap_invoice_payments_all where check_id = &check_id )
and not exists
(select 1 from xla_distribution_links xdl
where xdl.event_id = aid.accounting_event_id
and xdl.source_distribution_id_num_1 = aid.invoice_distribution_id)
order by invoice_id;

This query will return the result in case of there is any mismatch between AP_INVOICE_DISTRIBUTIONS_ALL and XLA_DISTRIBUTION_LINKS.


a)If above query returning any data then check invoice_distibution_id and source_distributions_id_num_1. If there is some distributions unaccounted till now. Try to account them. If invoice is having any kind of variance, release the variance and try to complete accounting.

b) There may be some case where source_distribution_id_num_1 is null or have any data that is not available in AP_INVOICE_DISTRIBUTIONS_ALL as invoice_distribution_id. To resolve it run undo accounting and account it once again.

c) Check data for applied_to columns in XLA_DISTRIBUTION_LINKS, if it is not populated, raise an datafix SR.

Note: This query will return you data for AWT lines if there is payment time AWT. Please ignore them.

3) Run Following Query:

Select ail.line_number,ail.line_type_lookup_code,ail.amount,sum(aid.amount)
From ap_invoice_lines_all ail,ap_invoice_distributions_all aid
Where ail.invoice_id = aid.invoice_id
and ail.invoice_id = &invoice_id
and ail.line_number = aid.invoice_line_number
having ail.amount <> sum(aid.amount)
group by ail.line_number,ail.line_type_lookup_code,ail.amount

This Query will return you data in case of Dist_variance on invoice.

Solution :

* If line_type_lookup_code is ’TAX’ and historical_flag is ’Y’ then raise an datafix SR with output of above query.
* Confirm that there is no orphan distributions, it may be possible that required distributions are available as orphan. If it is so then please use orphan record removal GDF provided by Ebiz AP.

4)

Run Following Query:

Select * from ap_invoice_distributions_all aid

Where aid.invoice_id = &invoice_id
and not exists
(select 1 from ap_invoice_lines_all ail
where ail.invoice_id = aid.invoice_id
and ail.line_number = aid.line_number)

This query will return you result in case of orphan distributions.

Solution:

* Analyze the invoice, if there is any way to attach it back to invoice because of missing link, attach it.
* If it is not possible to attach it back to invoice and you want to delete them, confirm you are deleting accounting side data as well if associated.
* There is one Select and fix GDF available for invoice orphans. Please refer them for further details.

Previous payment accounting not accounted completely

Following are some common cases

* Previous payment accounting data not generated/upgraded successfully
* Accounting date is not in open period
* Bflow data is not correct.
* There is more than one payment_created transaction_type in AP_PAYMENT_HISTORY_ALL
* Related invoice_accounting do not have LIABILITY Line in XLA_AE_LINES
* AP_INVOICE_PAYMENTS_ALL does not have accounting_event_id in case of upgraded data
* PAYMENT_UNCLEARING event is not getting accounted.

Tips to work in such cases:

1)Run following Query :

select distinct ac.check_id
from ap_payment_history_all aph, ap_checks_all ac
where ac.org_id=&org_id
and ac.check_date >
’&approx_date_check_created’
and aph.check_id=ac.check_id
and aph.transaction_type like ’PAYMENT CREATED’
and nvl(aph.historical_flag,’N’)
=’Y’
and not exists
(select 1 from ap_payment_hist_dists aphd
where aphd.payment_history_id=aph.payment_history_id)

This query will return you the checks not having data in AP_PAYMENT_HIST_DISTS.

Solution:

There is one GDF available to populate AP_PAYMENT_HIST_DIST and XLA_DISTRIBUTION_LINKS. Please use it to populate the data

2)Run Following Query:

select aph.*
from ap_payment_history_all aph
where aph.transaction_type = ’PAYMENT CREATED’
and exists (select 1
from ap_accounting_events_all aae
where aae.accounting_event_id=aph.accounting_event_id
and aae.ax_accounted_flag=’Y’);

If above query returns any data, it means there is multiple PAYMENT_CREATED event because of AX Accounting.
Solution

Delete this data after checking if it is not accounted (it should not be in accounted status)

3) For distributions in AP_NIVOICE_DISTRIBUTIONS_ALL having 0 amount and there is no data in XLA_AE_LINES and XLA_DISTRIBUTION_LINKS.(AX Upgraded Data)

4)

While running upgrade script for payment, No data gets generated for ap_payment_hist_dist.

Solution:

Check if Accounting_evnet_id in AP_INVOICE_PAYMENTS_ALL is null. If it is so then populatye it with accounting_evnet_id of ’PAYMENT_CREATED" transaction type of ap_payment_history_all .

Check if recon_accounting_flag is ’Y’ where XLA_AE_LINES have CASH lines in place of CASH_CLEARING accounting_class_code .

Check if Source_id and Source_table is null.

5) PAYMENT_UNCLEARING trnasaction type in AP_PAYMENT_HISTORY_ALL is not getting accounted where PAYMENT_CANCELED is already accounted.

This Transaction is not necessary to get accounted as payment is already gets cancelled.

Set event_stastus_code as ’N’ process_status_code as ’P’ in XLA_EVENTS and set posted_flag as ’P’ in AP_PAYMENT_HISTORY_ALL.

HTTP on Browser

We are now in Internet age and one of the software we use on regular basis, except OS, is browser. If we calculate the average time we spend with browser in one day, it would be more then the time spent on most of other software's in a week!!
but did we ever thought that what browser is and how it works? I am sure most of us never thought on it.
For me, browser, or in specific, 'Web Browser' is a client software which help us to communicate with different server machines. We discussed about communication and protocol in our last blog.
As we know that to make a communication we need two things. 1) network and 2) Protocol.
So it is true in case of browsers also. It require network and usually supports all standard protocols like HTTP, HTTPS, FTP etc.
Lets discuss something about HTTP now.
HTTP is Hyper Text Transfer Protocol with support HTML (Hyper Text Markup Language) and it is a stateless protocol.
It is very important to understand the meaning of stateless here because it is the only reason behind evolution of Web development languages like ASP, Servlet, JSP etc.
A stateless protocol starts with a request and end with a response from server. It can not maintain the transactional data by itself.
To understand it better, lest assume a login page where user pass login Id and password and it return welcome page if login credentials are correct.
Now there are other links to go from login page but how those page will know that login was successful at last time and the user sending page request is having any account. More over if page is suppose to display user specific data the for which user date needs to be displayed as HTTP already lost the request and response data.
To handle these scenarios programmers needs Session Management which we will discuss in future blogs.

Protocol and Network

While writing my first code for client server environment, I was quite amazed to see how it works. It was a thick client chat server and client code, written on Swings with Socket program in early 2000.
First thing I learned by this program was the term "Protocol". For me protocol is like a language grammar. While communicating, we need a mutually understandable language and a medium by which we can pass the expression.In same way protocol help two different entities to fix their grammar and communicate on network medium.
It is like a universal truth, any types of machines can communicate with each other if they have set protocol and communication medium.
Now a days, most popular medium is 'Internet' as many of us must be aware of that internet is also a network so any machine connected with a network can use network as medium.
Other used mediums are , Intranet, LAN(Local area Network) , WAN( Wide Area Network) etc.
Now in terms of Protocol, most popular and simpler is HTTP (Hyper Text Transfer Protocol).
HTTP is a grammar by which two machine can understand each other's messages.
Think about a letter you have received filled with 1's and 0's..you will definitely feel puzzled..isn't it?
but if you know binary number format, you will try to convert it to some meaningful number's..
but what after it.. will those number give you any clue for the message.. you may further try to decode it with some pattern..but what if you know the pattern?..life will be easy for you!!..
you can read the message now..
Protocols help machines to write data on network and recover it from network so that machine can understand it.
Some other popular protocols used now a days are.. FTP (for File Transfer), WAP( for wireless Access) SOAP(Simple Object Access Protocol for web services communication) .
One thing I would like to mention over here that, it is not necessary to use Standard protocol only for communication. If you know socket programming then you can create a socket and Server socket where socket can pass any message to your server socket in your defined protocol.
I have used it in my chat program, where multiple messages were sent via a delimiter(||).
I will post a code snippet of writing such program later.