1 PACKAGE BODY PJM_UTILS AS
2 /* $Header: PJMPUTLB.pls 120.2 2006/03/08 14:43:20 yliou noship $ */
3 FUNCTION Get_Demand_Quantity (
4 X_line_id IN NUMBER
5 ) RETURN NUMBER IS
6
7 L_qty NUMBER := 0;
8
9 BEGIN
10
11 select PRIMARY_UOM_QUANTITY
12 into L_qty
13 from MTL_DEMAND
14 where DEMAND_SOURCE_LINE = X_line_id
15 AND DEMAND_SOURCE_TYPE IN (2,8,12)
16 AND RESERVATION_TYPE IN (2,3);
17
18 return ( L_qty );
19
20 exception
21 when others then
22 return ( 0 );
23
24 END Get_Demand_Quantity;
25
26
27 --
28 -- Name : Last_Accum_Period
29 -- Pre-reqs : None.
30 -- Function : This function returns the last accumulated period
31 -- for a given project or project / task combo.
32 --
33 --
34 -- Parameters :
35 -- IN : X_project_id IN NUMBER
36 -- : X_task_id IN NUMBER
37 --
38 -- Returns : Last accum period
39 --
40 FUNCTION Last_Accum_Period (
41 X_project_id IN NUMBER,
42 X_task_id IN NUMBER
43 ) RETURN VARCHAR2 IS
44
45 L_accum_period_type VARCHAR2(30);
46 L_period_name VARCHAR2(20);
47
48 CURSOR PA_Accum_Period ( C_project_id NUMBER
49 , C_task_id NUMBER )
50 IS
51 SELECT accum.accum_period
52 FROM pa_project_accum_headers accum
53 , pa_periods per
54 WHERE accum.project_id = C_project_id
55 AND accum.task_id = nvl(C_task_id, accum.task_id)
56 AND per.period_name = accum.accum_period
57 ORDER BY per.start_date desc;
58
59 CURSOR GL_Accum_Period ( C_project_id NUMBER
60 , C_task_id NUMBER )
61 IS
62 SELECT accum.accum_period
63 FROM pa_project_accum_headers accum
64 , pa_implementations_all imp
65 , gl_sets_of_books sob
66 , gl_periods per
67 , pa_projects_all pa
68 WHERE accum.project_id = C_project_id
69 AND pa.project_id = accum.project_id
70 AND accum.task_id = nvl(C_task_id, accum.task_id)
71 AND sob.set_of_books_id = imp.set_of_books_id
72 AND per.period_set_name = sob.period_set_name
73 AND per.period_type = sob.accounted_period_type
74 AND per.period_name = accum.accum_period
75 AND imp.org_id = pa.org_id
76 ORDER BY per.start_date desc;
77
78 BEGIN
79
80 select accumulation_period_type
81 into L_accum_period_type
82 from pa_implementations;
83
84 if ( L_accum_period_type = 'PA' ) then
85 open PA_Accum_Period ( X_project_id
86 , X_task_id );
87 fetch PA_Accum_Period into L_period_name;
88 close PA_Accum_Period;
89 elsif ( L_accum_period_type = 'GL' ) then
90 open GL_Accum_Period ( X_project_id
91 , X_task_id );
92 fetch GL_Accum_Period into L_period_name;
93 close GL_Accum_Period;
94 else
95 L_period_name := null;
96 end if;
97
98 return ( L_period_name );
99
100 exception
101 when others then
102 return ( NULL );
103
104 end Last_Accum_Period;
105
106
107 --
108 -- Name : default_wip_acct_class
109 -- Pre-reqs : None.
110 -- Function : This function returns the default WIP accounting
111 -- class for the given organization , project and
112 -- task. The default is derived first from the
113 -- task level, and if not found, the project level.
114 --
115 --
116 -- Parameters :
117 -- IN : X_inventory_org_id IN NUMBER
118 -- : X_project_id IN NUMBER
119 -- : X_task_id IN NUMBER
120 -- : X_class_type IN NUMBER
121 --
122 -- Returns : WIP accounting class code
123 --
124 FUNCTION default_wip_acct_class
125 ( X_inventory_org_id in number
126 , X_project_id in number
127 , X_task_id in number
128 , X_class_type in number
129 ) RETURN VARCHAR2 IS
130
131 cursor c is
132 select wac.class_code
133 from wip_accounting_classes wac
134 , ( select 2 lvl
135 , wip_acct_class_code
136 , eam_acct_class_code
137 from pjm_project_parameters
138 where organization_id = X_inventory_org_id
139 and project_id = X_project_id
140 union all
141 select 1 lvl
142 , wip_acct_class_code
143 , eam_acct_class_code
144 from pjm_task_wip_acct_classes
145 where organization_id = X_inventory_org_id
146 and project_id = X_project_id
147 and task_id = X_task_id ) pjm
148 where wac.organization_id = X_inventory_org_id
149 and wac.class_code in ( pjm.wip_acct_class_code , pjm.eam_acct_class_code )
150 and wac.class_type = X_class_type
151 order by lvl , wac.class_code;
152
153 crec c%rowtype;
154
155 BEGIN
156
157 open c;
158 fetch c into crec;
159 close c;
160 return ( crec.class_code );
161
162 END default_wip_acct_class;
163
164 end;
[Home] [Help]