OPM Batch Release Query

OPM Batch release Code
=====================================


SET serveroutputon

DECLARE
l_batch_header gme_batch_header%ROWTYPE;
x_message_countNUMBER;
x_message_listVARCHAR2 (2000);
l_exception_material_tblgme_common_pvt.exceptions_tab;
x_return_statusVARCHAR2 (10);
x_batch_header gme_batch_header%ROWTYPE;
v_st_dateDATE;
v_com_dateDATE;
l_msg_index_outNUMBER;
BEGIN
SELECTplan_start_date,plan_cmplt_date INTOv_st_date,v_com_date
FROM gme_batch_header WHEREbatch_no= 3000046;
fnd_global.apps_initialize(user_id=> 1490,
resp_id=> 50764,resp_appl_id=> 553 );
l_batch_header.batch_type:= 0;
l_batch_header.batch_no:=3000046;
l_batch_header.plan_start_date:=v_st_date;
l_batch_header.plan_cmplt_date:=v_com_date;
l_batch_header.update_inventory_ind:= 'Y';
l_batch_header.recipe_validity_rule_id:=172;
--L_BATCH_HEADER.WIP_WHSE_CODE:=V_WHSE_CODE;
l_batch_header.actual_start_date:= SYSDATE;

gme_api_pub.release_batch (p_api_version=> 2.0,
p_validation_level=> 100,
p_init_msg_list=> fnd_api.g_false,
p_commti=> fnd_api.g_false,
x_message_count=>x_message_count,
x_message_list=>x_message_list,
x_return_status=>x_return_status,
p_batch_header_rec=>l_batch_header,
p_org_code=> 'VCP',
p_ignore_exception=> fnd_api.g_false,
p_validate_flexfields=> fnd_api.g_false,
x_batch_header_rec=>x_batch_header,
x_exception_material_tbl=>l_exception_material_tbl );
COMMTI;
IF x_return_status= FND_API.g_ret_sts_success THEN
dbms_output.put_line('Batch Released');
--l_batch_header.batch_id:=3000065;
-- SAVE THE CHANGES
--gme_api_pub.save_batch(
--p_batch_header => l_batch_header,
--x_return_status => x_return_status,
--p_commti =>fnd_api.g_true);
gme_api_pub.save_batch(p_header_id=>22001 ,
p_table=>2 ,
p_commti=> fnd_api.g_false ,
x_return_status=>x_return_status
--Bug#5584699 Changed the datatype from boolean to varchar2.,
p_clear_qty_cache=> fnd_api.g_true);
else
dbms_output.put_line('Batch Release failed'); IFx_message_count= 1 THEN
DBMS_OUTPUT.PUT_LINE('Error:'||x_message_list);
ELSE
FOR i IN 1..x_message_countLOOP FND_MSG_PUB.get (p_msg_index=>i ,
p_data=>X_message_list ,
p_msg_index_out=>l_msg_index_out);
DBMS_OUTPUT.PUT_LINE ('Error: '||x_message_list);
END LOOP;
end if;
end if;
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
RAISE;
END;


OPM BATCH Complete Batch

setserveroutputon;

-- request Create Cost Adjustment From AP Invoice

declare
l_statusVARCHAR2 (10);
l_return_statusVARCHAR2 (1):= fnd_api.g_ret_sts_success;
l_countNUMBER ;l_record_countNUMBER (10):= 0;
l_loop_cntNUMBER (10):= 0;
l_dummy_cntNUMBER (10):= 0;
l_dataVARCHAR2 (2000);
l_msg_index_outNUMBER;
l_in_batch_header_rec gme_batch_header%ROWTYPE;
l_batch_header_rec gme_batch_header%ROWTYPE;
l_exception_material_tblgme_common_pvt.exceptions_tab;
xnumber;
BEGIN fnd_global.apps_initialize(user_id=> 1490,resp_id=> 50764,resp_appl_id=> 553 );
l_in_batch_header_rec.batch_type:= 0;
l_in_batch_header_rec.batch_id:='36001';
--batch_id
l_in_batch_header_rec.update_inventory_ind:='Y';
l_in_batch_header_rec.actual_start_date:= SYSDATE;
p_msg_index_out=>l_msg_index_out );
DBMS_OUTPUT.put_line ('Error: '||SUBSTR (l_data, 1, 255));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error: '||SUBSTR (l_data, 1, 255));
END LOOP;
END IF;
END IF;
exception when others then null;
end ;

