Form Personalization from Sales Order form and Purchase Order form aganist the Item

Form Personalization for Purchase Order and Sales Order while choose Item from procure material from Purchase order form or sell the Material from Sales Order form at the time we should know the that particular material is

Refer the screen shots below…

Expected output form Sales Order form

Form personalization

Nav:Open the form and choose Object trigger value → Help → Diagnostics → Custom code → Personalize

Condition :

PELEXVAT(${item.LINE.ORDERED_ITEM_DSP.value},${item.LINE.ship_from_org_id.value})=2

 

CREATE OR REPLACE FUNCTION APPS.PELEXVAT(ITEM_CODE VARCHAR2,SHIP_ORG_ID NUMBER) RETURN Number 

IS 

FLAG Number;

BEGIN

Select (Case When Flag=0 Then 1 Else 2 End) as Flag Into Flag From(

Select Abs(Sum(F1+F2)-Sum(F3+F4)) Flag From(

Select (Case When Sum(F1)<>0 Then Sum(F1) Else 3 End ) as F1,

(Case When Sum(F2)<>0 Then Sum(F2) Else 4 End) as F2,

(Case When Sum(F3)<>0 Then Sum(F3) Else 5 End)as F3,

(Case When Sum(F4)<>0 Then Sum(F4) Else 6 End) as F4 From (

Select (Case When F1=1 Then Sum(F1) End) as F1,

(Case When F2=1 Then Sum(F2) End) as F2,

(Case When F3=1 Then Sum(F3) End) as F3,

(Case When F4=1 Then Sum(F4) End) as F4 From (

Select Distinct ( Case When ATTRIBUTE_CODE=’EXCISABLE’ And ATTRIBUTE_VALUE=’Y’ Then 1 Else 5 End) as F1,

( Case When ATTRIBUTE_CODE=’MODVATABLE’ And ATTRIBUTE_VALUE=’Y’ Then 1 Else 4 End) as F2,

( Case When ATTRIBUTE_CODE=’RECOVERABLE’ And ATTRIBUTE_VALUE=’Y’ Then 1 Else 4 End) as F3,

( Case When ATTRIBUTE_CODE=’APPLICABLE’ And ATTRIBUTE_VALUE=’Y’ Then 1 Else 11 End) as F4

From ( Select ATTRIBUTE_CODE,ATTRIBUTE_VALUE, SEGMENT1,CREATION_DATE,TEMPLATE_ID,templ_org_regns_id,
ORGANIZATION_ID,RGM_ITEM_REGNS_ID
From (

select Distinct A.ATTRIBUTE_CODE,ATTRIBUTE_VALUE, SEGMENT1,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID

From JAI_RGM_ITM_TMPL_ATTRS a,

jai_rgm_tmpl_itm_regns b,

jai_rgm_tmpl_org_regns c,

Mtl_System_Items_b I

Where b.inventory_item_id =I.INVENTORY_ITEM_ID

And c.templ_org_regns_id = b.templ_org_regns_id

And C.ORGANIZATION_ID=I.ORGANIZATION_ID

and c.template_id = a.template_id

And (ATTRIBUTE_CODE =’EXCISABLE’ ) And SEGMENT1 =ITEM_CODE

And C.ORGANIZATION_ID=SHIP_ORG_ID

Union All

select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID

From JAI_RGM_ITM_TMPL_ATTRS a,

jai_rgm_tmpl_itm_regns b,

jai_rgm_tmpl_org_regns c,

Mtl_System_Items_b I

Where b.inventory_item_id =I.INVENTORY_ITEM_ID

And c.templ_org_regns_id = b.templ_org_regns_id

And C.ORGANIZATION_ID=I.ORGANIZATION_ID

and c.template_id = a.template_id

And (ATTRIBUTE_CODE =’MODVATABLE’ ) And SEGMENT1 =ITEM_CODE

And C.ORGANIZATION_ID=SHIP_ORG_ID

Union All

select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID

From JAI_RGM_ITM_TMPL_ATTRS a,

jai_rgm_tmpl_itm_regns b,

jai_rgm_tmpl_org_regns c,

Mtl_System_Items_b I

Where b.inventory_item_id =I.INVENTORY_ITEM_ID

And c.templ_org_regns_id = b.templ_org_regns_id

And C.ORGANIZATION_ID=I.ORGANIZATION_ID

and c.template_id = a.template_id

And (ATTRIBUTE_CODE =’APPLICABLE’ ) And SEGMENT1 =ITEM_CODE

And C.ORGANIZATION_ID=SHIP_ORG_ID

Union All

select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID

From JAI_RGM_ITM_TMPL_ATTRS a,

jai_rgm_tmpl_itm_regns b,

jai_rgm_tmpl_org_regns c,

Mtl_System_Items_b I

Where b.inventory_item_id =I.INVENTORY_ITEM_ID

And c.templ_org_regns_id = b.templ_org_regns_id

And C.ORGANIZATION_ID=I.ORGANIZATION_ID

and c.template_id = a.template_id

And (ATTRIBUTE_CODE =’RECOVERABLE’ ) And SEGMENT1 =ITEM_CODE

And C.ORGANIZATION_ID=SHIP_ORG_ID

Union All

select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID

From JAI_RGM_ITM_TMPL_ATTRS a,

jai_rgm_tmpl_itm_regns b,

jai_rgm_tmpl_org_regns c,

Mtl_System_Items_b I

Where b.inventory_item_id =I.INVENTORY_ITEM_ID

And c.templ_org_regns_id = b.templ_org_regns_id

And C.ORGANIZATION_ID=I.ORGANIZATION_ID

and c.template_id = a.template_id

And (ATTRIBUTE_CODE =’ITEM CLASS’ ) And SEGMENT1 =ITEM_CODE

And C.ORGANIZATION_ID=SHIP_ORG_ID )))Group By F1,F2,F3,F4)));

