Fixed Asset Retirement and cost adjustment using API

API For Cost Adjsutment

/* Formatted on 2013/05/06 13:01 (Formatter Plus v4.8.8) */
declare
  l_trans_rec                 fa_api_types.trans_rec_type;
  l_asset_hdr_rec             fa_api_types.asset_hdr_rec_type;
  l_asset_fin_rec_adj         fa_api_types.asset_fin_rec_type;
  l_asset_fin_rec_new         fa_api_types.asset_fin_rec_type;
  l_asset_fin_mrc_tbl_new     fa_api_types.asset_fin_tbl_type;
  l_inv_trans_rec             fa_api_types.inv_trans_rec_type;
  l_inv_tbl                   fa_api_types.inv_tbl_type;
  l_inv_rate_tbl              fa_api_types.inv_rate_tbl_type;
  l_asset_deprn_rec_adj       fa_api_types.asset_deprn_rec_type;
  l_asset_deprn_rec_new       fa_api_types.asset_deprn_rec_type;
  l_asset_deprn_mrc_tbl_new   fa_api_types.asset_deprn_tbl_type;
  l_inv_rec                   fa_api_types.inv_rec_type;
  l_group_reclass_options_rec fa_api_types.group_reclass_options_rec_type;
  l_return_status             varchar2(1);
  l_mesg_count                number := 0;
  l_mesg_len                  number;
  l_mesg                      varchar2(4000);
  l_asset_id                  number;
  --l_new_life FA_API_TYPES.asset_hr_attr_rec_type;
  cursor cur_fa_addition is
    select a.asset_number, b.cost
      from fa_additions_b a, fa_books_v b
     where a.asset_id = b.asset_id
       and b.cost not in (0)
       and a.asset_number = '5612960'
       and a.attribute4 = '0000002526'
      and not exists (select 1 from fa_retirements fr where fr.asset_id=a.asset_id)            
                  ;
begin
  for c1 in cur_fa_addition loop
    begin
      select asset_id
        into l_asset_id
        from fa_additions_b
       where asset_number = c1.asset_number;
    exception
      when others then
        dbms_output.put_line(' Asset number does not exit' ||
                             c1.asset_number);
    end;
    fnd_profile.put('PRINT_DEBUG', 'Y');
    dbms_output.enable(1000000);
    fa_srvr_msg.init_server_message;
    fa_debug_pkg.initialize;
    --asset header info
    l_asset_hdr_rec.asset_id       := l_asset_id;
    l_asset_hdr_rec.book_type_code := 'CORPORATE BOOK';
    -- fin info
    l_asset_fin_rec_adj.cost := -c1.cost;---c1.asset_cost;
    --l_new_life.life_in_months:=100;
    fa_adjustment_pub.do_adjustment(p_api_version           => 1.0,
                                    p_init_msg_list         => fnd_api.g_false,
                                    p_commit                => fnd_api.g_false,
                                    p_validation_level      => fnd_api.g_valid_level_full,
                                    x_return_status         => l_return_status,
                                    x_msg_count             => l_mesg_count,
                                    x_msg_data              => l_mesg,
                                    p_calling_fn            => 'ADJ_TEST_SCRIPT',
                                    px_trans_rec            => l_trans_rec,
                                    px_asset_hdr_rec        => l_asset_hdr_rec,
                                    p_asset_fin_rec_adj     => l_asset_fin_rec_adj,
                                    x_asset_fin_rec_new     => l_asset_fin_rec_new,
                                    x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
                                    px_inv_trans_rec        => l_inv_trans_rec,
                                    px_inv_tbl              => l_inv_tbl,
                                    -- px_inv_rate_tbl => l_inv_rate_tbl,
                                    p_asset_deprn_rec_adj       => l_asset_deprn_rec_adj,
                                    x_asset_deprn_rec_new       => l_asset_deprn_rec_new,
                                    x_asset_deprn_mrc_tbl_new   => l_asset_deprn_mrc_tbl_new,
                                    p_group_reclass_options_rec => l_group_reclass_options_rec);
    dbms_output.put_line(l_return_status);
    if (l_return_status <> fnd_api.g_ret_sts_success) then
      fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
      l_mesg_count := fnd_msg_pub.count_msg;
      if l_mesg_count > 0 then
        l_mesg := substr(fnd_msg_pub.get(fnd_msg_pub.g_first,
                                         fnd_api.g_false),
                         1,
                         512);
        dbms_output.put_line(substr(l_mesg, 1, 255));
        for i in 1 .. l_mesg_count - 1 loop
          l_mesg := substr(fnd_msg_pub.get(fnd_msg_pub.g_next,
                                           fnd_api.g_false),
                           1,
                           512);
          dbms_output.put_line(substr(l_mesg, 1, 255));
        end loop;
        fnd_msg_pub.delete_msg();
      end if;
    else
      dbms_output.put_line('SUCCESS');
      commit;
      dbms_output.put_line('THID' ||
                           to_char(l_trans_rec.transaction_header_id));
    end if;
  end loop;