/After Completion it has to be done
set serveroutputon;

declare
x_message_countnumber;
x_message_listvarchar2(200);
x_return_statusvarchar2(100);
x_mmt_rec mtl_material_transactions%ROWTYPE;
x_mmln_tblgme_common_pvt.mtl_trans_lots_num_tbl;
l_mmti_rec mtl_transactions_interface%ROWTYPE;
l_mmli_tblgme_common_pvt.mtl_trans_lots_inter_tbl;
beginfnd_global.apps_initialize(user_id=> 1490,resp_id=> 50764,resp_appl_id=> 553 );
l_mmti_rec.source_code:='TEST';
l_mmti_rec.source_header_id:=2;
l_mmti_rec.source_line_id:=2;
l_mmti_rec.process_flag:=3;
l_mmti_rec.lock_flag:=2;
l_mmti_rec.transaction_mode:=3;
l_mmti_rec.last_update_date:=SYSDATE;
l_mmti_rec.last_updated_by:=fnd_global.user_id;
l_mmti_rec.creation_date:=SYSDATE;
l_mmti_rec.created_by:=fnd_global.user_id;
l_mmti_rec.organization_id:=423;
l_mmti_rec.inventory_item_id:=43006;
l_mmti_rec.inventory_item:='G011009300105M';
l_mmti_rec.transaction_quantity:=1;
l_mmti_rec.transaction_uom:='ECH';
l_mmti_rec.primary_quantity:=1;
l_mmti_rec.secondary_transaction_quantity:=22;
l_mmti_rec.secondary_uom_code:='GRM';
l_mmti_rec.transaction_date:=sysdate;
l_mmti_rec.SUBINVENTORY_CODE:='Corp_FG';
l_mmti_rec.transaction_type_id:=44;
--44;
l_mmti_rec.transaction_action_id:=31;
--31;
l_mmti_rec.transaction_source_type_id:=5;
l_mmti_rec.transaction_interface_id:=10001;
l_mmli_tbl(1).LOT_NUMBER:='MAH1456';
l_mmli_tbl(1).transaction_quantity:=1;
l_mmli_tbl(1).secondary_transaction_quantity:=22;


l_mmli_tbl(1).last_update_date:=SYSDATE;
l_mmli_tbl(1).last_updated_by:=fnd_global.user_id;
l_mmli_tbl(1).creation_date:=SYSDATE;
l_mmli_tbl(1).created_by:=fnd_global.user_id;
gme_api_pub.create_material_txn(p_api_version=> 2.0 ,
p_validation_level=> 1000 ,
p_init_msg_list=> fnd_api.g_true ,
p_commit=> fnd_api.g_true ,
x_message_count=>x_message_count,
x_message_list=>x_message_list,
x_return_status=>x_return_status,
p_org_code=>'VCP' ,
p_mmti_rec=>l_mmti_rec ,
p_mmli_tbl=>l_mmli_tbl,
p_batch_no=>3000100 ,
p_line_no=>1 ,
p_line_type=>1 ,
p_create_lot=>'T' ,
p_generate_lot=>'F' ,
p_generate_parent_lot=>'F' ,
x_mmt_rec=>x_mmt_rec,
x_mmln_tbl=>x_mmln_tbl);
DBMS_OUTPUT.put_line ('status: '|| x_return_status ||' msg Count '||x_message_count);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'status: '|| x_return_status ||'msg Count '|| x_message_count);
IFx_return_status= 'S' THEN
COMMIT;
DBMS_OUTPUT.put_line ('success!!');
ELSE IFx_message_count= 1 THEN
DBMS_OUTPUT.put_line('Error:'|| x_message_list); FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error:'|| x_message_list);
ELSE
DBMS_OUTPUT.put_line ( 'status: '|| x_return_status||' Error Count '|| x_message_count );
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'status: '|| x_return_status||' Error Count '|| x_message_count );
DBMS_OUTPUT.put_line ('Error: '||SUBSTR (x_message_list, 1, 255));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Error: '||SUBSTR (x_message_list, 1,255));
END IF;
END IF;
exception when others then null;
end ;


