Oracle Blogs

SOAP API to add attachment (Example request AR invoice attachment and Journal Header) Service WSDL URL:   https://servername/fscmService/ErpObjectAttachmentService?WSDL Operation – uploadAttachment: Uploads an attachment zip file for a particular Universal Content Management Server account and updates the attachment tables for the relevant business object. Here is an example payload to upload attachment to AR invoice Header. <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/" xmlns:erp="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/">    <soapenv:Header/>    <soapenv:Body>       <typ:uploadAttachmentAsync>          <typ:entityName>RA_CUSTOMER_TRX_ALL</typ:entityName>          <typ:categor...

API SOAP to update AR DFF (oracle fusion) includes debit memo WSDL  =>    https://servername/fscmService/ErpObjectDFFUpdateService?WSDL Sample Request body <!-- WSDL https://<hostName>:443/fscmService/ErpObjectDFFUpdateService --> <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/" xmlns:erp="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/">    <soapenv:Header/>    <soapenv:Body>       <typ:updateDffEntityDetailsAsync>          <!--Optional:-->          <typ:operationMode>SINGLE</typ:operationMode>          <!--Optional:-->          <typ:object>             <!--Option...

select (regexp_replace(rawtohex(sys_guid()),'([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})','\1-\2-\3-\4-\5')) UUID from dual;

 To access the previous row in sql LAG function , subract from previous row or add or multiply or divide example : select *,sal,        sal - coalesce(lag(sal) over (order by EMPNO), 0) as diff from emp;

Below is a XSL example used to style or format a xml. <?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">      <xsl:output method="xml" indent="yes"/>   <!--select="DATA_DS/INVOICE_NO" <== these are invoice field paths change them accordingly -->      <xsl:template match="/">     <Invoice>       <InvoiceNumber><xsl:value-of select="DATA_DS/INVOICE_NO"/></InvoiceNumber>              <InvoiceDetails>         <xsl:for-each select="DATA_DS/G_1">           <Detail>             <ProfileID><xsl:value-of select="PROFILEID"/></ProfileID>             <UBLVersionID><xsl:value-of select="UBLVERSIONID"/></UBLVersionID>   ...

Q1 for ar customer transaction lines  SELECT  distinct hca.account_number client_number, hp.party_name client_name, rctt.name TRX_TYPE1, rct.trx_number trx_number, rct.PURCHASE_ORDER, TO_CHAR(rct.creation_date,'DD-MON-YY') TRX_DATE, TO_CHAR(rctg.gl_date,'DD-MON-YY') GL_DATE, apsa.TAX_REMAINING VAT_AMOUNT, ROUND(NVL(XAL.ACCOUNTED_DR,0),2) DEBIT, ROUND(NVL(ACCOUNTED_CR,0),2)   CREDIT , ROUND((NVL(XAL.ACCOUNTED_DR,0)),2) - ROUND((NVL(ACCOUNTED_CR,0)),2) balance, xal.CURRENCY_CODE CURRENCY, rtt.name payment_term, TO_CHAR(apsa.due_date,'DD-MON-YY') DUE_DATE, xal.description DESCRIPTION FROM  XLA.XLA_AE_LINES  XAL, XLA.XLA_AE_HEADERS XAH, XLA.XLA_TRANSACTION_ENTITIES XTE, ra_customer_trx_all rct, ra_customer_trx_lines_all rcl, RA_CUST_TRX_LINE_GL_DIST_ALL rctg, ra_cust_trx_types_all rctt, ra_terms_tl rtt, ar_payment_schedules_all apsa, hz_cust_accounts hca, hz_parties hp WHERE 1=1 and rct.customer_trx_id = rcl.customer_trx_id and rct.CUST_TRX_TYPE_ID = rctt.CUST_...

 <?xml version="1.0" encoding="WINDOWS-1252" ?> <dataTemplate name ="APOPENINGBALANCE" description="AP OPENING BALANCE REPORT " Version="1.0" > <parameters> <parameter name="FROM_DATE" datatype="VARCHAR2"/> <parameter name="TO_DATE" datatype="VARCHAR2"/> </parameters>  <dataQuery>  <sqlStatement name="Q1"> <![CDATA[ SELECT  ROUND(SUM(NVL(XAL.ACCOUNTED_DR,0)),2) OPENING_DEBIT, ROUND(SUM(NVL(ACCOUNTED_CR,0)),2)   OPENING_CREDIT , ROUND(SUM(NVL(XAL.ACCOUNTED_DR,0)),2) - ROUND(SUM(NVL(ACCOUNTED_CR,0)),2)   OPENING_BALANCE FROM  XLA.XLA_AE_LINES  XAL, XLA.XLA_AE_HEADERS XAH, XLA.XLA_TRANSACTION_ENTITIES XTE, AP_INVOICES_ALL AI WHERE 1=1 AND XTE.SOURCE_ID_INT_1(+)=AI.INVOICE_ID AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID AND XAH.APPLICATION_ID = XAL.APPLICATION_ID AND XAL.APPLICATION_ID = XTE.APPLICATION_ID AND XAH.ENTITY_ID = XTE.ENTITY_ID AND AI...

       FBDI (File Based Data Import) To import objects, you use a spreadsheet template (CSV) to enter the data you want to import, and then run the appropriate import process from the  Scheduled Processes  page. This blog shows the process to create suppliers using FBDI. to create supplier using fbdi we need  XLSM template which can be found by googleing 'FBDI suppliers' or click here  LINK FOR SUPPLIER FBDI click on link highlited to download the template. Open the template and fill all the mandatory fields After filling click on generate csv file button Save the zip file and csv file Now open fusion instance , Navigate to TOOLS > SCHEDULE PROCESSESS Search for "Load Interface File for Import" under import processess "Import Suppliers" and in data file click on upload new file and upload your zip file                           Now after submittting you can find your uploaded supp...

Example : select SUM(empno) OVER(PARTITION BY deptno) sum,emp.* from emp partition by is used to use group funtions without using group by clause and retrive all rows while grouping them. The   PARTITION BY clause sets the range of records that will be used for each "GROUP" within the  OVER  clause. In your example SQL,  DEPT_COUNT  will return the number of employees within that department for every employee record. (It is as if you're de-nomalising the  emp  table; you still return every record in the  emp  table.) emp_no dept_no DEPT_COUNT 1 10 3 2 10 3 3 10 3 < - three because there are three "dept_no = 10" records 4 20 2 5 20 2 < - two because there are two "dept_no = 20" records If there was another column (e.g.,  state ) then you could count how many departments in that State. It is like getting the results of a  GROUP BY  ( SUM ...