end;

--------------------------------------------------------------------

API for Asset Retirement after making cost to zero

declare

  /* define local record types */

  l_trans_rec FA_API_TYPES.trans_rec_type;

  l_dist_trans_rec FA_API_TYPES.trans_rec_type;

  l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;

  l_asset_retire_rec FA_API_TYPES.asset_retire_rec_type;

  l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;

  l_subcomp_tbl FA_API_TYPES.subcomp_tbl_type;

  l_inv_tbl FA_API_TYPES.inv_tbl_type;

  /* misc info */

  l_api_version number := 1;

  l_init_msg_list varchar2(1) := FND_API.G_FALSE;

  l_commit varchar2(1) := FND_API.G_FALSE;

  l_validation_level number := FND_API.G_VALID_LEVEL_FULL;

  l_calling_fn varchar2(80) := 'Retirement test wrapper';

  l_return_status VARCHAR2(1);

  l_mesg_count number := 0;

  l_mesg_len number;

  l_mesg varchar2(4000);

  l_source_line_id number ;

  l_cost_retired number ;

  

   cursor cur_fa_addition is

    select a.asset_number,a.asset_id, b.cost

      from fa_additions_b a, fa_books_v b

     where a.asset_id = b.asset_id

       and b.cost in (0)

       and a.asset_number = '5612960'

       and a.attribute4 = '0000002526'

       and not exists

     (select 1 from fa_retirements fr where fr.asset_id = a.asset_id);

  

  

  

begin

  l_source_line_id :=null ;

  l_cost_retired := 0;

  

  

  dbms_output.disable;

  dbms_output.enable(1000000);

  dbms_output.put_line('begin');

  fnd_profile.put('PRINT_DEBUG', 'Y');

  dbms_output.enable(1000000);

  FA_SRVR_MSG.Init_Server_Message;

  FA_DEBUG_PKG.Initialize;

