Delete AR(Account Receivable) Transaction Using API in Oracle Apps

Delete AR Transaction Using API:

Before delete AR transaction "Allow Transaction Deletion" flag should be Y.

Allow Transaction Deletion:
           Enable the Allow transaction deletion option if you want to let users delete transactions from Oracle Receivables after the transactions have been saved.
           You will update this option at Operating Unit level.
 
   Check in database table:
 
           SELECT invoice_deletion_flag
           FROM ar_system_parameters_all ab
           WHERE org_id = 201;   -- Operating Unit org_id
 
           If value of 'invoice_deletion_flag' is Y, its mean you are enable to delete AR Transaction, otherwise you need to do setup for this.
 
   Setup:

      Navigation:  Receivable Responsibility --> Setup --> System --> System Options

  Select 'Trans and Customers' Tab:

  Click on "Allow Transaction Deletion" button and Save it.
  Check in database table.
 
   To delete AR Transaction, follow below steps:
 
   Step 1) Create a staging table to stored data related to AR transaction.

           CREATE TABLE xx_delete_ar_transaction
           AS
             SELECT customer_trx_id,
                             trx_date,
                             trx_number,
                             org_id,
                             creation_date,
                             created_by,
                             'N' record_status
                 FROM ra_customer_trx_all
              WHERE org_id = 201
                    AND trx_number IN ('123000', '124000');

   Step 2) Call API in PLSQL block to Delete AR Transactions:

           DECLARE
              CURSOR lcur_ar_trans
              IS
                 SELECT ROWID,
                                 customer_trx_id,
                                 trx_date,
                                 trx_number,
                                 org_id,
                                 creation_date,
                                 created_by
                 FROM xx_delete_ar_transaction
                 WHERE record_status = 'N';

                          lc_msg_data                VARCHAR2(4000);
                          ln_msg_count              NUMBER;
                          lc_ret_status                VARCHAR2(1) := 'S';
                          v_message_tbl             arp_trx_validate.message_tbl_type;
                          ln_user_id                    NUMBER;
                          ln_responsibility_id     NUMBER;
                          ln_application_id         NUMBER;
                          ln_msg_index              NUMBER;

           BEGIN
              --
              ln_user_id                 := 0;
              ln_application_id      := 0;
              ln_responsibility_id  := 0;

              -- Get application and responsibility id's
              BEGIN
                       SELECT application_id, responsibility_id
                       INTO ln_application_id, ln_responsibility_id
                       FROM fnd_responsibility_tl
                       WHERE responsibility_name = 'Responsibility Name';  -- Enter valid Responsibility Name
              EXCEPTION
                       WHEN others THEN
                        lc_ret_status := 'E';
                        dbms_output.put_line('Error at getting Responsibility id: '||SQLERRM);
              END;
              -- Get User ID
              BEGIN
                       SELECT user_id
                       INTO ln_user_id
                       FROM fnd_user
                       WHERE user_name = 'ABC007';   -- Enter valid User Name
              EXCEPTION
                       WHEN others THEN
                        lc_ret_status := 'E';
                        dbms_output.put_line('Error at getting User id: '||SQLERRM);
              END;
              --
              FOR lreq_ar IN lcur_ar_trans
              LOOP
              -- Variable initialization:
                  ln_msg_count := NULL;
                  lc_msg_data := NULL;
                  --
                  dbms_output.put_line ('--> TRX Number: '|| lreq_ar.trx_number);
                  -- Set Org Context for the session
                  apps.mo_global.set_policy_context ('S', lreq_ar.org_id);
                  -- Set Oracle applications context
                  apps.fnd_global.apps_initialize (ln_user_id, ln_responsibility_id, ln_application_id);

                 -- Call API
                 ar_invoice_api_pub.delete_transaction (
                            p_api_name               => 'Delete_Transaction',
                            p_api_version            => 1.0,
                            p_init_msg_list          => fnd_api.g_true,
                            p_commit                   => fnd_api.g_true,
                            p_validation_level     => fnd_api.g_valid_level_full,
                            p_customer_trx_id     => lreq_ar.customer_trx_id,
                            p_return_status          => lc_ret_status,
                            p_msg_count             => ln_msg_count,
                            p_msg_data               => lc_msg_data,
                            p_errors                     => v_message_tbl);

                IF lc_ret_status <> 'S' THEN
                    dbms_output.put_line ('--> Status: '||lc_ret_status);
             
                    BEGIN
                         UPDATE xx_delete_ar_transaction
                         SET record_status = lc_ret_status
                         WHERE ROWID = lreq_ar.ROWID;
                    EXCEPTION
                         WHEN others THEN
                         dbms_output.put_line('Error at update staging table: '||SQLERRM);
                    END;
             
                    FOR i IN 1 .. ln_msg_count
                    LOOP
                            apps.fnd_msg_pub.get (i,
                                            apps.fnd_api.g_false,
                                            lc_msg_data,
                                            ln_msg_index
                                          );
                            dbms_output.put_line ('Error: '||lc_msg_data);
                     END LOOP;
                ELSE
                     dbms_output.put_line ('AR Transaction Successfully Deleted.');
                END IF;
                dbms_output.put_line ('--------------------');
                COMMIT;
              END LOOP;
           EXCEPTION
                 WHEN OTHERS THEN
                 dbms_output.put_line ('Error in main block: ' || SQLERRM);
           END;
           / 

Cannot update price since the Purchase Agreement doest not allow changes to the Purchase Order line price - Oracle Apps R12

Actual Behavior: While updating the Standard PO Line Unit Price using the Oracle API: PO_CHANGE_API1_S.UPDATE_PO , API returned a below error message:

"Cannot update price since the Purchase Agreement doest not allow changes to the Purchase Order line price."

Problematic Record details

Standard PO Num: 27578
        Line Num: 1
Item Num: A22909
Unit Price: 67.34

User was tyring to update new Unit Price as 70.21  -- at this step got an above error.

Analysis: Standard Purchase Order has linked with Blancket Purchase Agreement, and that BPA is the Global Purchase Agreement(GBPA). That's a reason API not allow to update PO Unit Price.

Query to check the Standard PO is linked or not with Global BPA:

        SELECT pha1.segment1 standard_po_num, pla1.line_num std_line_num
      ,pha2.segment1 GBPA_num, pla2.line_num gbpa_line_num, pha2.global_agreement_flag
FROM    po_headers_all pha1, po_lines_all pla1
      ,po_headers_all pha2, po_lines_all pla2
WHERE pha1.po_header_id = pla1.po_header_id
      AND pla1.from_header_id = pha2.po_header_id
      AND pla1.from_line_id   = pla2.po_line_id
      --
      AND pha1.segment1 = '27578'
      AND pla1.line_num = 1;

Solution: Oracle API has failed to update Standard PO Line level Unit Price because the value of "Allow_Price_Override_Flag" field of Global BPA line is N (No). So, need to update it as Y (Yes).

    We can do it through Purchase Order form: 

    Navigation: PO SuperUser  --> Purchase Orders --> Purchase Orders

        --> Query (F11) --> Enter Global BPA number --> Run (Ctrl + F11)

        --> Click on Price Reference tab --> Select Line --> Click on Allow Price Override check button. And save it

Note: If new and old Unit Price values are same then API will allow to update it.

Thank you..