Oracle Blogs

Charts Of Accounts Structure, query is given below. The query here will fetch you following list of columns: 1. Business Unit Name 2. Ledger Name 3. Charts Of Accounts Name 4. Segment Name 5. Value Set Code 6. Value Set Description Charts Of Accounts Structure Query select hou.name BU_NAME, led.NAME Ledger_Name, Str.STRUCTURE_CODE CHART_OF_ACCOUNTS_Name, SegInSt.SEGMENT_CODE COA_SEGMENT_NAME, vs.value_set_code, vs.description value_set_desc from hr_operating_units hou, xla_gl_ledgers led , fnd_kf_structures_b Str, fnd_kf_str_instances_b StrInSt, fnd_kf_segment_instances SegInSt, fnd_vs_value_sets vs where 1=1 and led.ledger_id = hou.set_of_books_id AND led.CHART_OF_ACCOUNTS_ID = Str.Structure_id AND Str.KEY_FLEXFIELD_CODE = ‘GL#’ AND Str.Structure_id = StrInSt.Structure_id and SegInSt.Structure_inStance_id = StrInSt.Structure_inStance_id and vs.VALUE_SET_ID = SegInSt.VALUE_SET_ID If you look at the query above, you will see that every Business Unit has something called “Set of Book...

 opaque schema xsd (standard file used in OIC) You can use an opaque schema in a stage file action Read File or Write File operation without concern for a schema for the file.   The only condition is that whatever is sent to the opaque element in the opaque schema must be base64-encoded data.  or in other words  it is used to decode base64 files Save the below xml as  opaqueschema.xsd  file and then you can use it in the integration. <?xml version = '1.0' encoding = 'UTF-8'?> <schema targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/opaque/" xmlns="http://www.w3.org/2001/XMLSchema" > <element name="opaqueElement" type="base64Binary" /> </schema>

PERSONALIZATION TO CHANGE LOV (Example used AP invoice form> Lines> TAX CLASSIFICATION CODE column LOV)  Step 1: GO form and click on LOV That needed to be changed Step 2 : Click ALT+H+D+E to examine and copy that block and field Step 3 : Click ALT+H+O to open about oracle applicaiton.              Copy Form Name and PATH Step 4 : Go to that path above winscp in the path replace 'ap' with 'au' and search for the FORM name                 and download it into your local system Step 5: Open form in form builder ,in  DATA_BLOCKS go to block you found in examine the form and in that block find item(field) that you found in examine  >        > >  double click on item  >copy this LOV name and find it in LOV section of FORM BUILDER   >double click on TAX_CLASSIFCATION_CODE2  >copy the record group name > find the record group in ...

 select * from po_headers_all / set serverout on; declare   /**********************************************************************   I_ACTION ==>   *********************************************************************/   V_STATUS VARCHAR2(100); --For check this HEADER_ID Status. (Is 'INCOMPLETE' ?)   V_RETURN_CODE VARCHAR2(1) := 'S'; --Process status code ==> S : Success / E : Error   V_RETURN_MSG  VARCHAR2(2000); --Error message   V_ITEM_KEY  VARCHAR2(200); --For API   V_ITEM_TYPE VARCHAR2(20);   V_SEGMENT1           PO_HEADERS_ALL.SEGMENT1%TYPE; --For API   V_PREPARER_ID        PO_HEADERS_ALL.AGENT_ID%TYPE; --For API   V_DOCUMENT_TYPE_CODE PO_DOCUMENT_TYPES_ALL.DOCUMENT_TYPE_CODE%TYPE; --For API   V_DOCUMENT_SUBTYPE   PO_DOCUMENT_TYPES_ALL.DOCUMENT_SUBTYPE%type; --For API   I_HEADER_ID number; -- PO_HEADER_ID   V_ORG_ID number;   ...

Custom PLL file path  example : $AU_TOP/12.0.0/resource ------this  is the to be used and pasted in custom pll   v_lov LOV;   rg_name VARCHAR2(40);   rg_id RecordGroup;   v_sql  varchar2(5000);   result Number;   rec_id RecordGroup;   --    -- Real code starts here    --change lov of tax classifcation code in sales order form 19-11-2024      -------------------   --    begin      null;      begin     IF (event_name = 'WHEN-NEW-ITEM-INSTANCE' AND form_name= 'OEXOEORD') THEN     IF (block_name = 'LINE') THEN     rg_id := FIND_GROUP('TAX_CODES_RG');     v_lov := FIND_LOV('TAX_CODES');     v_sql := 'select TAX_CLASSIFICATION_CODE,TAX_CLASSIFICATION from zx_output_classifications_v1';     rec_id := CREATE_GROUP_FROM_QUERY('PO_GRP_548741',v_sql); ...

  SELECT      vendor_name,     supplier_num,     SUM("current1") AS "current",      SUM("1-30 Days") AS "1-30 Days",      SUM("30-60 days") AS "30-60 days",      SUM("60-90 Days") AS "60-90 Days",      SUM("90-180 days") AS "90-180 days",      SUM("180-365 days") AS "180-365 days",      SUM("365-730 days") AS "365-730 days",      SUM("730 above") AS "730 above",      sum("current"+"1-30 Days"+"30-60 days"+"60-90 Days"+"90-180 days"+"180-365 days"+"365-730 days"+"730 above") BALANCE FROM     (         SELECT          lineS.line_number,             MAX(ps.vendor_name) vendor_name,            MAX( ps.segment1 )supplier_num,             inv.invoice_num,           ...

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