============================================================================================

OPM Batch - Release and Unrelease


OPM Batch Release :

The  Release_Batch API checks  for the validity of the batch validity rule, and ingredient reservations to create transactions. It also checks for unexploded phantoms, except for those that are Automatic by Step with a step association. The Batch status is set to work in process.

API: GME_API_PUB.RELEASE_BATCH

Sample Code:

create or replace PROCEDURE release_batch (p_batch_id IN NUMBER, p_run_seq_id IN NUMBER)
   IS
      lr_batch_header             gme_batch_header%ROWTYPE;
      lt_exception_material_tbl   gme_common_pvt.exceptions_tab;
      x_return_status             VARCHAR2 (10);
      x_batch_header              gme_batch_header%ROWTYPE;
      ld_st_date                  DATE;
      ld_com_date                 DATE;
      ln_msg_index_out            NUMBER;
      ln_batch_no                 NUMBER;
      ln_rule_id                  NUMBER;
      ln_org_id                   NUMBER;
      lc_org_code                 VARCHAR2 (5);
      lc_status                   VARCHAR2 (5);
      lc_message_list             VARCHAR2 (2000);
      ln_step_no                  NUMBER;
      x_message_count             NUMBER;
      x_message_list              VARCHAR2 (2000);
   BEGIN
      fnd_global.apps_initialize (user_id           => gn_user_id,
                                  resp_id           => gn_resp_id,
                                  resp_appl_id      => gn_prog_appl_id
                                 );

      BEGIN
         SELECT plan_start_date, plan_cmplt_date, batch_no,
                recipe_validity_rule_id, organization_id
           INTO ld_st_date, ld_com_date, ln_batch_no,
                ln_rule_id, ln_org_id
           FROM gme_batch_header
          WHERE batch_id = p_batch_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (   'Error In Selecting Batch Mat Details'
                                  || SQLERRM
                                 );
      END;

      IF ln_org_id IS NOT NULL
      THEN
         SELECT organization_code
           INTO lc_org_code
           FROM org_organization_definitions
          WHERE organization_id = ln_org_id;
      END IF;

      lr_batch_header.batch_type := 0;
      lr_batch_header.batch_no := ln_batch_no;
      lr_batch_header.plan_start_date := ld_st_date;
      lr_batch_header.plan_cmplt_date := ld_com_date;
      lr_batch_header.update_inventory_ind := 'Y';
      lr_batch_header.recipe_validity_rule_id := ln_rule_id;
      lr_batch_header.actual_start_date := SYSDATE;
      gme_api_pub.release_batch
                        (p_api_version                 => 2.0,
                         p_validation_level            => 100,
                         p_init_msg_list               => fnd_api.g_false,
                         p_commit                      => fnd_api.g_false,
                         x_message_count               => x_message_count,
                         x_message_list                => x_message_list,
                         x_return_status               => x_return_status,
                         p_batch_header_rec            => lr_batch_header,
                         p_org_code                    => lc_org_code,
                         p_ignore_exception            => 'T',
                         p_validate_flexfields         => fnd_api.g_false,
                         x_batch_header_rec            => x_batch_header,
                         x_exception_material_tbl      => lt_exception_material_tbl
                        );
      COMMIT;

      IF x_return_status = fnd_api.g_ret_sts_success
      THEN
         DBMS_OUTPUT.put_line ('Batch Released');
       
      ELSE
         DBMS_OUTPUT.put_line ('Batch Release failed');

         IF x_message_count = 1
         THEN
            DBMS_OUTPUT.put_line ('Error:' || x_message_list);
         ELSE
            FOR i IN 1 .. x_message_count
            LOOP
               fnd_msg_pub.get (p_msg_index          => i,
                                p_data               => x_message_list,
                                p_msg_index_out      => ln_msg_index_out
                               );
               DBMS_OUTPUT.put_line ('Error: ' || x_message_list);
            END LOOP;
         END IF;

               END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Error ' || TO_CHAR (SQLCODE) || ': '
                               || SQLERRM
                              );
   END;

