[Home] [Help]
PACKAGE BODY: APPS.GME_INIT_RSRC_TXNS_SUMMARY
Source
1 PACKAGE BODY GME_INIT_RSRC_TXNS_SUMMARY AS
2 /* $Header: GMEMIRSB.pls 120.0 2005/05/26 14:22:32 appldev noship $ */
3 /*============================================================================
4 | Copyright (c) 2002 Oracle Corporation
5 | Redwood Shores, California, USA
6 | All rights reserved
7 =============================================================================
8 | FILENAME
9 | GMEMIRSB.pls
10 |
11 | DESCRIPTION
12 | Package body containing the procedures used to populate the new
13 | resource transaction summary table.
14 |
15 | NOTES
16 |
17 | HISTORY
18 | 03-OCT-2002 Eddie Oumerretane Created.
19 | 04-NOV-2002 Eddie Oumerretane Added a check to prevent the table
20 | from being re-populated if the patch 2565952 has already been
21 | applied.
22 =============================================================================
23 */
24
25 PROCEDURE Initialize_Rsrc_Txns_Summary IS
26
27
28 CURSOR Get_Rsrc_Txns_Summary IS
29 SELECT
30 COUNT(*)
31 FROM
32 gme_resource_txns_summary;
33
34 CURSOR Cur_Get_Rsrc_Txns IS
35
36 SELECT
37 orgn_code,
38 resources,
39 instance_id,
40 start_date,
41 end_date,
42 sequence_dependent_ind,
43 last_updated_by,
44 last_update_login
45 FROM
46 gme_resource_txns
47 WHERE
48 completed_ind = 0 AND
49 delete_mark = 0;
50
51 CURSOR Cur_get_resource_id (p_orgn_code VARCHAR2,
52 p_resource VARCHAR2) IS
53 SELECT
54 resource_id
55 FROM
56 cr_rsrc_dtl
57 WHERE
58 resources = p_resource AND
59 orgn_code = p_orgn_code;
60
61 l_resource_id GME_RESOURCE_TXNS_SUMMARY.resource_id%TYPE;
62 l_sysdate DATE;
63 l_count NUMBER;
64
65 BEGIN
66
67 OPEN Get_Rsrc_Txns_Summary;
68 FETCH Get_Rsrc_Txns_Summary INTO l_count;
69
70 --- If the resource transaction summary table is not empty, then we
71 --- assume that patch 2565952 has already been applied and that the
72 --- table has alredy been populated.
73 IF Get_Rsrc_Txns_Summary%NOTFOUND OR l_count <= 0 THEN
74
75 FOR txns_rec IN Cur_Get_Rsrc_Txns
76 LOOP
77
78 OPEN Cur_get_resource_id (txns_rec.orgn_code,
79 txns_rec.resources);
80
81 FETCH Cur_get_resource_id INTO l_resource_id;
82
83 IF Cur_get_resource_id%NOTFOUND THEN
84 CLOSE Cur_get_resource_id;
85 ELSE
86
87 CLOSE Cur_get_resource_id;
88
89 l_sysdate := SYSDATE;
90
91
92 --- First try to update the table by incrementing the number
93 --- of required units.
94
95 UPDATE
96 gme_resource_txns_summary
97 SET
98 required_units = required_units + 1,
99 last_updated_by = txns_rec.last_updated_by,
100 last_update_date = l_sysdate,
101 last_update_login = txns_rec.last_update_login
102 WHERE
103 start_date = txns_rec.start_date AND
104 end_date = txns_rec.end_date AND
105 resource_id = l_resource_id AND
106 NVL(instance_id, -1) = NVL(txns_rec.instance_id, -1) AND
107 sequence_dependent_ind = NVL(txns_rec.sequence_dependent_ind, 0);
108
109 IF (SQL%NOTFOUND) THEN
110
111 --- This new interval does not exist, we need to create it
112
113 INSERT INTO gme_resource_txns_summary
114 ( resource_id
115 ,instance_id
116 ,start_date
117 ,end_date
118 ,required_units
119 ,sequence_dependent_ind
120 ,creation_date
121 ,last_update_date
122 ,created_by
123 ,last_updated_by
124 ,last_update_login)
125 VALUES
126 ( l_resource_id
127 ,txns_rec.instance_id
128 ,txns_rec.start_date
129 ,txns_rec.end_date
130 ,1
131 ,NVL(txns_rec.sequence_dependent_ind, 0)
132 ,l_sysdate
133 ,l_sysdate
134 ,txns_rec.last_updated_by
135 ,txns_rec.last_updated_by
136 ,txns_rec.last_update_login);
137 END IF;
138
139 END IF;
140
141 END LOOP;
142
143 END IF;
144
145 CLOSE Get_Rsrc_Txns_Summary;
146
147 COMMIT;
148
149 EXCEPTION
150 WHEN OTHERS THEN
151 CLOSE Get_Rsrc_Txns_Summary;
152 ROLLBACK;
153 FND_MESSAGE.SET_NAME('GME', 'GME_UNEXPECTED_ERROR');
154 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
155 APP_EXCEPTION.raise_exception;
156
157 END Initialize_Rsrc_Txns_Summary;
158
159 END GME_INIT_RSRC_TXNS_SUMMARY;