[Home] [Help]
PACKAGE BODY: APPS.GMF_ORGANIZATIONS_PKG
Source
1 PACKAGE BODY GMF_ORGANIZATIONS_PKG AS
2 /* $Header: GMFPORGB.pls 120.9.12020000.2 2012/07/24 14:53:19 spabolu ship $ */
3 --+==========================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+==========================================================================+
8 --| File Name : GMFPORGB.pls |
9 --| Package Name : GMF_ORGANIZATIONS_PKG |
10 --| API name : Get_Process_Organizations |
11 --| Type : Public |
12 --| Pre-reqs : N/A |
13 --| Function : Populate process organizations for a legal entity |
14 --| and range of organizations |
15 --| Parameters : p_Legal_Entity_id |
16 --| p_From_Orgn_Code |
17 --| p_To_Orgn_Code |
18 --| x_Row_Count |
19 --| x_Return_Status |
20 --| |
21 --|AUTHOR : Sukarna Reddy Dt 05-Jul-2005 |
22 --| Notes |
23 --| This package contains a procedure to populate global temporary table |
24 --| GMF_PROCESS_ORGANIZATIONS_GT with process organization for a |
25 --| specified legal entity. |
26 --| |
27 --| HISTORY |
28 --| umoogala 05-Aug-2005 Added Legal_entity_Id to the table. |
29 --| rpatangy 13-Aug-2009 B8757676 Added period_id to check for disabled org |
30 --+==========================================================================+
31
32 /* Bug 10048030Begin: overloaded procedure get_process_organizations */
33 PROCEDURE get_process_organizations(p_Legal_Entity_id IN NUMBER,
34 p_From_Orgn_Code IN VARCHAR2,
35 p_To_Orgn_Code IN VARCHAR2,
36 p_period_id IN NUMBER DEFAULT NULL,
37 x_Row_Count OUT NOCOPY NUMBER,
38 x_Return_Status OUT NOCOPY NUMBER)
39
40 IS
41 l_period_id gmf_period_statuses.period_id%TYPE; /* B8757676 */
42 l_period_start_date gmf_period_statuses.start_date%TYPE; /* B8757676 */
43 l_period_end_date gmf_period_statuses.end_date%TYPE; /* B8757676 */
44
45 BEGIN /* B8757676 */
46 l_period_id := p_period_id;
47 l_period_start_date := NULL; /*Bug10096240*/
48 l_period_end_date := NULL; /*Bug10096240*/
49
50 BEGIN
51 IF l_period_id IS NOT NULL THEN
52 SELECT start_date, end_date INTO
53 l_period_start_date, l_period_end_date
54 FROM gmf_period_statuses WHERE period_id = l_period_id ;
55 ELSE
56 NULL;
57 END IF;
58
59 EXCEPTION
60 WHEN NO_DATA_FOUND THEN
61 NULL ;
62 WHEN OTHERS THEN
63 NULL ;
64 END ; /* B8757676 */
65
66 get_process_organizations(p_Legal_Entity_id => p_Legal_Entity_id,
67 p_From_Orgn_Code => p_From_Orgn_Code ,
68 p_To_Orgn_Code => p_To_Orgn_Code ,
69 p_period_end_date => l_period_end_date, /* B10048030 */
70 x_Row_Count => x_Row_Count ,
71 x_Return_Status => x_Return_Status
72 ) ;
73
74 END get_process_organizations;
75 /* Bug 10048030 End */
76
77 PROCEDURE get_process_organizations(p_Legal_Entity_id IN NUMBER,
78 p_From_Orgn_Code IN VARCHAR2,
79 p_To_Orgn_Code IN VARCHAR2,
80 p_period_end_date IN DATE, /* B10048030 */
81 x_Row_Count OUT NOCOPY NUMBER,
82 x_Return_Status OUT NOCOPY NUMBER
83 ) IS
84 l_index PLS_INTEGER;
85 l_org_count PLS_INTEGER;
86 l_return_status VARCHAR2(5);
87 l_msg_data VARCHAR2(2000);
88
89 l_from_orgn_code MTL_ORGANIZATIONS.ORGANIZATION_CODE%TYPE;
90 l_to_orgn_code MTL_ORGANIZATIONS.ORGANIZATION_CODE%TYPE;
91 l_le_info XLE_BUSINESSINFO_GRP.INV_ORG_REC_TYPE;
92
93 TYPE inv_orgs IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
94 l_inv_orgs inv_orgs;
95 l_period_end_date gmf_period_statuses.end_date%TYPE; /* B10048030 */
96
97 BEGIN
98
99 x_Return_Status := 0;
100 l_from_orgn_code := p_from_orgn_code;
101 l_to_orgn_code := p_to_orgn_code;
102 l_period_end_date := p_period_end_date; /* B10048030 */
103
104 XLE_BUSINESSINFO_GRP.Get_InvOrg_Info(
105 x_return_status => l_return_status,
106 x_msg_data => l_msg_data,
107 P_InvOrg_ID => NULL,
108 P_Le_ID => p_legal_entity_id,
109 P_Party_ID => NULL,
110 x_Inv_Le_info => l_le_info);
111 l_org_count := l_le_info.count;
112
113 IF l_org_count = 0
114 THEN
115 x_Row_Count := 0;
116 x_return_status := -1; --No Rows returned by the API
117 RETURN;
118 END IF;
119
120
121 FOR l_index IN 1..l_org_count
122 LOOP
123 l_inv_orgs(l_index) := l_le_info(l_index).inv_org_id;
124 END LOOP;
125
126 FORALL j IN 1..l_le_info.COUNT
127 INSERT
128 INTO GMF_PROCESS_ORGANIZATIONS_GT
129 (
130 organization_id,
131 organization_code,
132 base_currency_code,
133 std_uom,
134 legal_entity_id,
135 operating_unit_id
136 )
137 SELECT mp.organization_id,
138 mp.organization_code,
139 gfp.base_currency_code,
140 NULL,
141 p_legal_entity_id,
142 ood.operating_unit
143 FROM mtl_parameters mp,
144 gmf_fiscal_policies gfp,
145 org_organization_definitions ood
146 WHERE mp.organization_id = l_inv_orgs(j)
147 AND mp.process_enabled_flag = 'Y'
148 AND mp.organization_code >= NVL(l_from_orgn_code,mp.organization_code)
149 AND mp.organization_code <= NVL(l_to_orgn_code,mp.organization_code)
150 AND gfp.legal_entity_id = p_legal_entity_id
151 AND ood.organization_id = l_inv_orgs(j)
152 AND ( ( ood.disable_date IS NOT NULL AND l_period_end_date IS NOT NULL
153 AND ood.disable_date > l_period_end_date )
154 OR
155 ( ood.disable_date IS NULL )
156 OR
157 ( l_period_end_date IS NULL ) -- B8757676
158 )
159 ;
160
161 x_Row_Count := sql%rowcount;
162
163 IF x_Row_Count = 0
164 THEN
165 x_return_status := -1; --No Rows returned by the API
166 RETURN;
167 END IF;
168
169
170 UPDATE gmf_process_organizations_gt gpo
171 SET std_uom = (SELECT u.uom_code
172 FROM mtl_units_of_measure u,
173 gmd_parameters_hdr h,
174 gmd_parameters_dtl d
175 WHERE u.base_uom_flag = 'Y'
176 AND gpo.organization_id = h.organization_id
177 AND h.parameter_id = d.parameter_id
178 AND d.parameter_name = 'FM_YIELD_TYPE'
179 AND d.parameter_value = u.uom_class)
180 WHERE gpo.std_uom IS NULL;
181
182 UPDATE gmf_process_organizations_gt gpo
183 SET std_uom = (SELECT u.uom_code
184 FROM mtl_units_of_measure u,
185 gmd_parameters_hdr h,
186 gmd_parameters_dtl d
187 WHERE u.base_uom_flag = 'Y'
188 AND h.organization_id IS NULL
189 AND h.parameter_id = d.parameter_id
190 AND d.parameter_name = 'FM_YIELD_TYPE'
191 AND d.parameter_value = u.uom_class)
192 WHERE gpo.std_uom IS NULL;
193
194 EXCEPTION
195 WHEN OTHERS
196 THEN
197 x_return_status := -1;
198
199 END get_process_organizations;
200
201 END GMF_ORGANIZATIONS_PKG;