Oracle Apps Knowledge Sharing

In this post, we have given the sample script to retire an API in R12.

API: FA_RETIREMENT_PUB.DO_RETIREMENT 

Test Instance: R12.1.3 

Script: 

SET SERVEROUTPUT ON;
  DECLARE
    le_api_error        EXCEPTION;
	
	-- Asset to be retired
	lv_asset_number     VARCHAR2(100) DEFAULT '43296';
	lv_book_type_code   VARCHAR2(15)  DEFAULT 'CORP';
	
    ln_asset_id         NUMBER;    
    ln_user_id          NUMBER := 12345;
    ln_cost_retired     NUMBER;
    ln_proceeds_of_sale NUMBER := 0;
    ln_cost_of_removal  NUMBER := 0;
    ln_request_id       NUMBER;
    lr_trans_rec        fa_api_types.trans_rec_type;
    lr_dist_trans_rec   fa_api_types.trans_rec_type;
    lr_asset_hdr_rec    fa_api_types.asset_hdr_rec_type;
    lr_asset_retire_rec fa_api_types.asset_retire_rec_type;
    lt_asset_dist_tbl   fa_api_types.asset_dist_tbl_type;
    lt_subcomp_tbl      fa_api_types.subcomp_tbl_type;
    lt_inv_tbl          fa_api_types.inv_tbl_type;
    ln_api_version      NUMBER := 1;
    lv_init_msg_list    VARCHAR2(1) := fnd_api.g_false;
    lv_commit           VARCHAR2(1) := fnd_api.g_true;
    lv_validation_level NUMBER := fnd_api.g_valid_level_full;
    lv_calling_func     VARCHAR2(80) := 'Shareoracleapps Wrapper';
    lv_return_sts       VARCHAR2(1) := fnd_api.g_false;
    ln_msg_cnt          NUMBER := 0;
    lv_message          VARCHAR2(512);
    ln_count            NUMBER;
    i                   NUMBER := 0;
    lv_dummy            VARCHAR2(512);
    ln_message_count    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 a.asset_number = lv_asset_number
      AND a.creation_date > sysdate - 300
      AND b.book_type_code = lv_book_type_code
      AND ROWNUM < 5
      AND NOT EXISTS ( SELECT 1
                         FROM fa_retirements fr
                        WHERE fr.asset_id = a.asset_id
                      );

  BEGIN
    fnd_global.apps_initialize(user_id => 12345, 
                               resp_id => 50561, 
                               resp_appl_id => 140
                               );

    FOR i IN cur_fa_addition 
    LOOP
      BEGIN         
        fa_srvr_msg.init_server_message;
        fa_debug_pkg.set_debug_flag(debug_flag => 'YES');
        dbms_output.put_line('Asset_id :' || i.asset_id);
            
        ln_asset_id     := i.asset_id;
        ln_cost_retired := i.cost;
            
        -- Get Standard Who Info
        ln_request_id := fnd_global.conc_request_id;
        fnd_profile.get('LOGIN_ID', lr_trans_rec.who_info.last_update_login);
        fnd_profile.get('USER_ID', lr_trans_rec.who_info.last_updated_by);
        IF ( lr_trans_rec.who_info.last_updated_by IS NULL ) 
        THEN
          lr_trans_rec.who_info.last_updated_by := -1;
        END IF;

        IF ( lr_trans_rec.who_info.last_update_login IS NULL ) 
        THEN
          lr_trans_rec.who_info.last_update_login := -1;
        END IF;

        lr_trans_rec.who_info.last_update_date := SYSDATE;
        lr_trans_rec.who_info.creation_date    := SYSDATE;
        lr_trans_rec.who_info.created_by       := lr_trans_rec.who_info.last_updated_by;
        lr_asset_hdr_rec.asset_id              := ln_asset_id;
        lr_asset_hdr_rec.book_type_code        := lv_book_type_code;
        lr_trans_rec.transaction_type_code     := NULL;
        
        -- This Will Be Determined Inside Api
        lr_trans_rec.transaction_date_entered := NULL;
        lr_asset_hdr_rec.period_of_addition   := NULL;
        lr_asset_retire_rec.retirement_prorate_convention := NULL;
        lr_asset_retire_rec.date_retired      := NULL;
        -- Will Be Current Period By Default
        lr_asset_retire_rec.units_retired     := NULL;
        lr_asset_retire_rec.cost_retired      := ln_cost_retired;
        lr_asset_retire_rec.proceeds_of_sale  := ln_proceeds_of_sale;
        lr_asset_retire_rec.cost_of_removal   := ln_cost_of_removal;
        lr_asset_retire_rec.retirement_type_code := 'SALE';
        lr_asset_retire_rec.trade_in_asset_id := NULL;
        lr_asset_retire_rec.calculate_gain_loss := fnd_api.g_false;
        fnd_profile.put('USER_ID', ln_user_id);
        
        lt_asset_dist_tbl.DELETE;
        dbms_output.put_line('Call API');
        fa_retirement_pub.do_retirement
                         (
                          p_api_version       => ln_api_version, 
                          p_init_msg_list     => lv_init_msg_list, 
                          p_commit            => lv_commit,
                          p_validation_level  => lv_validation_level, 
                          p_calling_fn        => lv_calling_func,
                          x_return_status     => lv_return_sts, 
                          x_msg_count         => ln_msg_cnt, 
                          x_msg_data          => lv_message, 
                          px_trans_rec        => lr_trans_rec, 
                          px_dist_trans_rec   => lr_dist_trans_rec,
                          px_asset_hdr_rec    => lr_asset_hdr_rec,
                          px_asset_retire_rec => lr_asset_retire_rec, 
                          p_asset_dist_tbl    => lt_asset_dist_tbl, 
                          p_subcomp_tbl       => lt_subcomp_tbl, 
                          p_inv_tbl           => lt_inv_tbl
                         );

        IF lv_return_sts = fnd_api.g_false 
        THEN
          RAISE le_api_error;
        ELSE
          dbms_output.put_line('lv_return_sts :' || lv_return_sts);
        END IF;

        dbms_output.put_line('Asset Retirement Done: id: '
                            || lr_asset_retire_rec.retirement_id
                            );

 
        IF ( fa_debug_pkg.print_debug ) 
        THEN
          fa_debug_pkg.write_debug_log;
        END IF;
        fa_srvr_msg.add_message(calling_fn => lv_calling_func, 
                                name       => 'FA_SHARED_END_SUCCESS', 
                                token1     => 'PROGRAM', 
                                value1 => 'RETIREMENT_API'
                               );

        ln_message_count := fnd_msg_pub.count_msg;
        IF ( ln_message_count > 0 ) 
        THEN
          lv_dummy := fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
          dbms_output.put_line('dump: ' || lv_dummy);
          FOR i IN 1..( ln_message_count - 1 ) 
		  LOOP
            lv_dummy := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
            dbms_output.put_line('dump: ' || lv_dummy);
          END LOOP;

        ELSE
          dbms_output.put_line('dump: NO MESSAGE !');
        END IF;
      EXCEPTION
        WHEN OTHERS THEN
           dbms_output.put_line('Error :' || sqlerrm);
        END;
    END LOOP;

  EXCEPTION
    WHEN le_api_error THEN
      ROLLBACK WORK;
      fa_srvr_msg.add_message(calling_fn => lv_calling_func, 
		              name       => 'FA_SHARED_PROGRAM_FAILED', 
			      token1     => 'PROGRAM', 
			      value1     => lv_calling_func);

      ln_message_count := fnd_msg_pub.count_msg;
      IF ( ln_message_count > 0 )
	  THEN
  	    lv_dummy := fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
        dbms_output.put_line('dump: ' || lv_dummy);
        FOR i IN 1..( ln_message_count - 1 ) 
		LOOP
          lv_dummy := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
          dbms_output.put_line('dump: ' || lv_dummy);
        END LOOP;

      ELSE
        dbms_output.put_line('dump: NO MESSAGE !');
      END IF;

      COMMIT;