for i in cur_fa_addition loop

  -- Get standard who info

  l_asset_hdr_rec.asset_id := i.asset_id;

  l_asset_hdr_rec.book_type_code :='&book_type_code';

  l_asset_retire_rec.calculate_gain_loss := FND_API.G_FALSE;

  l_asset_dist_tbl.delete;

  l_inv_tbl(1).source_line_id := l_source_line_id;

  l_inv_tbl(1).fixed_assets_cost := l_cost_retired;

  FA_RETIREMENT_PUB.do_retirement (p_api_version => l_api_version

                                  ,p_init_msg_list => l_init_msg_list

                                   ,p_commit => l_commit

                                   ,p_validation_level => l_validation_level

                                   ,p_calling_fn => l_calling_fn

                                    ,x_return_status => l_return_status

                                    ,x_msg_count => l_mesg_count

                                    ,x_msg_data => l_mesg

                                    ,px_trans_rec => l_trans_rec

                                    ,px_dist_trans_rec => l_dist_trans_rec

                                    ,px_asset_hdr_rec => l_asset_hdr_rec

                                    ,px_asset_retire_rec => l_asset_retire_rec

                                    ,p_asset_dist_tbl => l_asset_dist_tbl

                                    ,p_subcomp_tbl => l_subcomp_tbl

                                    ,p_inv_tbl => l_inv_tbl

                                     );

 

  dbms_output.put_line('Return Status: ' || l_return_status);

  if (fa_cache_pkg.fa_print_debug) then

  fa_debug_pkg.dump_debug_messages(max_mesgs => 0);

  end if;

  l_mesg_count := fnd_msg_pub.count_msg;

  if l_mesg_count > 0 then

  l_mesg := chr(10) || substr(fnd_msg_pub.get

  (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),

  1, 512);

  dbms_output.put_line(l_mesg);

  for i in 1..(l_mesg_count - 1) loop

  l_mesg :=

  substr(fnd_msg_pub.get

  (fnd_msg_pub.G_NEXT,

  fnd_api.G_FALSE), 1, 512);

  dbms_output.put_line(l_mesg);

  end loop;

  fnd_msg_pub.delete_msg();

  end if;

  if l_return_status = FND_API.G_FALSE then

  dbms_output.put_line('FAILURE');

  rollback;

  else

  dbms_output.put_line('SUCCESS');

  dbms_output.put_line('test wrapper: retirement_id: '||l_asset_retire_rec.retirement_id);

  end if;

end loop ;

end;

PO and Purchase Requisition Link in Oracle Apps

-- ALL OPEN PO'S

SELECT h.segment1 "PO NUM",
h.authorization_status "STATUS",

l.line_num "SEQ NUM",

ll.line_location_id,
d.po_distribution_id,

h.type_lookup_code "TYPE"

FROM po.po_headers_all h,
po.po_lines_all l,
po.po_line_locations_all ll,
po.po_distributions_all d
WHERE h.po_header_id = l.po_header_id
AND ll.po_line_id = l.po_Line_id
AND ll.line_location_id = d.line_location_id
AND h.closed_date IS NULL
AND h.type_lookup_code NOT IN ('QUOTATION')

-- PO WITHOUT PURCHASE REQUISITION

SELECT prh.segment1 "PR NUM",
TRUNC (prh.creation_date) "CREATED ON",
TRUNC (prl.creation_date) "Line Creation Date",
prl.line_num "Seq #",
msi.segment1 "Item Num",
prl.item_description "Description",
prl.quantity "Qty",
TRUNC (prl.need_by_date) "Required By",
ppf1.full_name "REQUESTOR",
ppf2.agent_name "BUYER"
FROM po.po_requisition_headers_all prh,
po.po_requisition_lines_all prl,
apps.per_people_f ppf1,
( SELECT DISTINCT agent_id, agent_name FROM apps.po_agents_v) ppf2,
po.po_req_distributions_all prd,
inv.mtl_system_items_b msi,
po.po_line_locations_all pll,
po.po_lines_all pl,
po.po_headers_all ph
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
AND ppf1.person_id = prh.preparer_id
AND prh.creation_date BETWEEN ppf1.effective_start_date
AND ppf1.effective_end_date
AND ppf2.agent_id(+) = msi.buyer_id
AND msi.inventory_item_id = prl.item_id
AND msi.organization_id = prl.destination_organization_id
AND pll.line_location_id(+) = prl.line_location_id
AND pll.po_header_id = ph.po_header_id(+)
AND pll.po_line_id = pl.po_line_id(+)
AND prh.authorization_status = 'APPROVED'
AND pll.line_location_id IS NULL
AND prl.closed_code IS NULL
AND NVL (prl.cancel_flag, 'N') <> 'Y'

