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;