END;
/
 
Script to submit “Calculate Gain or Loss”: 
DECLARE 
   lv_book_type_code   VARCHAR2 (15) := 'CORP';   
   ln_request_id       NUMBER;
BEGIN
   fnd_global.apps_initialize (user_id           => 12345,
                               resp_id           => 50561,
                               resp_appl_id      => 140
                              );
   ln_request_id :=
          fnd_request.submit_request ('OFA',
                                      'FARET',
                                       null,
                                       null,
                                       false,
                                       lv_book_type_code
                                      );
   COMMIT;
   DBMS_OUTPUT.PUT_LINE (ln_request_id);
End;

Sample Execution :

In this post, we have given the query which gives you the joins between AP payment tables, Bank statement tables, Internal bank Account details and vendor details.  

Tested instance: R12.2.4 

Tables Involved: 

Query:  

SELECT 
    cba.bank_account_id ,
    ac.bank_account_name,
    ac.bank_account_num,
    ac.bank_account_type,
    ac.bank_num,
    ac.base_amount,
    ac.address_line1,
    ac.address_line2,
    ac.address_line3,
    ac.address_line4,
    nvl(ac.address_style, 'DEFAULT') address_style,
    ac.amount,
    ac.checkrun_id,
    ac.checkrun_name,
    ac.check_date,
    ac.check_id,
    ac.check_number,
    pd.payment_document_id,
    ac.check_voucher_num,
    ac.city,
    ac.cleared_amount,
    ac.cleared_base_amount,
    ac.cleared_date,
    ac.cleared_exchange_date,
    ac.cleared_exchange_rate,
    ac.cleared_exchange_rate_type,
    ac.country,
    ac.county,
    ac.currency_code,
    ac.doc_category_code,
    ac.doc_sequence_id,
    ac.doc_sequence_value,
    ac.exchange_date,
    ac.exchange_rate,
    ac.exchange_rate_type,
    ac.future_pay_due_date,
    ac.org_id,
    ac.payment_method_code,
    ac.payment_type_flag,
    ac.province,
    ac.released_date,
    ac.released_by,
    ac.state,
    ac.status_lookup_code,
    ac.stopped_date,
    ac.stopped_by,
    ac.treasury_pay_date,
    ac.treasury_pay_number,
    ac.vendor_id,
    ac.vendor_name,
    ac.vendor_site_code,
    ac.vendor_site_id,
    ac.void_date,
    ac.withholding_status_lookup_code,
    ac.zip,
    ac.address_line1
    || DECODE(ac.address_line1, NULL, '', fnd_global.local_chr(10))
    || ac.address_line2
    || DECODE(ac.address_line2, NULL, '', fnd_global.local_chr(10))
    || ac.address_line3
    || DECODE(ac.address_line3, NULL, '', fnd_global.local_chr(10))
    || ac.city
    || ', '
    || ac.state
    || ' '
    || ac.zip
    || DECODE(ac.city, 
             NULL, DECODE(ac.state, 
                          NULL, DECODE(ac.zip, 
                                       NULL, '', fnd_global.local_chr(10)
                                      ), 
                          fnd_global.local_chr(10)
                         ), 
             fnd_global.local_chr(10)
             ) 
    || ac.country address,
    cbb.bank_name,
    DECODE(paycard_reference_id, 
           NULL, cba.bank_account_name, 
           ac.bank_account_name
          ),
    cba.currency_code ,
    aspa.set_of_books_id  ,
    pd.payment_document_name,
    alc1.displayed_field,
    iby1.payment_method_name ,
    alc3.displayed_field check_status,
    stopped_date stop_date,
    fds.name doc_sequence_name,
    fdsc.name doc_category_name,
    ft.territory_short_name,
    gdct.user_conversion_type user_rate_type,
    nvl(pv.vendor_name, 
        DECODE(ac.party_id, '', '*****', hzp.party_name)
        ) current_vendor_name,
    DECODE(ac.vendor_id, 
           '', DECODE(ac.party_id, '', '****', NULL), 
           pv.segment1
           )  vendor_number,
    DECODE(ac.vendor_id, 
          '', DECODE(ac.party_id, '', '***', NULL), 
          pv.num_1099
          )  num_1099,
    DECODE(ac.vendor_site_id, '', 
            DECODE(ac.party_id, '', '****', NULL), 
            DECODE(sign(ac.vendor_site_id), - 1, NULL, pvs.vendor_site_code)
          )  current_vendor_site_code,
    DECODE(sign(ac.vendor_site_id), - 1, 
           NULL, hzl.address1
                 || ' '
                 || hzl.address2
                 || ' '
                 || hzl.city
                 || ' '
                 || hzl.state
                 || ' '
                 || hzl.postal_code
         )  trading_partner_address,
    csh.statement_number ,
    csl.line_number statement_line_number,
    ap_auto_payment_pkg.selection_criteria_exists(ac.check_id) 
    selection_criteria_flag,
    ap_invoice_payments_pkg.get_max_gl_date(ac.check_id) max_payment_gl_date,
    ac.positive_pay_status_code,
    ac.transfer_priority,
    ac.external_bank_account_id, 
    ac.stamp_duty_amt,
    ac.stamp_duty_base_amt,
    ac.maturity_exchange_date,
    ac.maturity_exchange_rate_type,
    ac.maturity_exchange_rate,
    gdct1.user_conversion_type maturity_user_rate_type,
    ac.description,
    ac.anticipated_value_date,
    ac.actual_value_date,
    ap_checks_pkg.get_posting_status(ac.check_id),
    iby3.meaning bank_charge_bearer_dsp,
    iby5.meaning settlement_priority_dsp,
    ac.bank_charge_bearer,
    ac.settlement_priority,
    ac.party_id,
    ac.party_site_id,
    iby2.payment_profile_id,
    iby2.payment_profile_name,
    iby2.processing_type,
    ac.payment_id,
    ac.legal_entity_id,
    ac.void_check_id,
    ac.void_check_number,
    ac.ce_bank_acct_use_id,
    ac.remit_to_supplier_name remit_to_supplier_name,
    ac.remit_to_supplier_id remit_to_supplier_id,
    ac.remit_to_supplier_site remit_to_supplier_site,
    ac.remit_to_supplier_site_id  remit_to_supplier_site_id,
    pv1.segment1 remit_to_supplier_number,
    DECODE(sign(ac.remit_to_supplier_site_id),
           - 1, NULL, hzl1.address1
                      || ' '
                      || hzl1.address2
                      || ' '
                      || hzl1.city
                      || ' '
                      || hzl1.state
                      || ' '
                      || hzl1.postal_code
           ) remit_to_supplier_address,
    ac.relationship_id relationship_id, 
    ac.paycard_authorization_number,
    ac.paycard_reference_id,
    DECODE(ac.remit_to_supplier_id, 
           '', DECODE(pv1.party_id, '', 'NA', NULL), 
           pv1.num_1099
          ) remit_num_1099,
    DECODE(pv1.party_id, 
           '', 'NA', 
           hzp1.party_name
           ) current_remit_to_supplier_name,
    DECODE(ac.remit_to_supplier_site_id,
           '', DECODE(pv1.party_id, '', 'NA', NULL), 
           DECODE(sign(ac.remit_to_supplier_site_id), - 1,NULL, 
                  pvs1.vendor_site_code
                 )
          ) current_remit_vendor_site_code,
    ac.acknowledged_flag  acknowledged_flag