DBMS_OUTPUT.Put_Line(Flag);

RETURN FLAG;

END;

/

 

Form personalization

Nav:Open the form and choose Object trigger value → Help → Diagnostics → Custom code → Personalize

Condition :

PELEXITEMVAT(${item.PO_LINES.ITEM_NUMBER.value},

${item.PO_HEADERS.SHIP_TO_ORG_ID.value})<>5

OR

PELEXITEMVAT(${item.PO_LINES.ITEM_NUMBER.value},

${item.PO_HEADERS.SHIP_TO_ORG_ID.value}) =0

CREATE OR REPLACE Function APPS.PELEXITEMVAT(ITEM_CODE VARCHAR2,SHIP_ORG_ID NUMBER) RETURN Number

IS

FLAG Number;

BEGIN

Select (Case When Flag Is Null Then 0 Else Flag End ) as Flag Into Flag From (

Select Sum(F1)+Sum(F2)+SUM(F3)+SUM(F4)+SUM(F5) as Flag From (

Select (Case When F1=1 Then Sum(F1) End) as F1,

(Case When F2=1 Then Sum(F2) End) as F2,

(Case When F3=1 Then Sum(F3) End) as F3,

(Case When F4=1 Then Sum(F4) End) as F4,

(Case When F5=1 Then Sum(F5) End) as F5 From (

Select Distinct ( Case When ATTRIBUTE_CODE=’EXCISABLE’ Then 1 Else 5 End) as F1,

( Case When ATTRIBUTE_CODE=’MODVATABLE’ Then 1 Else 4 End) as F2,

( Case When ATTRIBUTE_CODE=’RECOVERABLE’ Then 1 Else 4 End) as F3,

( Case When ATTRIBUTE_CODE=’APPLICABLE’ Then 1 Else 11 End) as F4,

( Case When ATTRIBUTE_CODE=’ITEM’ Then 1 Else 11 End) as F5

From (

Select ATTRIBUTE_CODE,ATTRIBUTE_VALUE, SEGMENT1,CREATION_DATE,TEMPLATE_ID,
templ_org_regns_id,ORGANIZATION_ID,RGM_ITEM_REGNS_ID
From (

select Distinct A.ATTRIBUTE_CODE,ATTRIBUTE_VALUE, SEGMENT1,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID

From JAI_RGM_ITM_TMPL_ATTRS a,

jai_rgm_tmpl_itm_regns b,

jai_rgm_tmpl_org_regns c,

Mtl_System_Items_b I

Where b.inventory_item_id =I.INVENTORY_ITEM_ID

And c.templ_org_regns_id = b.templ_org_regns_id

And C.ORGANIZATION_ID=I.ORGANIZATION_ID

and c.template_id = a.template_id

And (ATTRIBUTE_CODE =’EXCISABLE’ ) And SEGMENT1 =ITEM_CODE

And C.ORGANIZATION_ID=SHIP_ORG_ID

Union All

select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID

From JAI_RGM_ITM_TMPL_ATTRS a,

jai_rgm_tmpl_itm_regns b,

jai_rgm_tmpl_org_regns c,

Mtl_System_Items_b I

Where b.inventory_item_id =I.INVENTORY_ITEM_ID

And c.templ_org_regns_id = b.templ_org_regns_id

And C.ORGANIZATION_ID=I.ORGANIZATION_ID

and c.template_id = a.template_id

And (ATTRIBUTE_CODE =’MODVATABLE’ ) And SEGMENT1 =ITEM_CODE

And C.ORGANIZATION_ID=SHIP_ORG_ID

Union All

select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID

From JAI_RGM_ITM_TMPL_ATTRS a,

jai_rgm_tmpl_itm_regns b,

jai_rgm_tmpl_org_regns c,

Mtl_System_Items_b I

Where b.inventory_item_id =I.INVENTORY_ITEM_ID

And c.templ_org_regns_id = b.templ_org_regns_id

And C.ORGANIZATION_ID=I.ORGANIZATION_ID

and c.template_id = a.template_id

And (ATTRIBUTE_CODE =’APPLICABLE’ ) And SEGMENT1 =ITEM_CODE

And C.ORGANIZATION_ID=SHIP_ORG_ID

Union All

select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID

From JAI_RGM_ITM_TMPL_ATTRS a,

jai_rgm_tmpl_itm_regns b,

jai_rgm_tmpl_org_regns c,

Mtl_System_Items_b I

Where b.inventory_item_id =I.INVENTORY_ITEM_ID

And c.templ_org_regns_id = b.templ_org_regns_id

And C.ORGANIZATION_ID=I.ORGANIZATION_ID

and c.template_id = a.template_id

And (ATTRIBUTE_CODE =’RECOVERABLE’ ) And SEGMENT1 =ITEM_CODE

And C.ORGANIZATION_ID=SHIP_ORG_ID

Union All

select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID

From JAI_RGM_ITM_TMPL_ATTRS a,

jai_rgm_tmpl_itm_regns b,

jai_rgm_tmpl_org_regns c,

Mtl_System_Items_b I

Where b.inventory_item_id =I.INVENTORY_ITEM_ID

And c.templ_org_regns_id = b.templ_org_regns_id

And C.ORGANIZATION_ID=I.ORGANIZATION_ID

and c.template_id = a.template_id

And (ATTRIBUTE_CODE =’ITEM CLASS’ ) And SEGMENT1 =ITEM_CODE

And C.ORGANIZATION_ID=SHIP_ORG_ID

Union All

select Distinct ‘ITEM’ as ATTRIBUTE_CODE, ‘ITEM’ as ATTRIBUTE_VALUE, B.SEGMENT1 ,Null as CREATION_DATE,0 as TEMPLATE_ID,0 as templ_org_regns_id,B.ORGANIZATION_ID,0 as RGM_ITEM_REGNS_ID

From jai_rgm_tmpl_itm_regns A,Mtl_System_Items_B B

Where A.INVENTORY_ITEM_ID=B.INVENTORY_ITEM_ID And

B.SEGMENT1 =ITEM_CODE

And B.ORGANIZATION_ID=SHIP_ORG_ID )))Group By F1,F2,F3,F4,F5));

DBMS_OUTPUT.Put_Line(Flag);

RETURN FLAG;

END;

/