OPM Batch Unrelease :
==============================================

Once a batch is released, the status will be reversed from Released to Pending.
The completed transactions are reversed, actual values are reset to zero, and inventory is updated.

API: GME_API_PUB.UNRELEASE_BATCH

Sample Code:

create or replace PROCEDURE unrelease_batch (p_batch_id IN NUMBER, p_run_seq_id IN NUMBER)
   IS
      lr_batch_header             gme_batch_header%ROWTYPE;
      lt_exception_material_tbl   gme_common_pvt.exceptions_tab;
      x_return_status             VARCHAR2 (10);
      x_batch_header              gme_batch_header%ROWTYPE;
      ln_msg_index_out            NUMBER;
      ln_batch_no                 NUMBER;
      lc_org_code                 VARCHAR2 (5);
      lc_status                   VARCHAR2 (5);
      lc_message_list             VARCHAR2 (2000);
      x_message_count             NUMBER;
      x_message_list              VARCHAR2 (2000);
      i                           NUMBER;
   BEGIN
      fnd_global.apps_initialize (user_id           => gn_user_id,
                                  resp_id           => gn_resp_id,
                                  resp_appl_id      => gn_prog_appl_id
                                 );
      lr_batch_header.batch_id := p_batch_id;

      BEGIN
         SELECT organization_code
           INTO lc_org_code
           FROM org_organization_definitions
          WHERE organization_id = (SELECT organization_id
                                     FROM gme_batch_header
                                    WHERE batch_id = p_batch_id);
      EXCEPTION
         WHEN OTHERS
         THEN
            lc_org_code := NULL;
      END;

      gme_api_pub.unrelease_batch (p_api_version                => 2.0,
                                   p_validation_level           => 100,
                                   p_init_msg_list              => fnd_api.g_false,
                                   p_commit                     => fnd_api.g_false,
                                   p_save_batch                 => fnd_api.g_false,
                                   x_message_count              => x_message_count,
                                   x_message_list               => lc_message_list,
                                   x_return_status              => x_return_status,
                                   p_batch_header_rec           => lr_batch_header,
                                   p_org_code                   => lc_org_code,
                                   p_create_resv_pend_lots      => 1, -- Recreate reservations- NO
                                   p_continue_lpn_txn           => 'N',
                                   x_batch_header_rec           => x_batch_header
                                  );
      COMMIT;

      IF x_return_status = fnd_api.g_ret_sts_success
      THEN
         DBMS_OUTPUT.put_line ('Batch Unreleased' || x_message_list);
      ELSE
         IF x_message_count = 1
         THEN
            DBMS_OUTPUT.put_line ('Error' || lc_message_list);
         ELSE
            FOR i IN 1 .. x_message_count
            LOOP
               fnd_msg_pub.get (p_msg_index          => i,
                                p_data               => x_message_list,
                                p_msg_index_out      => ln_msg_index_out
                               );
            END LOOP;
         
            COMMIT;
         END IF;
      END IF;
   END;

======================================================================================