Tuesday, 9 August 2016

How to do CREATE AMENDMENT in Oracle apps Custom Form R12 EBS.

How to do CREATE AMENDMENT  in Oracle apps Custom Form R12 EBS.
 


 

In this form we use the Amendment  feature .
Amendment is actually create the same data in the form with new Header ID.
Here we use 3 different table so that we use 3 table columns.
We create the Form level Procedure to create Amendment. Using Program Unit 





---Code is Here : ---
PROCEDURE CREATE_AMENDMENT IS
LC_USER_ID         NUMBER;
LV_BLK_HEADER_ID NUMBER;

BEGIN
LC_USER_ID :=FND_PROFILE.VALUE('USER_ID');
  Declare
                    --         lv_version_number number;
                   
  Begin
                      /*     select nvl(max(VERSION_NUMBER),0)+1 into lv_version_number
                                from XXKNIT_COST_HDR_TBL
                                where CUSTOMER_NAME = :XXKNIT_COST_HDR_TBL.CUSTOMER_NAME
                                and   CUSTOMER_PO = :XXKNIT_COST_HDR_TBL.CUSTOMER_PO
                                and   ORG_ID = :XXKNIT_COST_HDR_TBL.ORG_ID
                                and   ORGANIZATION_ID = :XXKNIT_COST_HDR_TBL.ORGANIZATION_ID;
                     */
                Begin
                  ----------------Generate the Header ID from Using MAX -------------
                Declare
                L_MAX_BLKLST_HEADER_ID       Number := 0;
                Begin

                                Begin
                                SELECT NVL(MAX (BLKLST_HEADER_ID),0)
                                INTO L_MAX_BLKLST_HEADER_ID
                                FROM XXBEX_BLKLST_HEADER;

                                Exception When Others Then
                                Fnd_Message.Debug('--ERROR(01)--(BLOCK/TRIGGER - XXBEX_BLKLST_HEADER/PRE-INSERT)--11111111'||SQLERRM);
                                End;
                                                                                                                                               
                                Begin
                                :XXBEX_BLKLST_HEADER.BLKLST_HEADER_ID := (L_MAX_BLKLST_HEADER_ID+1);
                                End;
                                                                                                 
                End;
                -----------------Insert Data Into The Custom Table XXBEX_BLKLST_HEADER  --------------

                                INSERT INTO XXBEX_BLKLST_HEADER VALUES(
                                                :XXBEX_BLKLST_HEADER.BLKLST_HEADER_ID,
                                                :XXBEX_BLKLST_HEADER.NO_DA,
                                                :XXBEX_BLKLST_HEADER.NO_DA_DATE,
                                                :XXBEX_BLKLST_HEADER.IPO_NO,
                                                :XXBEX_BLKLST_HEADER.BLOCK_LIST_NO,
                                                :XXBEX_BLKLST_HEADER.BLOCK_LIST_NO_DATE,
                                                :XXBEX_BLKLST_HEADER.STATUS,
                                                :XXBEX_BLKLST_HEADER.FIRM_NAME,
                                                :XXBEX_BLKLST_HEADER.FIRM_ADDRESS,
                                                :XXBEX_BLKLST_HEADER.FACTORY_ADDRESS,
                                                :XXBEX_BLKLST_HEADER.CCINES_CATEGORY_REGIS,
                                                :XXBEX_BLKLST_HEADER.LAST_YEAR_TOT_CNF_IMP_TK,
                                                :XXBEX_BLKLST_HEADER.TYPE_OF_LICENSE,
                                                :XXBEX_BLKLST_HEADER.TOT_VAT_PAID_LAST_YEAR,
                                                :XXBEX_BLKLST_HEADER.INTENDED_PORT_OF_ARRIVAL,
                                                LC_USER_ID,--:XXBEX_BLKLST_HEADER.CREATED_BY,
                                                SYSDATE,--:XXBEX_BLKLST_HEADER.CREATION_DATE,
                                                LC_USER_ID,--:XXBEX_BLKLST_HEADER.LAST_UPDATED_BY,
                                                SYSDATE,--:XXBEX_BLKLST_HEADER.LAST_UPDATE_DATE,
                                                LC_USER_ID,--:XXBEX_BLKLST_HEADER.LAST_UPDATE_LOGIN,
                                                null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE1,
                                                null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE2,
                                                null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE3,
                                                null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE4,
                                                null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE5,
                                                null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE6,
                                                null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE7,
                                                null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE8,
                                                null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE9,
                                                null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE10,
                                                :XXBEX_BLKLST_HEADER.SHIPPING_PERIOD,
                                                :XXBEX_BLKLST_HEADER.AMANDAMENT_YES_NO,
                                                :XXBEX_BLKLST_HEADER.BLOCK_LIST_TYPE,
                                                NULL,--:XXBEX_BLKLST_HEADER.AUTHORITY,
                                                :XXBEX_BLKLST_HEADER.EXPLOSIVE,
                                                :XXBEX_BLKLST_HEADER.NARCOTICS,
                                                :XXBEX_BLKLST_HEADER.DRUGS,
                                                :XXBEX_BLKLST_HEADER.PI_OTHER_REF,
                                                :XXBEX_BLKLST_HEADER.PI_OTHER_REF_DATE,
                                                :Parameter.P_ORG_ID, --:XXBEX_BLKLST_HEADER.ORG_ID,
                                                :Parameter.P_OU_NAME --:XXBEX_BLKLST_HEADER.OPERATING_UNIT_NAME
                                                );
                               
                End;
     
     
    -----------------Insert Data Into The Custom Table XXBEX_FOREIGN_CUR_LINES  --------------
    Begin
            Go_Block('XXBEX_FOREIGN_CUR_LINES');
            First_Record;
            Loop
                INSERT INTO XXBEX_FOREIGN_CUR_LINES VALUES(
                                                :XXBEX_BLKLST_HEADER.BLKLST_HEADER_ID,
                                                :XXBEX_FOREIGN_CUR_LINES.BLKLST_FC_ID,
                                                :XXBEX_FOREIGN_CUR_LINES.FC_CURRENCY,
                                                :XXBEX_FOREIGN_CUR_LINES.FC_EXCHANGE_RATE,
                                                LC_USER_ID,      --:XXBEX_BLKLST_HEADER.CREATED_BY,
                                                SYSDATE,                                             --:XXBEX_BLKLST_HEADER.CREATION_DATE,
                                                LC_USER_ID,      --:XXBEX_BLKLST_HEADER.LAST_UPDATED_BY,
                                                SYSDATE,                                             --:XXBEX_BLKLST_HEADER.LAST_UPDATE_DATE,
                                                LC_USER_ID,      --:XXBEX_BLKLST_HEADER.LAST_UPDATE_LOGIN,
                                                null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE1,
                                                null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE2,
                                                null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE3,
                                                null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE4,
                                                null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE5,
                                                null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE6,
                                                null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE7,
                                                null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE8,
                                                null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE9,
                                                null  --:XXBEX_BLKLST_HEADER.ATTRIBUTE10
                                                                                                                                                                                                                                );
                                                                       
                          Exit When :System.Last_Record = 'TRUE';
                          Next_Record;
            End Loop;
            
                End;
     
    -----------------Insert Data Into The Custom Table XXBEX_BLKLST_LINES  --------------

                Begin
        Go_Block('XXBEX_BLKLST_LINES');
        First_Record;
        Loop
            INSERT INTO XXBEX_BLKLST_LINES VALUES(
                                  :XXBEX_BLKLST_HEADER.BLKLST_HEADER_ID,
                                  :XXBEX_BLKLST_LINES.BLKLST_LINE_ID,
                                  :XXBEX_BLKLST_LINES.SL_NO,
                                  :XXBEX_BLKLST_LINES.ITEM_ID,
                                  :XXBEX_BLKLST_LINES.ITEM_CODE,
                                  :XXBEX_BLKLST_LINES.ITEM_DESC,
                                  :XXBEX_BLKLST_LINES.ORG_ID,
                                  :XXBEX_BLKLST_LINES.INV_ORGANIZATION_ID,
                                  :XXBEX_BLKLST_LINES.NAME_OF_RAW_PACK_MATERIAL,
                                  :XXBEX_BLKLST_LINES.MANUFACTURER_1,
                                  :XXBEX_BLKLST_LINES.MANUFACTURER_2,
                                  :XXBEX_BLKLST_LINES.SUPPLIER_1,
                                  :XXBEX_BLKLST_LINES.SUPPLIER_2,--:XXKNIT_COST_YFDC_LINES_TBL.ORG_ID,
                                  -- :parameter.ORGANIZATION_ID,
                                  -- lv_version_number,
                                  -- 'N',--:XXKNIT_COST_YFDC_LINES_TBL.YFDC_LINE_FLAG,
                                   :XXBEX_BLKLST_LINES.QTY_IN_FIGURES,
                                   :XXBEX_BLKLST_LINES.QTY_UOM,
                                   :XXBEX_BLKLST_LINES.QTY_IN_WORDS,
                                   :XXBEX_BLKLST_LINES.FC_CURRENCY,
                                   :XXBEX_BLKLST_LINES.FC_EXCHANGE_RATE,
                                   :XXBEX_BLKLST_LINES.TAKA_AMOUNT,
                                   :XXBEX_BLKLST_LINES.FINISHED_PRODUCT_NAME,
                                   :XXBEX_BLKLST_LINES.FINISHED_PRODUCT_QTY_LY,
                                   :XXBEX_BLKLST_LINES.PROD_DML_RECIPE_APPROVED,
                                   :XXBEX_BLKLST_LINES.QTY_IMPORTED_LAST_YEAR,
                                   :XXBEX_BLKLST_LINES.UOM_IMPORTED_LAST_YEAR,
                                   :XXBEX_BLKLST_LINES.QTY_APPROVED_THIS_YEAR,                                                                       
                                   :XXBEX_BLKLST_LINES.TOT_QTY_REQD_THIS_YEAR,
                                   :XXBEX_BLKLST_LINES.PERIOD_SHIPPING,
                                  lc_user_id,--:XXBEX_BLKLST_HEADER.CREATED_BY,
                                  SYSDATE,--:XXBEX_BLKLST_HEADER.CREATION_DATE,
                                  lc_user_id,--:XXBEX_BLKLST_HEADER.LAST_UPDATED_BY,
                                  SYSDATE,--:XXBEX_BLKLST_HEADER.LAST_UPDATE_DATE,
                                  lc_user_id,--:XXBEX_BLKLST_HEADER.LAST_UPDATE_LOGIN,
                                  null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE1,
                                 null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE2,
                                 null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE3,
                                 null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE4,
                                 null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE5,
                                 null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE6,
                                 null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE7,
                                 null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE8,
                                 null, --:XXBEX_BLKLST_HEADER.ATTRIBUTE9,
                                 null,  --:XXBEX_BLKLST_HEADER.ATTRIBUTE10
                                 :XXBEX_BLKLST_LINES.EXCHANGE_RATE);
                                                                                                                                                               
       --    :SYSTEM.MESSAGE_LEVEL :=25;
       --    commit;
       --   :SYSTEM.MESSAGE_LEVEL :=0;                           
                                                                       
                          Exit When :System.Last_Record = 'TRUE';
                          Next_Record;
        End Loop;
            
    End;
    LV_BLK_HEADER_ID := :XXBEX_BLKLST_HEADER.BLKLST_HEADER_ID;
   -- fnd_message.debug ('Header id = ' ||LV_BLK_HEADER_ID);
                                 forms_ddl('commit');
                                 clear_form(no_validate);
                                                    
    
                                 go_block('XXBEX_BLKLST_HEADER');
                                 :Global.BLKLST_HEADER_ID := LV_BLK_HEADER_ID ;--(:XXBEX_BLKLST_HEADER.BLKLST_HEADER_ID);
                                 SET_BLOCK_PROPERTY('XXBEX_BLKLST_HEADER',ONETIME_WHERE,'BLKLST_HEADER_ID = :Global.BLKLST_HEADER_ID');
                                 execute_query;

         
    End;
   
