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