ORDER BY 1, 2, 4

-- REQUISITION AND PO

SELECT r.segment1 "Req Num", ph.segment1 "PO Num"
FROM po_headers_all ph,
po_distributions_all d,
po_req_distributions_all rd,
po_requisition_lines_all rl,
po_requisition_headers_all r
WHERE ph.po_header_id = d.po_header_id
AND d.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = rl.requisition_line_id
AND rl.requisition_header_id = r.requisition_header_id

-- CANCELLED REQUISITIONS

SELECT prh.REQUISITION_HEADER_ID,
prh.PREPARER_ID,
prh.SEGMENT1 "REQ NUM",
TRUNC (prh.CREATION_DATE),
prh.DESCRIPTION,
prh.NOTE_TO_AUTHORIZER
FROM apps.Po_Requisition_headers_all prh, apps.po_action_history pah
WHERE action_code = 'CANCEL'
AND pah.object_type_code = 'REQUISITION'
AND pah.object_id = prh.REQUISITION_HEADER_ID

-- INTERNAL REQUISITION WITHOUT INTERNAL SALES ORDER

SELECT RQH.SEGMENT1,
RQL.LINE_NUM,
RQL.REQUISITION_HEADER_ID,
RQL.REQUISITION_LINE_ID,
RQL.ITEM_ID,
RQL.UNIT_MEAS_LOOKUP_CODE,
RQL.UNIT_PRICE,
RQL.QUANTITY,
RQL.QUANTITY_CANCELLED,
RQL.QUANTITY_DELIVERED,
RQL.CANCEL_FLAG,
RQL.SOURCE_TYPE_CODE,
RQL.SOURCE_ORGANIZATION_ID,
RQL.DESTINATION_ORGANIZATION_ID,
RQH.TRANSFERRED_TO_OE_FLAG
FROM PO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQH
WHERE RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID
AND RQL.SOURCE_TYPE_CODE = 'INVENTORY'
AND RQL.SOURCE_ORGANIZATION_ID IS NOT NULL
AND NOT EXISTS
(SELECT 'existing internal order'
FROM OE_ORDER_LINES_ALL LIN
WHERE LIN.SOURCE_DOCUMENT_LINE_ID =
RQL.REQUISITION_LINE_ID
AND LIN.SOURCE_DOCUMENT_TYPE_ID = 10)
ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM

API To Cancel or Finally Close Requisition

API To Cancel or Finally Close Requisition

Do we have any API to cancel or finally close a Purchase Requisition:

Yes, we do have one API & that is po_reqs_control_sv.update_reqs_status which can be used for finally closing or cancelling the requisition. We need to pass the parameters like requisition_header_id, requisition_line_id, Preparer_id, document_type_code,
type_lookup_code, req_control_action, Req_control_reason and the other default parameter to the API.

-- R12 - PO - Sample Script to Cancel PR Using API


DECLARE

X_req_control_error_rc VARCHAR2 (500);
l_org_id NUMBER := 308; -- Enter the Operating_Unit Here
cnt number := 0;

CURSOR C_REQ_CANCEL is

SELECT
prh.segment1 requisition_num,
prh.requisition_header_id,
prh.org_id,
prl.requisition_line_id,
prh.preparer_id,
prh.type_lookup_code,
pdt.document_type_code,
prh.authorization_status,
prl.line_location_id
FROM
apps.po_requisition_headers_all prh,
apps.po_requisition_lines_all prl,
apps.po_document_types_all pdt
WHERE 1 = 1
AND prh.org_id = l_org_id
AND pdt.document_type_code = 'REQUISITION'
AND prh.authorization_status = 'APPROVED'
AND prl.line_location_id is null
AND prh.requisition_header_id = prl.requisition_header_id
AND prh.type_lookup_code = pdt.document_subtype
AND prh.org_id = pdt.org_id
AND prh.segment1 = '21170000909'; -- Enter The Requisition Number

