[Home] [Help]
PACKAGE BODY: APPS.HXC_RDB_RECIPIENT_SNAPSHOT
Source
1 PACKAGE BODY HXC_RDB_RECIPIENT_SNAPSHOT AS
2 /* $Header: hxcrdbrecsnp.pkb 120.1.12010000.2 2010/04/03 12:25:17 asrajago noship $ */
3
4 PROCEDURE get_snapshot(errbuff OUT NOCOPY VARCHAR2,
5 retcode OUT NOCOPY NUMBER,
6 p_request_id IN NUMBER DEFAULT 0)
7 IS
8
9 CURSOR get_retro_exp
10 IS SELECT pa.expenditure_item_id,
11 exp.expenditure_group,
12 ROWIDTOCHAR(ret.rowid)
13 FROM hxc_ret_pa_latest_details ret,
14 pa_expenditure_items_all pa,
15 pa_expenditures_all exp
16 WHERE ret.request_id = p_request_id
17 AND pa.transaction_source = 'ORACLE TIME AND LABOR'
18 AND pa.orig_transaction_reference = ret.time_building_block_id||':'||ret.object_version_number
19 AND pa.net_zero_adjustment_flag = 'Y'
20 AND pa.expenditure_id = exp.expenditure_id
21 AND ret.old_measure IS NOT NULL;
22
23 CURSOR get_new_exp
24 IS SELECT pa.expenditure_item_id,
25 exp.expenditure_group,
26 ROWIDTOCHAR(ret.rowid)
27 FROM hxc_ret_pa_latest_details ret,
28 pa_expenditure_items_all pa,
29 pa_expenditures_all exp
30 WHERE ret.request_id = p_request_id
31 AND pa.transaction_source = 'ORACLE TIME AND LABOR'
32 AND pa.orig_transaction_reference = ret.time_building_block_id||':'||ret.object_version_number
33 AND pa.net_zero_adjustment_flag = 'N'
34 AND pa.expenditure_id = exp.expenditure_id ;
35
36
37 eitab NUMBERTAB;
38 ettab VARCHARTAB;
39 rowtab VARCHARTAB;
40
41 l_req_complete BOOLEAN := FALSE;
42 l_request_id NUMBER;
43
44 l_call_status BOOLEAN ;
45 l_interval NUMBER := 30;
46 l_phase VARCHAR2(30);
47 l_status VARCHAR2(30);
48 l_dev_phase VARCHAR2(30);
49 l_dev_status VARCHAR2(30);
50 l_message VARCHAR2(30);
51
52
53
54 BEGIN
55
56 l_request_id := p_request_id;
57
58 l_call_status := FND_CONCURRENT.get_request_status(l_request_id,
59 '',
60 '',
61 l_phase,
62 l_status,
63 l_dev_phase,
64 l_dev_status,
65 l_message);
66
67 IF l_dev_phase <> 'COMPLETE'
68 THEN
69 l_req_complete := FALSE;
70 END IF;
71
72 IF l_call_status = FALSE
73 THEN
74 l_req_complete := TRUE;
75 END IF;
76
77 << WAIT_AND_PICK_TIMECARDS >>
78 LOOP
79
80 OPEN get_retro_exp;
81 LOOP
82 FETCH get_retro_exp
83 BULK COLLECT INTO eitab,ettab,
84 rowtab LIMIT 1000;
85
86 EXIT WHEN eitab.COUNT = 0;
87
88 FORALL i IN eitab.FIRST..eitab.LAST
89 UPDATE hxc_ret_pa_latest_details
90 SET retro_pei_id = eitab(i),
91 retro_exp_group = ettab(i)
92 WHERE ROWID = CHARTOROWID(rowtab(i));
93 COMMIT;
94
95 END LOOP;
96 CLOSE get_retro_exp;
97
98 OPEN get_new_exp;
99 LOOP
100 FETCH get_new_exp
101 BULK COLLECT INTO eitab,ettab,
102 rowtab LIMIT 1000;
103
104 EXIT WHEN eitab.COUNT = 0;
105
106 FORALL i IN eitab.FIRST..eitab.LAST
107 UPDATE hxc_ret_pa_latest_details
108 SET pei_id = eitab(i),
109 exp_group = ettab(i)
110 WHERE ROWID = CHARTOROWID(rowtab(i));
111 COMMIT;
112
113 END LOOP;
114 CLOSE get_new_exp;
115
116
117 IF l_req_complete = TRUE
118 THEN
119 EXIT WAIT_AND_PICK_TIMECARDS ;
120 ELSE
121 dbms_lock.sleep(10);
122 l_call_status := FND_CONCURRENT.get_request_status(l_request_id,
123 '',
124 '',
125 l_phase,
126 l_status,
127 l_dev_phase,
128 l_dev_status,
129 l_message);
130
131 IF l_dev_phase <> 'COMPLETE'
132 THEN
133 l_req_complete := FALSE;
134 ELSE
135 l_req_complete := TRUE;
136 END IF;
137
138 IF l_call_status = FALSE
139 THEN
140 l_req_complete := TRUE;
141 END IF;
142
143 END IF;
144
145 END LOOP WAIT_AND_PICK_TIMECARDS;
146
147
148 DELETE FROM hxc_rdb_pending_processes
149 WHERE request_id = l_request_id;
150
151
152 COMMIT;
153
154
155
156 END get_snapshot;
157
158 END HXC_RDB_RECIPIENT_SNAPSHOT;
159