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 ENABLEDThe 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 (
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
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
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
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
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
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 (
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
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
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
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
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
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;
/
Nice Ji
ReplyDelete