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
-
VATABLE
-
EXCISEABLE
-
RECOVERABLE (CLAIMABLE)
-
ITEM CLASS ENABLED
The above all condition satisfied we can proceed to procure material or sell the material other wise give the note message for warning but it should not stop any transaction.
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
Message Text :
= ‘THIS ‘ || :LINE.ORDERED_ITEM_DSP || ‘ ITEM EXCISE OR VAT NOT DEFINED PLS CHECK.’
PELEXVAT (Procedure)
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;
/
Expected output form Purchase Order form
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
PELEXITEMVAT (Procedure)
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;
/
Recent Comments