FROM
    ce_bank_accounts            cba,
    ce_bank_acct_uses_all       cbau,
    ce_bank_branches_v          cbb,
    ap_system_parameters_all    aspa,
    ce_payment_documents        pd,
    ap_lookup_codes             alc1,
    iby_payment_methods_vl      iby1,
    iby_payment_profiles        iby2,
    fnd_lookups                 iby3,
    fnd_lookups                 iby5,
    ap_lookup_codes             alc3,
    fnd_document_sequences      fds,
    fnd_doc_sequence_categories fdsc,
    fnd_territories_vl          ft,
    gl_daily_conversion_types   gdct,
    ap_suppliers                pv,
    ap_supplier_sites_all       pvs,
    ce_statement_reconcils_all  csr,
    ce_statement_headers        csh,
    ce_statement_lines          csl,
    ap_checks_all               ac,
    gl_daily_conversion_types   gdct1,
    hz_parties                  hzp,
    hz_party_sites              hps,
    hz_locations                hzl, 
    ap_suppliers                pv1,
    ap_supplier_sites_all       pvs1,
    hz_party_sites              hps1,
    hz_locations                hzl1, 
    hz_parties                  hzp1 
WHERE ac.ce_bank_acct_use_id    = cbau.bank_acct_use_id (+)
    AND cbau.bank_account_id    = cba.bank_account_id (+)
    AND cbau.org_id             = aspa.org_id (+)
    AND ac.maturity_exchange_rate_type = gdct1.conversion_type (+)
    AND cbb.branch_party_id (+) = cba.bank_branch_id
    AND ac.payment_document_id  = pd.payment_document_id (+)
    AND alc1.lookup_type        = 'PAYMENT TYPE'
    AND alc1.lookup_code        = ac.payment_type_flag
    AND iby1.payment_method_code (+) = ac.payment_method_code
    AND iby2.payment_profile_id (+) = ac.payment_profile_id
    AND alc3.lookup_type (+)    = 'CHECK STATE'
    AND alc3.lookup_code (+)    = ac.status_lookup_code
    AND ac.bank_charge_bearer   = iby3.lookup_code (+)
    AND iby3.lookup_type (+)    = 'IBY_BANK_CHARGE_BEARER'
    AND ac.settlement_priority  = iby5.lookup_code (+)
    AND iby5.lookup_type (+)    = 'IBY_SETTLEMENT_PRIORITY'
    AND ac.doc_sequence_id      = fds.doc_sequence_id (+)
    AND fdsc.application_id (+) = 200
    AND ac.doc_category_code    = fdsc.code (+)
    AND ac.country              = ft.territory_code (+)
    AND ac.exchange_rate_type   = gdct.conversion_type (+)
    AND ac.vendor_id            = pv.vendor_id (+)
    AND ac.party_id             = hzp.party_id (+)
    AND ac.vendor_site_id       = pvs.vendor_site_id (+)
    AND ac.party_site_id        = hps.party_site_id (+)
    AND hps.location_id         = hzl.location_id (+)
    AND csr.reference_type (+)  = 'PAYMENT'
    AND csr.reference_id (+)    = ac.check_id
    AND csr.current_record_flag (+) = 'Y'
    AND csr.statement_line_id   = csl.statement_line_id (+)
    AND csl.statement_header_id = csh.statement_header_id (+)
    AND csr.status_flag (+)     = 'M' 
    AND ac.remit_to_supplier_id = pv1.vendor_id (+)
    AND ac.remit_to_supplier_site_id = pvs1.vendor_site_id (+)
    AND pvs1.party_site_id      = hps1.party_site_id (+)
    AND hps1.location_id        = hzl1.location_id (+) 
    AND pv1.party_id            = hzp1.party_id (+) 
    AND ac.creation_date > SYSDATE - 50;