PACKAGE BODY: APPS.GRP_DISCOUNTS

Source

1 PACKAGE BODY GRP_DISCOUNTS as
2 /* $Header: OEXGRPPB.pls 115.1 99/08/05 15:05:14 porting ship  $ */
3 
4 Function Check_item_category (Inv_Item_Id  In Number,ENT_VAL in VARCHAR2, OrgId In Number)
5          Return VARCHAR2 IS
6          dummy   Varchar2(1) := 'X';
7          CURSOR C_Check_item_category_1 IS
8                  SELECT 'Y'
9                  FROM  mtl_default_category_sets MTDCS
10 			  , mtl_category_set_valid_cats MCSV
11 			  , mtl_categories MC
12 			  ,MTL_ITEM_CATEGORIES MTC
13                  WHERE  MTDCS.functional_area_id = 7
14                  AND    MTC.category_set_id = MTDCS.category_set_id
15                  AND    MTC.INVENTORY_ITEM_ID = TO_CHAR( Inv_Item_Id )
16 			  AND    MCSV.category_set_id = MTC.category_set_id
17 			  AND    MCSV.category_id = MTC.category_id
18 			  AND    MCSV.category_id = MC.category_id
19 			  AND    sysdate < nvl(MC.disable_date, sysdate+1)
20                  AND    MTC.CATEGORY_ID = to_number(ENT_VAL);
21 
22 
23          CURSOR C_Check_item_category_2 IS
24                  SELECT 'Y'
25                  FROM mtl_default_category_sets MTDCS
26 			  , mtl_category_set_valid_cats MCSV
27 			  , mtl_categories MC
28                  	  , MTL_ITEM_CATEGORIES MTC
29                  WHERE  MTDCS.functional_area_id = 7
30                  AND    MTC.category_set_id = MTDCS.category_set_id
31                  AND    MTC.INVENTORY_ITEM_ID = TO_CHAR( Inv_Item_Id )
32 			  AND    MCSV.category_set_id = MTC.category_set_id
33 			  AND    MCSV.category_id = MTC.category_id
34 			  AND    MCSV.category_id = MC.category_id
35 			  AND    sysdate < nvl(MC.disable_date, sysdate+1)
36                  AND    MTC.ORGANIZATION_ID = OrgId
37                  AND    MTC.CATEGORY_ID = to_number(ENT_VAL);
38 
39 
40 
41     Begin
42 	If OrgId Is Null Then
43           Open C_Check_item_category_1;
44           FETCH C_Check_item_category_1
45                into dummy;
46           close C_Check_item_category_1;
47           if dummy = 'Y' then
48                 RETURN('Y');
49           else
50                RETURN('N');
51           end if;
52 	Else
53           Open C_Check_item_category_2;
54           FETCH C_Check_item_category_2
55                into dummy;
56           close C_Check_item_category_2;
57           if dummy = 'Y' then
58                 RETURN('Y');
59           else
60                RETURN('N');
61           end if;
62 	End If;
63   Exception
64    When Others then return('N');
65    End;
66 
67 END GRP_DISCOUNTS;