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;

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

MTL ITEM RESERVATIONS QUERY

==========     MTL ITEM RESERVATIONS QUERY  ===========

select * from MTL_RESERVATIONS

select * from mtl_reservations where organization_id= 2035

select * from mtl_system_items_b bb where bb.INVENTORY_ITEM_ID = 133109 and bb.ORGANIZATION_ID = 10--


select *  from mtl_reservations where organization_id = 2035 and INVENTORY_ITEM_ID = 133109 and RESERVATION_ID=1345--

select * from MTL_DEMAND

SELECT o.organization_code, o.organization_name, i.segment1, i.description,
       r.reservation_quantity, orig_demand_source_header_id,
       supply_source_type_id
  FROM mtl_reservations r,
       org_organization_definitions o,
       mtl_system_items_b i
 WHERE r.inventory_item_id = 396964  --132940
   AND r.organization_id = o.organization_id
   AND r.organization_id = i.organization_id
   AND r.inventory_item_id = i.inventory_item_id

----------DEL---RES--TB--

SELECT *
  FROM gme_batch_header b
 WHERE b.batch_id = 1349161 --2052297



SELECT *
  FROM mtl_material_transactions_temp t
 WHERE t.inventory_item_id = 396964 --132940

 select * from MTL_RESERVATIONS_INTERFACE ri
where ri.INVENTORY_ITEM_ID = 396964


select * from mtl_transactions_interface rt
where rt.INVENTORY_ITEM_ID = 396964


select *  from mtl_reservations where organization_id = 2035 and INVENTORY_ITEM_ID = 133109 and RESERVATION_ID=134598531

Cost Finding SQL for Oracle Apps R12

=========  Cost Finding SQL for Oracle Apps R12  ===================


select ccd.PERIOD_ID,ccd.CMPNT_COST
from  cm_cmpt_dtl ccd,mtl_system_items_b msi
where msi.INVENTORY_ITEM_ID = ccd.INVENTORY_ITEM_ID
and ccd.ORGANIZATION_ID = msi.ORGANIZATION_ID
and msi.ORGANIZATION_ID = :A01
and ccd.PERIOD_ID = :PERIOD_ID2

select MAX(PERIOD_ID)TOP1 from GMF_PERIOD_STATUSES
select MAX(PERIOD_ID) TOP2 from GMF_PERIOD_STATUSES order by PERIOD_ID

SELECT MAX( col )
  FROM table
 WHERE col < ( SELECT MAX( col )
                 FROM table )
               
 select top 1 col
from (
    select top 2 col
    from [table]
    order by col) topTwo
order by col desc


select MAX(PERIOD_ID) TOP1 from (select MAX(PERIOD_ID) TOP2 from GMF_PERIOD_STATUSES order by PERIOD_ID ) TOP2 order by PERIOD_ID desc

select *
from(
select MAX(PERIOD_ID) TOP1 From GMF_PERIOD_STATUSES ,
select MAX(PERIOD_ID) From GMF_PERIOD_STATUSES
where PERIOD_ID <(select MAX(PERIOD_ID) TOP2from GMF_PERIOD_STATUSES) ,
(select MAX(PERIOD_ID)  TOP3 From GMF_PERIOD_STATUSES
where PERIOD_ID <(select MAX(PERIOD_ID) from GMF_PERIOD_STATUSES where PERIOD_ID <(select MAX(PERIOD_ID) from GMF_PERIOD_STATUSES)))))

