Apps r12 financials update budget account in the Purchase Requisition

CREATE OR REPLACE 

procedure update_req2(p_req_id in number, p_attribute_1 in varchar2 --new buget_account_id

)is

l_req_hdr XX_po_requisition_update_pub.req_hdr1;

l_req_line_tbl XX_po_requisition_update_pub.req_line_tbl1;

l_req_dist_tbl XX_po_requisition_update_pub.req_dist_tbl;

l_return_status VARCHAR2(1);

l_msg_count NUMBER;

l_msg_data VARCHaR2(2000);

l_concatenated_segments varchar2(1000);

p_quantity number;

cursor c1 is

select attribute1,attribute2,org_id,segment1 from PO_REQUISITION_HEADERS_ALL where REQUISITION_HEADER_ID=p_req_id;

cursor c2 is

select * from PO_REQUISITION_LINES_ALL where REQUISITION_HEADER_ID=p_req_id;

cursor c3 (p_REQUISITION_LINE_ID number) is

 select * from PO_REQ_DISTRIBUTIONS_ALL  

  where REQUISITION_LINE_ID=p_REQUISITION_LINE_ID;

  L_J NUMBER:=0;

  L_K NUMBER:=0;

begin

--:= 204; --mandatory

--l_req_hdr.description := 'Updated VIA API';

for i in c1 loop

--insert into xxtest

--values('INSIDEC2',SYSDATE,p_req_id,I.ATTRIBUTE2||'-old',p_attribute_2);

--commit;

   L_J:=0;

   select fnd_flex_ext.get_segs(application_short_name  =>    'SQLGL',

                    key_flex_code     =>'GL#',

                    structure_number   =>'50408',

                    --combination_id    => I.ATTRIBUTE1

                    combination_id    => p_attribute_1

                    ) INTO l_concatenated_segments

                    FROM DUAL;

       l_req_hdr.org_id :=I.ORG_ID;

       l_req_hdr.segment1  :=I.segment1;

  for j in c2 loop

    L_K:=0;

    L_J:=L_J+1;

    l_req_line_tbl(L_J).requisition_line_num := L_J; --set the req line #

    l_req_line_tbl(L_J).requisition_number := i.segment1; --set req #

    

    for k in c3(j.REQUISITION_LINE_ID) loop

      L_K:=L_K+1;

      l_req_dist_tbl(L_K).requisition_number := i.segment1;

       l_req_dist_tbl(L_K).req_line_num := j.LINE_NUM ;

       l_req_dist_tbl(L_K).distribution_num := L_K;

      -- l_req_dist_tbl(L_K).budget_account:= l_concatenated_segments;

     -- update PO_REQ_DISTRIBUTIONS_ALL set BUDGET_ACCOUNT_ID= I.ATTRIBUTE1 where DISTRIBUTION_ID=k.DISTRIBUTION_ID;

      update PO_REQ_DISTRIBUTIONS_ALL set BUDGET_ACCOUNT_ID= p_attribute_1 where DISTRIBUTION_ID=k.DISTRIBUTION_ID;

    END LOOP;

  END LOOP;

 END LOOP;

  

 

    xx_PO_REQUISITION_UPDATE_PUB.update_requisition

    (

    p_init_msg_list => 'T'

    ,p_commit => 'Y'

    ,x_return_status => l_return_status

    ,x_msg_count => l_msg_count

    ,x_msg_data => l_msg_data

    ,p_submit_approval => 'N'

    ,p_req_hdr => l_req_hdr

    ,p_req_line_tbl => l_req_line_tbl

    ,p_req_dist_tbl => l_req_dist_tbl

    );

  dbms_output.put_line ('Test'|| l_return_status || ' MSG' ||l_msg_data);

end;

R12 SQL query for the employees under supervisor

 CREATE OR REPLACE VIEW XX_EMP_SUP_V AS

SELECT DISTINCT PERSON_ID,EMPLOYEE_NUMBER, SUPERVISOR_ID,EMPLOYEE_FULL_NAME,SUPERVISOR_FULL_NAME FROM(

SELECT     LEVEL LEVL1, e.*

      FROM (SELECT DISTINCT papf.person_id, papf.employee_number,

                            papf.full_name "EMPLOYEE_FULL_NAME",

                            paaf.supervisor_id,

                            papf1.employee_number "SUPERVISOR_EMP_NUMBER"

                            ,papf1.full_name "SUPERVISOR_FULL_NAME"

                       FROM apps.per_all_people_f papf,

                            apps.per_all_assignments_f paaf,

                            apps.per_all_people_f papf1,

                            apps.per_person_types ppt

                      WHERE papf.person_id = paaf.person_id

                        AND papf1.person_id = paaf.supervisor_id

                        --AND papf.business_group_id = 142

                        AND papf.business_group_id = paaf.business_group_id

                        AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date

                                                AND papf.effective_end_date

                        AND TRUNC (SYSDATE) BETWEEN papf1.effective_start_date

                        AND papf1.effective_end_date

                        AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date

                                                AND paaf.effective_end_date

                        AND ppt.person_type_id = papf.person_type_id

                        AND ppt.user_person_type <> 'Ex-employee') e

CONNECT BY PRIOR person_id = supervisor_id  and prior sys_guid() is not null 

START WITH person_id = person_id--4530;--5541;-- pass here supervisorid

)

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.

