1 Package BODY PA_CLIENT_EXTEN_CIP_GROUPING AS
2 /* $Header: PAXGCEB.pls 120.5 2007/02/06 09:30:05 rshaik ship $ */
3 FUNCTION CLIENT_GROUPING_METHOD( p_proj_id IN PA_PROJECTS_ALL.project_id%TYPE,
4 p_task_id IN PA_TASKS.task_id%TYPE,
5 p_expnd_item_id IN PA_EXPENDITURE_ITEMS_ALL.expenditure_item_id%TYPE,
6 p_expnd_id IN PA_EXPENDITURE_ITEMS_ALL.expenditure_id%TYPE,
7 p_expnd_type IN PA_EXPENDITURE_TYPES.expenditure_type%TYPE,
8 p_expnd_category IN PA_EXPENDITURE_CATEGORIES.expenditure_category%TYPE,
9 p_attribute1 IN PA_EXPENDITURE_ITEMS_ALL.attribute1%TYPE,
10 p_attribute2 IN PA_EXPENDITURE_ITEMS_ALL.attribute1%TYPE,
11 p_attribute3 IN PA_EXPENDITURE_ITEMS_ALL.attribute1%TYPE,
12 p_attribute4 IN PA_EXPENDITURE_ITEMS_ALL.attribute1%TYPE,
13 p_attribute5 IN PA_EXPENDITURE_ITEMS_ALL.attribute1%TYPE,
14 p_attribute6 IN PA_EXPENDITURE_ITEMS_ALL.attribute1%TYPE,
15 p_attribute7 IN PA_EXPENDITURE_ITEMS_ALL.attribute1%TYPE,
16 p_attribute8 IN PA_EXPENDITURE_ITEMS_ALL.attribute1%TYPE,
17 p_attribute9 IN PA_EXPENDITURE_ITEMS_ALL.attribute1%TYPE,
18 p_attribute10 IN PA_EXPENDITURE_ITEMS_ALL.attribute1%TYPE,
19 p_attribute_category IN PA_EXPENDITURE_ITEMS_ALL.attribute_category%TYPE,
20 p_transaction_source IN PA_EXPENDITURE_ITEMS_ALL.transaction_source%TYPE,
21 p_ref2 IN PA_COST_DISTRIBUTION_LINES_ALL.system_reference2%TYPE,
22 p_ref3 IN PA_COST_DISTRIBUTION_LINES_ALL.system_reference3%TYPE,
23 p_ref4 IN PA_COST_DISTRIBUTION_LINES_ALL.system_reference4%TYPE)
24 return VARCHAR2 IS
25 v_grouping_method varchar2(2000) default null;
26 v_material_flag pa_expenditure_types.attribute10%TYPE;
27
28 /* Adding cursor for IPA for bug 5637615 */
29 CURSOR get_crl_instal_rec is
30 SELECT asset_name_id
31 FROM ipa_asset_naming_conventions ;
32
33 l_crl_rec ipa_asset_naming_convents_all.asset_name_id%TYPE;
34 BEGIN
35
36 /* Adding another check for IPA for bug 5637615 */
37 OPEN get_crl_instal_rec;
38 FETCH get_crl_instal_rec into l_crl_rec;
39 IF get_crl_instal_rec%notfound then
40 l_crl_rec :=NULL;
41 END IF;
42 CLOSE get_crl_instal_rec;
43
44
45 IF (PA_INSTALL.is_product_installed('IPA')) AND (l_crl_rec IS NOT NULL) THEN
46 /* All the CRL Customers need to uncomment this part */
47
48
49 /*bug5454123 adding IF-ELSE*/
50 IF (p_transaction_source IN('AP INVOICE', 'AP EXPENSE', 'AP NRTAX', 'AP DISCOUNTS', 'AP VARIANCE',
51 'PO RECEIPT', 'PO RECEIPT NRTAX', 'PO RECEIPT NRTAX PRICE ADJ', 'PO RECEIPT PRICE ADJ')
52 and p_attribute6 is NULL
53 and p_attribute7 is NULL
54 and p_attribute8 is NULL
55 and p_attribute9 is NULL
56 and p_attribute10 is NULL) THEN
57
58 v_grouping_method := p_ref2||p_ref3;
59
60 ELSE
61 /* Bug#2956569. Added IF condition to check if NL is installed and if so,
62 use attributes 6 and 7 on EI else do not use */
63
64 IF NVL(IPA_ASSET_MECH_APIS_PKG.g_nl_installed,'N') = 'Y' THEN
65 v_grouping_method := p_attribute8||p_attribute9||p_attribute10||
66 p_attribute6||p_attribute7;
67 ELSE
68 v_grouping_method := p_attribute8||p_attribute9||p_attribute10;
69 END IF;
70
71 /* For Grouping method by material flag do the following and comment
72 the other portion - grouping by expenditure type
73 Portion for grouping by Material and Non material */
74
75 /* CRL customers using Material flag as one of the grouping critera
76 uncomment the portion below
77 Note : Make sure the attrubute10 in pa_expenditure_types is populated
78 with the material flag indicator */
79 /* Select attribute10 into v_material_flag
80 From PA_EXPENDITURE_TYPES
81 Where expenditure_type = p_expnd_type;
82
83 if (v_material_flag is not null ) then
84 v_grouping_method := v_grouping_method || v_material_flag;
85 end if;
86 */
87 /* Portion for grouping by Expenditure Type
88 CRL customers using expenditure type (Not material flag)
89 uncomment the portion below */
90 /* v_grouping_method := v_grouping_method || p_expnd_type */
91 END IF; -- End if for bug 5523708
92 END IF;
93 -- If gruping method is null then return ALL
94 IF v_grouping_method is null then
95 v_grouping_method := 'ALL';
96 end if;
97 -- Return the Grouping Method
98 return v_grouping_method;
99
100 EXCEPTION
101 WHEN OTHERS THEN
102 null;
103 END;
104
105 END PA_CLIENT_EXTEN_CIP_GROUPING;
[Home] [Help]