[Home] [Help]
PACKAGE BODY: APPS.GMF_ORGANIZATIONS_PKG
Source
1 PACKAGE BODY GMF_ORGANIZATIONS_PKG AS
2 /* $Header: GMFPORGB.pls 120.5 2005/10/13 15:08:28 umoogala noship $ */
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 --+==========================================================================+
30
31
32 PROCEDURE get_process_organizations(p_Legal_Entity_id IN NUMBER,
33 p_From_Orgn_Code IN VARCHAR2,
34 p_To_Orgn_Code IN VARCHAR2,
35 x_Row_Count OUT NOCOPY NUMBER,
36 x_Return_Status OUT NOCOPY NUMBER
37 ) IS
38 l_index PLS_INTEGER;
39 l_org_count PLS_INTEGER;
40 l_return_status VARCHAR2(5);
41 l_msg_data VARCHAR2(2000);
42
43 l_from_orgn_code MTL_ORGANIZATIONS.ORGANIZATION_CODE%TYPE;
44 l_to_orgn_code MTL_ORGANIZATIONS.ORGANIZATION_CODE%TYPE;
45 l_le_info XLE_BUSINESSINFO_GRP.INV_ORG_REC_TYPE;
46
47 TYPE inv_orgs IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
48 l_inv_orgs inv_orgs;
49
50 BEGIN
51
52 x_Return_Status := 0;
53 l_from_orgn_code := p_from_orgn_code;
54 l_to_orgn_code := p_to_orgn_code;
55
56 XLE_BUSINESSINFO_GRP.Get_InvOrg_Info(
57 x_return_status => l_return_status,
58 x_msg_data => l_msg_data,
59 P_InvOrg_ID => NULL,
60 P_Le_ID => p_legal_entity_id,
61 P_Party_ID => NULL,
62 x_Inv_Le_info => l_le_info);
63 l_org_count := l_le_info.count;
64
65 IF l_org_count = 0
66 THEN
67 x_Row_Count := 0;
68 x_return_status := -1; --No Rows returned by the API
69 RETURN;
70 END IF;
71
72
73 FOR l_index IN 1..l_org_count
74 LOOP
75 l_inv_orgs(l_index) := l_le_info(l_index).inv_org_id;
76 END LOOP;
77
78 FORALL j IN 1..l_le_info.COUNT
79 INSERT
80 INTO GMF_PROCESS_ORGANIZATIONS_GT
81 (
82 organization_id,
83 organization_code,
84 base_currency_code,
85 std_uom,
86 legal_entity_id,
87 operating_unit_id
88 )
89 SELECT mp.organization_id,
90 mp.organization_code,
91 gfp.base_currency_code,
92 NULL,
93 p_legal_entity_id,
94 ood.operating_unit
95 FROM mtl_parameters mp,
96 gmf_fiscal_policies gfp,
97 org_organization_definitions ood
98 WHERE mp.organization_id = l_inv_orgs(j)
99 AND mp.process_enabled_flag = 'Y'
100 AND mp.organization_code >= NVL(l_from_orgn_code,mp.organization_code)
101 AND mp.organization_code <= NVL(l_to_orgn_code,mp.organization_code)
102 AND gfp.legal_entity_id = p_legal_entity_id
103 AND ood.organization_id = l_inv_orgs(j)
104 ;
105
106 x_Row_Count := sql%rowcount;
107
108 IF x_Row_Count = 0
109 THEN
110 x_return_status := -1; --No Rows returned by the API
111 RETURN;
112 END IF;
113
114
115 UPDATE gmf_process_organizations_gt gpo
116 SET std_uom = (SELECT u.uom_code
117 FROM mtl_units_of_measure u,
118 gmd_parameters_hdr h,
119 gmd_parameters_dtl d
120 WHERE u.base_uom_flag = 'Y'
121 AND gpo.organization_id = h.organization_id
122 AND h.parameter_id = d.parameter_id
123 AND d.parameter_name = 'FM_YIELD_TYPE'
124 AND d.parameter_value = u.uom_class)
125 WHERE gpo.std_uom IS NULL;
126
127 UPDATE gmf_process_organizations_gt gpo
128 SET std_uom = (SELECT u.uom_code
129 FROM mtl_units_of_measure u,
130 gmd_parameters_hdr h,
131 gmd_parameters_dtl d
132 WHERE u.base_uom_flag = 'Y'
133 AND h.organization_id IS NULL
134 AND h.parameter_id = d.parameter_id
135 AND d.parameter_name = 'FM_YIELD_TYPE'
136 AND d.parameter_value = u.uom_class)
137 WHERE gpo.std_uom IS NULL;
138
139 EXCEPTION
140 WHEN OTHERS
141 THEN
142 x_return_status := -1;
143
144 END get_process_organizations;
145
146 END GMF_ORGANIZATIONS_PKG;