BEGIN

fnd_global.apps_initialize (user_id => 2083,
resp_id => 20707,
resp_appl_id => 201);

mo_global.init ('PO');
mo_global.set_policy_context ('S', l_org_id);

FOR i IN C_REQ_CANCEL

LOOP

dbms_output.put_line (' Calling po_reqs_control_sv.update_reqs_status to cancel the Requisition=>'|| i.requisition_num);
dbms_output.put_line ('======================================================');

po_reqs_control_sv.update_reqs_status(
X_req_header_id => i.requisition_header_id
, X_req_line_id => i.requisition_line_id
, X_agent_id => i.preparer_id
, X_req_doc_type => i.document_type_code
, X_req_doc_subtype => i.type_lookup_code
, X_req_control_action => 'CANCEL'
, X_req_control_reason => 'CANCELLED BY API'
, X_req_action_date => SYSDATE
, X_encumbrance_flag => 'N'
, X_oe_installed_flag => 'Y'
, X_req_control_error_rc => X_req_control_error_rc);

DBMS_OUTPUT.PUT_LINE ( 'Status Found:=> ' ||X_req_control_error_rc);
DBMS_OUTPUT.PUT_LINE ('Requisition Number cancelled is :=>'|| i.Requisition_num);

cnt := cnt+1;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Count is :=>' ||cnt);

END;

-- R12 - PO - Script to Finally Close PR Using API.sql


DECLARE

X_req_control_error_rc VARCHAR2 (500);
l_org_id NUMBER := 308; -- Enter the Operating_Unit Here
cnt number := 0;

CURSOR C_REQ_CLOSE is

SELECT
prh.segment1 requisition_num,
prh.requisition_header_id,
prh.org_id,
prl.requisition_line_id,
prh.preparer_id,
prh.type_lookup_code,
pdt.document_type_code,
prh.authorization_status,
prh.closed_code
FROM
apps.po_requisition_headers_all prh,
apps.po_requisition_lines_all prl,
apps.po_document_types_all pdt
WHERE 1 = 1
AND prh.org_id = l_org_id
AND pdt.document_type_code = 'REQUISITION'
AND prh.authorization_status = 'APPROVED'
AND prl.line_location_id is null
AND prh.requisition_header_id = prl.requisition_header_id
AND prh.type_lookup_code = pdt.document_subtype
AND prh.org_id = pdt.org_id
AND prh.segment1 = '21170002264'; -- Enter The Requisition Number

BEGIN

fnd_global.apps_initialize (user_id => 2083,
resp_id => 20707,
resp_appl_id => 201);

mo_global.init ('PO');
mo_global.set_policy_context ('S', l_org_id);

FOR i IN C_REQ_CLOSE

LOOP

DBMS_OUTPUT.PUT_LINE ('Calling po_reqs_control_sv.update_reqs_status to Finally Close Requisition=>' ||i.requisition_num);

DBMS_OUTPUT.PUT_LINE ('=======================================================');

po_reqs_control_sv.update_reqs_status(
X_req_header_id => i.requisition_header_id
, X_req_line_id => i.requisition_line_id
, X_agent_id => i.preparer_id
, X_req_doc_type => i.document_type_code
, X_req_doc_subtype => i.type_lookup_code
, X_req_control_action => 'FINALLY CLOSE'
, X_req_control_reason => 'FINALLY CLOSED BY API'
, X_req_action_date => SYSDATE
, X_encumbrance_flag => 'N'
, X_oe_installed_flag => 'Y'
, X_req_control_error_rc => X_req_control_error_rc);

DBMS_OUTPUT.PUT_LINE ( 'Status Found: '|| X_req_control_error_rc);

DBMS_OUTPUT.PUT_LINE ('Requisition Number which is Finally Closed =>'|| i.Requisition_num);

cnt := cnt+1;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Count is :=>' cnt);

END;