END;

--------------------*********************  END of Amendment Code  **********--------------------


After Compile the procedure successfully.
Create a Push Button in the Form to CALL the Created Procedure.
 
   

Write the Code in the Amendment Button :
Using WHEN-BUTTON-PRESSED Trigger :

----------------------------- *********************** --------------------------

Declare
                  l_button          number;
                                lc_exist_po       VARCHAR2(1000);
Begin
               
                               
IF            :XXBEX_BLKLST_HEADER.AMANDAMENT_YES_NO = 'NO'  THEN --IF AMANDAMENT_YES_NO Not changed
                                fnd_message.set_string('Do you want to create Amendment ?');
    l_button := fnd_message.question('YES','CANCEL', 'NO',1, 3,'question');
   -- FND_MESSAGE.DEBUG('Button Pressed# '||l_button);
   
     if l_button = 1 and :XXBEX_BLKLST_HEADER.AMANDAMENT_YES_NO = 'NO' Then
    --         FND_MESSAGE.DEBUG('Button Pressed# '||l_button);
                :XXBEX_BLKLST_HEADER.AMANDAMENT_YES_NO := 'YES';
                fnd_message.set_string('Amendment has been created Successfully..');
                                  fnd_message.show();
                                  
                                   CREATE_AMENDMENT;  --- This is Call the from the Program Unit (Procedure)----
     elsif l_button = 2 then
                --             FND_MESSAGE.DEBUG('Button Pressed# '||l_button);
                 raise form_trigger_failure;        

    END IF;
ELSE
                fnd_message.set_string('You have already created the Amendment.');
                                  fnd_message.show();
                                                --app_special.enable('SAVE', PROPERTY_OFF);
End If;

End;

 -----------------------*     ***************** ---------------------------------------

When the user query the records and want to create the amendment then he click on the

 


Amendment button to create the Amendment .












No comments:

Post a Comment

How to change Employee Number Generation from Manual to Automatic in Oracle HRMS (When attempting to apply for a job in iRecruitment)

When attempting to apply for a job in iRecruitment, the following error occurs: ERROR: You must enter an Application Number. Solution: How t...