Oracle Blogs

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

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;