Oracle EBS (ERP): Personalizations

Showing posts with label Personalizations. Show all posts

Oracle R12 AR Receipt Form Personalization on DFF

---TCS U/S 206c(1H) on AR receipt Form Personalization DFF Base on Customer.

Step 1:

Condition: WHEN-NEW-ITEM-INSTANCE

Actions:

Update Value: JA.IN.ARXRWMAI.CASH_RECEIPTS

Update Value: N

Step 2:

Condition: WHEN-NEW-ITEM-INSTANCE

Actions:

Update Value: JA.IN.ARXRWMAI.CASH_RECEIPTS

Update Value: Y

Oracle form personalization Customer PO in Sales Order Exists or Not

Form personalization through validate the duplicate Customer PO number in Sales Order Form.

Open the oracle personalization form:

Trigger Event: WHEN-VALIDATE-RECORD

Trigger Object: ORDER

Enter the Condition:

(SELECT COUNT(*) FROM oe_order_headers_all ooh
WHERE ooh.cust_po_number = ${item.order.cust_po_number.value})>1

Action Tab:
Message Type: Error
Message Text: Custom message

Personalizations: Calling to Procedure

Help ->Diagnostics -> Custom Code -> Personalizations: Execute a Procedure

PROCEDURE XXBEX_BULK_BATCH_DFF_P2(P_BATCH_NO VARCHAR2)

--PRAGMA AUTONOMOUS_TRANSATION;

lv_batch_no VARCHAR2(32) := NULL;

lv_organization_id NUMBER;

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

-- ** XXBEX_BULK_BATCH_DFF_P: If FG Batch NO is showing batch Deatils Form **--

-- ** attribute6 is Bulk Batch Number ** --

-- ** Batch Detail Form : XXBEX_BULK_BATCH_NO_TBL ** --

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

CURSOR CUR_BULK

SELECT ATTRIBUTE6 BATCH_NO

,DECODE(gbh.BATCH_STATUS,1,'Pending',2,'WIP',3,'Completed',4,'Closed','Cancelled') STATUS

FROM GME_BATCH_HEADER gbh

WHERE rownum=1

AND ATTRIBUTE6 IS NOT NULL

AND gbh.BATCH_STATUS IN ('1','2','3')

--AND recipe_no = P_RECIPE_NO

AND BATCH_NO = P_BATCH_NO

FOR UPDATE;

BEGIN

SELECT attribute6,organization_id

INTO lv_batch_no ,lv_organization_id

FROM GME_BATCH_HEADER gbh

WHERE rownum=1

AND ATTRIBUTE6 IS NOT NULL

AND gbh.BATCH_STATUS IN ('1','2','3')

--AND recipe_no = P_RECIPE_NO

AND BATCH_NO = P_BATCH_NO;

BEGIN

--DELETE FROM XXBEX_BULK_BATCH_NO_TBL;

-- COMMIT;

---lv_recipe_no := P_RECIPE_NO;

--DBMS_OUTPUT.PUT_LINE ('RECIPE_NO: '||P_RECIPE_NO );

DBMS_OUTPUT.PUT_LINE ('~FG BATCH NO: '||P_BATCH_NO);

IF lv_batch_no IS NOT NULL

THEN

FOR CUR_B IN CUR_BULK

LOOP

INSERT INTO XXBEX_BULK_BATCH_NO_TBL

(BATCH_NO

,STATUS)

VALUES (

CUR_B.BATCH_NO,

CUR_B.STATUS

--CUR_B.ORDER_NUMBER,

/* NULL,-- ATTRIBUTE1,

NULL,-- ATTRIBUTE2,

NULL,-- ATTRIBUTE3,

NULL,-- ATTRIBUTE4

NULL,-- ATTRIBUTE5,

NULL,-- CUR_TRX_NO_NULL_REC.CREATED_BY,

NULL,-- CUR_TRX_NO_NULL_REC.CREATION_DATE,

NULL,-- CUR_TRX_NO_NULL_REC.LAST_UPDATE_DATE,

NULL,-- CUR_TRX_NO_NULL_REC.LAST_UPDATED_BY,

NULL*/ -- CUR_TRX_NO_NULL_REC.LAST_UPDATE_LOGIN

);

DBMS_OUTPUT.PUT_LINE ('2.BATCH_NO : '||CUR_B.BATCH_NO );

--COMMIT;

END LOOP;

ELSE

UPDATE XXBEX_BULK_BATCH_NO_TBL

SET ORGANIZATION_ID = lv_organization_id;

--WHERE

--COMMIT;

END IF;

--COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE('ERROR'||SQLERRM);

--p_status := ('ERROR'||SQLERRM);

NULL;

END;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE('MAIN ERROR'||SQLERRM);

--p_status := ('ERROR'||SQLERRM);

NULL;

END XXBEX_BULK_BATCH_DFF_P2;

END XXBEX_BULK_BATCH_DFF_PKG;

Oracle EBS Form Personalization Query

Form Personzalization in Oracle:

SELECT fat.application_name, ffc.function_name, fff.user_function_name,
fff.description function_description, ffc.form_name,
fft.user_form_name, fft.description form_description, ffc.SEQUENCE,
ffc.description, trigger_event, trigger_object, condition,
ffc.enabled, ffca.SEQUENCE line_sequence, argument_type, action_type,
ffca.enabled line_enabled, object_type, ffca.MESSAGE_TYPE,
ffca.MESSAGE_TEXT, ffca.builtin_type, ffca.property_name,
ffca.menu_entry, ffca.menu_label, menu_action,
ffca.menu_argument_long, ffca.menu_argument_short
FROM fnd_form_custom_rules ffc,
fnd_form_custom_actions ffca,
fnd_form_functions_vl fff,
fnd_form_tl fft,
fnd_application_tl fat
WHERE ffc.ID = ffca.rule_id
AND ffc.form_name = NVL (:p_form_name, ffc.form_name)
AND ffc.description = NVL (:p_forms_personalization, ffc.description)
AND ffc.function_name = fff.function_name
AND fff.form_id = fft.form_id
AND fat.application_id = fff.application_id
AND ffc.form_name LIKE 'GME%'
ORDER BY fat.application_name, ffc.form_name, ffc.SEQUENCE

How to Call Function from Forms Personalization

Step 1: Create Database Function

CREATE OR REPLACE FUNCTION APPS.XXBEX_GEN_BATCH_NUM

(

p_organization IN VARCHAR2

,p_item IN VARCHAR2

,p_order_number IN VARCHAR2

,p_parent_batch IN VARCHAR2

,p_pack_number IN VARCHAR2

)

RETURN VARCHAR2

Step 2: Open Forms Personalization

Step 3: Forms Personalization - Action

To call the Function