Translate the oracle workflow process name and description in Arabic (or any other language)

 

Before applying the below steps, run the below sql query to check what is the display_name, description in other languages.

ex:

SELECT * FROM WF_ACTIVITIES_TL WHERE ITEM_TYPE='HRSSA'

AND NAME='HR_EIT_VEN_PRC'; --> it is same in english for both US, AR languages

  

1.  download hrssa.wtf file from the workflow loader and open the file in notepad++ or any editor.

change in the file as below

1.1 LANGUAGE = "AR" in the line 9

1.2 update all the process_names display and description in arabic

1.3 save the file under the /orasb/XXCLONE/apps/apps_st/appl/per/12.0.0/patch/115/import/AR

2. in Putty :  export NLS_LANG=ARABIC_AMERICA.UTF8  

3. WFLOAD apps/apps 0 Y UPLOAD /orasb/DEVAP/apps/apps_st/appl/per/12.0.0/patch/115/import/AR/hrssa.wft

Testing:

run the above query to check the display_name, description changed in to lang.

SELECT * FROM V$NLS_PARAMETERS;

alter session set NLS_LANGUAGE ='ARABIC'

ARABIC.AMERICA.UTF8

Oracle Apps R12 API to submit PO Approval

 DECLARE

v_item_key VARCHAR2(100);

Cursor purchase_order is

SELECT

pha.po_header_id,

pha.org_id,

pha.segment1,

pha.agent_id,

pdt.document_subtype,

pdt.document_type_code,

pha.authorization_status

FROM apps.po_headers_all pha, apps.po_document_types_all pdt

WHERE pha.type_lookup_code = pdt.document_subtype

AND pha.org_id = pdt.org_id

AND pdt.document_type_code = 'PO'

--AND authorization_status in ('INCOMPLETE', 'REQUIRES REAPPROVAL')

--AND segment1 = '4456666'; -- Enter the Purchase Order Number

and pha.po_header_id = 2143276;

BEGIN

fnd_global.apps_initialize (user_id => 4131,

resp_id => 52375,

resp_appl_id => 201);

FOR i IN purchase_order

LOOP

mo_global.set_policy_context ('S', '1814');

SELECT i.po_header_id ||'-'|| to_char(po_wf_itemkey_s.NEXTVAL)

INTO v_item_key FROM dual;

dbms_output.put_line (' Calling po_reqapproval_init1.start_wf_process for po_id=>' ||i.segment1);

po_reqapproval_init1.start_wf_process(

ItemType => 'POAPPRV'

, ItemKey => v_item_key

, WorkflowProcess => 'POAPPRV_TOP'

, ActionOriginatedFrom => 'PO_FORM'

, DocumentID => i.po_header_id -- po_header_id

, DocumentNumber => i.segment1 -- Purchase Order Number

, PreparerID => i.agent_id -- Buyer/Preparer_id

, DocumentTypeCode => i.document_type_code--'PO'

, DocumentSubtype => i.document_subtype --'STANDARD'

, SubmitterAction => 'APPROVE'

, forwardToID => NULL

, forwardFromID => NULL

, DefaultApprovalPathID => NULL

, Note => NULL

, PrintFlag => 'N'

, FaxFlag => 'N'

, FaxNumber => NULL

, EmailFlag => 'N'

, EmailAddress => NULL

, CreateSourcingRule => 'N'

, ReleaseGenMethod => 'N'

, UpdateSourcingRule => 'N'

, MassUpdateReleases => 'N'

, RetroactivePriceChange => 'N'

, OrgAssignChange => 'N'

, CommunicatePriceChange => 'N'

, p_Background_Flag => 'N'

, p_Initiator => NULL

, p_xml_flag => NULL

, FpdsngFlag => 'N'

, p_source_type_code => NULL);

commit;

DBMS_OUTPUT.PUT_LINE ('Purchase Order has been approved=>'|| i.segment1);

END LOOP;

END;

Query To Get Payment Method for Supplier in Account Payables in Oracle Apps R12

     SELECT DISTINCT pmthds.Payment_Method_Name, pmthds.Payment_Method_Code--, pmthds.inactive_date inactive_date, 'N'
    FROM IBY_APPLICABLE_PMT_MTHDS apmthds, IBY_PAYMENT_METHODS_VL pmthds
    WHERE apmthds.payment_method_Code = pmthds.payment_method_code
    AND apmthds.Payment_flow = 'DISBURSEMENTS'
    AND NVL(pmthds.inactive_date,trunc(sysdate)) >= trunc(sysdate)
    --AND apmthds.APPLICATION_ID = :1
    AND (apmthds.applicable_type_code = 'PAYEE')
    AND NOT EXISTS( SELECT 1 FROM IBY_EXT_PARTY_PMT_MTHDS PmtMthdAssignmentsEO
    WHERE apmthds.payment_method_Code = PmtMthdAssignmentsEO.payment_method_code
    AND apmthds.Payment_flow = PmtMthdAssignmentsEO.Payment_flow
    AND PmtMthdAssignmentsEO.EXT_Pmt_party_ID --= 66991
    IN (SELECT EXT_PAYEE_ID FROM IBY_EXTERNAL_PAYEES_ALL WHERE PAYEE_PARTY_ID = (SELECT PARTY_ID FROM AP_SUPPLIERS WHERE VENDOR_ID = 871744) AND ROWNUM = 1))
    ;