[Home] [Help]
PACKAGE BODY: APPS.JAI_CMN_RG_BALANCES_PKG
Source
1 PACKAGE BODY jai_cmn_rg_balances_pkg AS
2 /* $Header: jai_cmn_rg_bals.plb 120.1 2005/07/20 12:57:15 avallabh ship $ */
3
4 PROCEDURE insert_row(
5
6 P_ORG_UNIT_ID IN JAI_CMN_RG_BALANCES.org_unit_id%TYPE,
7 P_ORGANIZATION_ID IN JAI_CMN_RG_BALANCES.organization_id%TYPE,
8 P_LOCATION_ID IN JAI_CMN_RG_BALANCES.location_id%TYPE,
9 P_PLA_BALANCE IN JAI_CMN_RG_BALANCES.pla_balance%TYPE,
10 P_RG23A_BALANCE IN JAI_CMN_RG_BALANCES.rg23a_balance%TYPE,
11 P_RG23C_BALANCE IN JAI_CMN_RG_BALANCES.rg23c_balance%TYPE,
12 P_CREATION_DATE IN JAI_CMN_RG_BALANCES.creation_date%TYPE,
13 P_CREATED_BY IN JAI_CMN_RG_BALANCES.created_by%TYPE,
14 P_LAST_UPDATE_DATE IN JAI_CMN_RG_BALANCES.last_update_date%TYPE,
15 P_LAST_UPDATED_BY IN JAI_CMN_RG_BALANCES.last_updated_by%TYPE,
16 P_LAST_UPDATE_LOGIN IN JAI_CMN_RG_BALANCES.last_update_login%TYPE,
17 P_BASIC_PLA_BALANCE IN JAI_CMN_RG_BALANCES.basic_pla_balance%TYPE,
18 P_ADDITIONAL_PLA_BALANCE IN JAI_CMN_RG_BALANCES.additional_pla_balance%TYPE,
19 P_OTHER_PLA_BALANCE IN JAI_CMN_RG_BALANCES.other_pla_balance%TYPE,
20 P_SIMULATE_FLAG IN VARCHAR2, -- DEFAULT 'N' File.Sql.35 by Brathod
21 P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
22 P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2
23 ) IS
24
25 /* Added by Ramananda for bug#4407165 */
26 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_balances_pkg.insert_row';
27
28 BEGIN
29 /*----------------------------------------------------------------------------------------------------------------------------
30 CHANGE HISTORY for FILENAME: jai_cmn_rg_balances_pkg.sql
31 S.No dd/mm/yyyy Author and Details
32 ------------------------------------------------------------------------------------------------------------------------------
33 1 16/07/2002 Vijay Shankar for Bug# 3496408, Version:115.0
34 Table handler Package for JAI_CMN_RG_BALANCES table
35
36 2. 08-Jun-2005 Version 116.2 jai_cmn_rg_bals -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
37 as required for CASE COMPLAINCE.
38
39 ----------------------------------------------------------------------------------------------------------------------------*/
40 /*P_SIMULATE_FLAG := 'N' ; */ --File.Sql.35 by Brathod
41
42 INSERT INTO JAI_CMN_RG_BALANCES(
43 ORG_UNIT_ID,
44 ORGANIZATION_ID,
45 LOCATION_ID,
46 PLA_BALANCE,
47 RG23A_BALANCE,
48 RG23C_BALANCE,
49 CREATION_DATE,
50 CREATED_BY,
51 LAST_UPDATE_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_LOGIN,
54 BASIC_PLA_BALANCE,
55 ADDITIONAL_PLA_BALANCE,
56 OTHER_PLA_BALANCE
57 ) VALUES (
58 P_ORG_UNIT_ID,
59 P_ORGANIZATION_ID,
60 P_LOCATION_ID,
61 P_PLA_BALANCE,
62 P_RG23A_BALANCE,
63 P_RG23C_BALANCE,
64 P_CREATION_DATE,
65 P_CREATED_BY,
66 P_LAST_UPDATE_DATE,
67 P_LAST_UPDATED_BY,
68 P_LAST_UPDATE_LOGIN,
69 P_BASIC_PLA_BALANCE,
70 P_ADDITIONAL_PLA_BALANCE,
71 P_OTHER_PLA_BALANCE
72 );
73
74 /* Added by Ramananda for bug#4407165 */
75 EXCEPTION
76 WHEN OTHERS THEN
77 P_PROCESS_STATUS := 'E';
78 P_PROCESS_MESSAGE :='Error in '||lv_object_name;
79 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
80 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
81 app_exception.raise_exception;
82
83 END insert_row;
84
85 PROCEDURE update_row(
86 P_ORGANIZATION_ID IN JAI_CMN_RG_BALANCES.organization_id%TYPE,
87 P_LOCATION_ID IN JAI_CMN_RG_BALANCES.location_id%TYPE,
88 p_register_type IN VARCHAR2,
89 p_amount_to_be_added IN NUMBER,
90 P_SIMULATE_FLAG IN VARCHAR2, -- DEFAULT 'N' File.Sql.35 by Brathod
91 P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
92 P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2
93 ) IS
94
95 ln_rg23a_amount NUMBER := 0;
96 ln_rg23c_amount NUMBER := 0;
97 ln_pla_amount NUMBER := 0;
98
99 /* Added by Ramananda for bug#4407165 */
100 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_balances_pkg.update_row';
101
102
103 BEGIN
104
105 IF p_register_type = 'A' THEN
106 ln_rg23a_amount := p_amount_to_be_added;
107 ELSIF p_register_type = 'C' THEN
108 ln_rg23c_amount := p_amount_to_be_added;
109 ELSIF p_register_type = 'PLA' THEN
110 ln_pla_amount := p_amount_to_be_added;
111 ELSE
112 P_PROCESS_STATUS := 'E';
113 P_PROCESS_MESSAGE := 'jai_cmn_rg_balances_pkg.update_row: Not able to find Register Type';
114 RETURN;
115 END IF;
116
117 UPDATE JAI_CMN_RG_BALANCES SET
118 PLA_BALANCE = nvl(PLA_BALANCE, 0) + ln_pla_amount,
119 RG23A_BALANCE = nvl(RG23A_BALANCE, 0) + ln_rg23a_amount,
120 RG23C_BALANCE = nvl(RG23C_BALANCE, 0) + ln_rg23c_amount,
121 LAST_UPDATE_DATE = SYSDATE,
122 LAST_UPDATED_BY = FND_GLOBAL.user_id,
123 LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
124 WHERE organization_id = p_organization_id
125 AND location_id = p_location_id;
126
127 /* Added by Ramananda for bug#4407165 */
128 EXCEPTION
129 WHEN OTHERS THEN
130 P_PROCESS_STATUS := null;
131 P_PROCESS_MESSAGE := null;
132 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
133 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
134 app_exception.raise_exception;
135
136 END update_row;
137
138 PROCEDURE get_balance(
139 P_ORGANIZATION_ID IN JAI_CMN_RG_BALANCES.organization_id%TYPE,
140 P_LOCATION_ID IN JAI_CMN_RG_BALANCES.location_id%TYPE,
141 P_REGISTER_TYPE IN VARCHAR2,
142 P_OPENING_BALANCE OUT NOCOPY VARCHAR2,
143 P_PROCESS_STATUS OUT NOCOPY VARCHAR2,
144 P_PROCESS_MESSAGE OUT NOCOPY VARCHAR2
145 ) IS
146
147 ln_organization_id NUMBER;
148 ln_rg23a_amount NUMBER := 0;
149 ln_rg23c_amount NUMBER := 0;
150 ln_pla_amount NUMBER := 0;
151
152 CURSOR c_rg_balances(cp_organization_id IN NUMBER, cp_location_id IN NUMBER) IS
153 SELECT organization_id, rg23a_balance, rg23c_balance, pla_balance
154 FROM JAI_CMN_RG_BALANCES
155 WHERE organization_id = cp_organization_id
156 AND location_id = cp_location_id;
157
158 BEGIN
159
160 OPEN c_rg_balances(p_organization_id, p_location_id);
161 FETCH c_rg_balances INTO ln_organization_id, ln_rg23a_amount, ln_rg23c_amount, ln_pla_amount;
162 CLOSE c_rg_balances;
163
164 IF ln_organization_id IS NULL THEN
165 p_process_status := 'E';
166 p_process_message := 'No Record found in JAI_CMN_RG_BALANCES';
167 RETURN;
168 END IF;
169
170 IF p_register_type = 'A' THEN
171 P_OPENING_BALANCE := ln_rg23a_amount;
172 ELSIF p_register_type = 'C' THEN
173 P_OPENING_BALANCE := ln_rg23c_amount;
174 ELSIF p_register_type = 'PLA' THEN
175 P_OPENING_BALANCE := ln_pla_amount;
176 ELSE
177 p_opening_balance := 0;
178 END IF;
179
180 END get_balance;
181
182 END jai_cmn_rg_balances_pkg;