(select * from

(

select MAX(PERIOD_ID)  TOP3 From GMF_PERIOD_STATUSES

where PERIOD_ID <(select MAX(PERIOD_ID) TOP2 from GMF_PERIOD_STATUSES where PERIOD_ID <(select MAX(PERIOD_ID) TOP1 from GMF_PERIOD_STATUSES))

select TOP2,ccd.CMPNT_COST from

(select MAX(PERIOD_ID) TOP2 from GMF_PERIOD_STATUSES 

where PERIOD_ID <(select MAX(PERIOD_ID) TOP1 from GMF_PERIOD_STATUSES))BB,cm_cmpt_dtl ccd

where ccd.PERIOD_ID = TOP2 

select ccd.CMPNT_COST from

(

select MAX(GPS.PERIOD_ID) TOP2, ccd.CMPNT_COST from GMF_PERIOD_STATUSES GPS,cm_cmpt_dtl ccd 

where GPS.PERIOD_ID <(select MAX(PERIOD_ID) TOP1 from GMF_PERIOD_STATUSES)

and ccd.PERIOD_ID = GPS.PERIOD_ID

group by GPS.PERIOD_ID,ccd.CMPNT_COST

where ccd.PERIOD_ID = TOP2 

)) TOP

Cost Comp for OPM Organization

===== Cost Comp for OPM Organization ==========

Select OU_NAME,OU_UNIT,ORG_CODE,ORG_NAME,Item_COED,description,UOM,ITEM_TYPE,PERIOD_CODE, pvs_mth2,pvs_mth1,cur_mth,
(cur_mth - pvs_mth1) Diff,
TO_CHAR(ROUND ((  (  (cur_mth - pvs_mth1)
                    / (CASE
                          WHEN (cur_mth) = 0
                             THEN 1
                          ELSE (cur_mth)
                       END
                      )
                   )
                 * 100
                ),
                6
               )) PP_VR
from
(SELECT ood.OPERATING_UNIT OU_UNIT, hr.NAME OU_NAME,msib.segment1 Item_COED, msib.description,msib.primary_uom_code UOM,
msib.ITEM_TYPE, mp.organization_code ORG_CODE,
       ood.organization_name ORG_NAME,ps.PERIOD_ID,ps.PERIOD_CODE,
     
       trunc(xxprg_item_cost1 (msib.organization_id,
                  msib.inventory_item_id,
                    (SELECT ps.PERIOD_ID FROM GMF_PERIOD_STATUSES PS WHERE TRUNC(END_DATE) = (
                    LAST_DAY(ADD_MONTHS((SELECT TRUNC(START_DATE) FROM GMF_PERIOD_STATUSES PS WHERE PERIOD_ID=:P_PERIOD_ID),-1))))
                 ),4) pvs_mth1,
        trunc(xxprg_item_cost1 (msib.organization_id,
                  msib.inventory_item_id,
                  ps.PERIOD_ID
                 ),4)cur_mth
  FROM mtl_system_items_b msib,
       mtl_parameters mp,
       org_organization_definitions ood,
      HR_OPERATING_UNITS hr,
       GMF_PERIOD_STATUSES PS
 WHERE ood.organization_id = mp.organization_id
   AND msib.organization_id = mp.organization_id
   --AND ood.BUSINESS_GROUP_ID = BU.BUSINESS_GROUP_ID
   AND ood.operating_unit = hr.ORGANIZATION_ID
   AND ood.operating_unit = :P_OU_ID
   -----AND ps.PERIOD_ID = :P_PERIOD_ID-----
   ---and ps.PERIOD_CODE =:P_PERIOD_NAME
  ---- and msib.ITEM_TYPE in----
   ---AND mp.process_enabled_flag -------
   --AND mp.organization_id <> 102
   AND EXISTS (
          SELECT 1
            FROM mtl_material_transactions
           WHERE organization_id = mp.organization_id
             AND inventory_item_id = msib.inventory_item_id
          AND TRUNC(TRANSACTION_DATE) BETWEEN TO_DATE(TO_CHAR(PS.START_DATE,'dd-mon-yyyy HH24:MI:SS' ), 'dd-mon-yyyy HH24:MI:SS') AND 
                TO_DATE(TO_CHAR(PS.END_DATE,'dd-mon-yyyy HH24:MI:SS' ), 'dd-mon-yyyy HH24:MI:SS'))
  order by  ood.organization_name)
  group by OU_UNIT,OU_NAME,Item_COED,description,UOM,ITEM_TYPE,ORG_CODE,ORG_NAME,PERIOD_CODE,pvs_mth2,pvs_mth1,cur_mth
  order by ORG_NAME ASC