Oracle forms data saving into oracle database in a cell as multi line. (paste from Excel)

User paste the data into Oracle Forms from excel/Email.

Issue: data saving into oracle database in a cell as multi line.

Solution: 

 Sè Form pre-insert, pre_update

:XXTABLE.EMP_ID :=  replace(replace(:XXTABLE.EMP_ID ,chr(10)),chr(13));

select replace(replace(EMP_ID,chr(10)),chr(13)) ,

replace(replace(ARABIC_NAME,chr(10)),chr(13)) arab,

replace(replace(ENGLISH_NAME,chr(10)),chr(13)) eng

FROM XXTABLE

WHERE EMP_ID LIKE '12345%';

Account Generator Customization to generate Charge Account, Budget Account from Purchase Requisition Line DFF

 Requirement: To default Budget Account, Charge Account in PO Requisition from Line DFF Attribute1, Attribute2.

1. Enable DFF

Table Name : gl_code_combinations_kfv gcc

gcc.CONCATENATED_SEGMENTS	Char	50
APPS.GL_FLEXFIELDS_PKG.get_concat_description(gcc.chart_of_accounts_id,gcc.code_combination_id)	Char	240
gcc.CODE_COMBINATION_ID	Number	150

Condition:  (Replace with you COA id)

WHERE chart_of_accounts_id = 50320

AND ENABLED_FLAG  = 'Y'

2. Open the Workflow in the workflow builder.

2.1 Budget Account

Copy and Paste the selected process names and change Display Name as below.

Change1 :

Take a Copy of function default existing function and change the new function name as below.

Function Name: XXPO_WF_PO_BUDGET_ACC.XXGET_LINEATT1_BA

This procedure is share at the end as attachments.

Budget Account is done. 

2. Charge Account:

Function Name: XXPO_WF_PO_CHARGE_ACC.XX_ATT1_inventory

Charge Account is done.

3. Update the Account Generator setup to our new Process Name.

Test and deploy to Prod.

Code:

-- xxget_lineatt1_BA

--getting budget account from the line attribute 1

--

procedure xxget_lineatt1_BA ( itemtype        in  varchar2,

                       itemkey         in  varchar2,

                       actid           in number,

                       funcmode        in  varchar2,

                       result          out NOCOPY varchar2    )

is

x_progress      varchar2(100);

x_dest_org_id number;

x_account number;

x_LINE_ATT1_char varchar2(240);

x_LINE_ATT1 number;

x_account1 number;

begin

  x_progress := 'XXPO_WF_PO_BUDGET_ACC.xxget_lineatt1_BA: 01';

  IF (g_po_wf_debug = 'Y') THEN

     /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);

  END IF;

  -- Do nothing in cancel or timeout mode

  --

  if (funcmode <> wf_engine.eng_run) then

      result := wf_engine.eng_null;

      return;

  end if;

 x_LINE_ATT1_char := wf_engine.GetItemAttrText ( itemtype => itemtype,

                                            itemkey  => itemkey,

                                     aname    => 'LINE_ATT2');

x_account := to_number(x_LINE_ATT1_char);

 

  if (x_account IS NOT NULL) then

  wf_engine.SetItemAttrNumber ( itemtype=>itemtype,

                                 itemkey=>itemkey,

                                 aname=>'TEMP_ACCOUNT_ID',

                                 avalue=>x_account );

   result := 'COMPLETE:SUCCESS';

  else

result := 'COMPLETE:FAILURE';

  end if;

  RETURN;

EXCEPTION

  WHEN OTHERS THEN

    wf_core.context('XXPO_WF_PO_BUDGET_ACC','xxget_lineatt1_BA',x_progress);

        raise;

end xxget_lineatt1_BA;

 

--Charge Account

procedure XX_ATT1_inventory  ( itemtype        in  varchar2,

                       itemkey         in  varchar2,

                       actid           in number,

                       funcmode        in  varchar2,

                       result          out NOCOPY varchar2    )

is

  x_progress  varchar2(100) := '000';

  x_debug_stmt  varchar2(100) := NULL;

  x_dest_sub_inv  varchar2(25);

  x_subinv_type varchar2(25);

  x_account       varchar2(200) := NULL;

  x_inv_item_type varchar2(25);

  x_dest_org_id   number;

  x_item_id number;

        --<INVCONV R12 START>

  x_status  varchar2(1);

  x_vendor_site_id number;

  x_msg_data      varchar2(2000);

        x_msg_count number;

        --<INVCONV R12 END>

  success   varchar2(2) := 'Y';

  dummy   VARCHAR2(40);

  xx_line_att1 number;

  ret     BOOLEAN;

begin

  x_debug_stmt := 'XXPO_WF_PO_CHARGE_ACC.inventory: 01' || x_progress;

  IF (g_po_wf_debug = 'Y') THEN

     /* DEBUG */  PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_debug_stmt);

  END IF;

  if (funcmode <> wf_engine.eng_run) then

      result := wf_engine.eng_null;

      return;

  end if;

  x_account := po_wf_util_pkg.GetItemAttrText ( itemtype => itemtype,

                                           itemkey  => itemkey,

                                       aname    => 'LINE_ATT1');

xx_line_att1 := to_number(x_account);

  po_wf_util_pkg.SetItemAttrNumber  (  itemtype=>itemtype,

                                  itemkey=>itemkey,

                                  aname=>'TEMP_ACCOUNT_ID',

                                  avalue=>xx_line_att1 );--x_account);--

 

  result := 'COMPLETE:SUCCESS';

  RETURN;

EXCEPTION

  WHEN OTHERS THEN

  wf_core.context('XXPO_WF_PO_CHARGE_ACC','inventory',x_progress);

        raise;

end XX_ATT1_inventory;

Test and deploy to Prod.