Oracle Blogs

Function to convert between datasets  SELECT convert(         DATA,         'TO DATASET',         'FROM DATASET'     ) FROM DUAL   example : SELECT convert(         'موقع الفعالية اضغط هنا',         'AL32UTF8',         'AL32UTF8'     ) FROM DUAL

 Register Table in Oracle Apps --=================================================== select * from apps.XX_EMP_ADI drop table apps.XX_EMP_ADI --=================================== Create Table --=================================== create table apps.XX_EMP_ADI ( HDR_ID NUMBER,  NAME  VARCHAR2(100),  EMP_NO VARCHAR2(100),  GRADE VARCHAR(2),  DOB DATE  ) --=================================== Check Table --===================================  select * from apps.XX_EMP_ADI --=================================== Check  Table is created in Database --===================================  select * from all_objects  where object_name = 'XX_EMP_ADI' --=================================== Check Table is register or Not --===================================  select * from fnd_tables  where table_name = 'XX_EMP_ADI' --=================================== Check Table Columns are register or Not --===================================  se...

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