1 PACKAGE BODY poa_savings_main AS
2 /* $Header: poasvp1b.pls 120.0 2005/06/01 12:47:24 appldev noship $ */
3
4 /*
5 NAME
6 populate_savings -
7 DESCRIPTION
8 main function for populating poa_savings fact table
9 for Oracle Purchasing
10 */
11 --
12 PROCEDURE populate_savings(p_start_date IN DATE, p_end_date IN DATE,
13 p_populate_inc IN BOOLEAN := TRUE)
14 IS
15
16 TYPE T_FLEXREF IS REF CURSOR;
17
18 v_num_rows NUMBER := 0;
19 v_start_time DATE;
20
21 v_account_cursor T_FLEXREF;
22 v_ccid NUMBER := 0;
23 v_cost_center_id VARCHAR2(2000) := NULL;
24 v_account_id VARCHAR2(2000) := NULL;
25 v_company_id VARCHAR2(2000) := NULL;
26 v_set_of_books_id NUMBER := 0;
27
28 v_buf VARCHAR2(240) := NULL;
29 x_progress VARCHAR2(3) := NULL;
30
31 --
32 l_poa_schema VARCHAR2(30);
33 l_stmt VARCHAR2(120);
34 l_status VARCHAR2(30);
35 l_industry VARCHAR2(30);
36 l_batch_size NUMBER := fnd_profile.value('POA_COLLECTION_BATCH_SIZE');
37 l_no_batch NUMBER;
38 cursor v_changed_rows(p_start_date date,p_end_date date,p_batch_size number) is
39 SELECT PO_DISTRIBUTION_ID, 1,ceil(rownum/p_batch_size)
40 FROM (SELECT pod.PO_DISTRIBUTION_ID,pol.item_id,pod.creation_date
41 FROM po_lines_all pol,
42 po_line_locations_all pll,
43 po_headers_all poh,
44 po_distributions_all pod
45 WHERE pod.line_location_id = pll.line_location_id
46 and pod.po_line_id = pol.po_line_id
47 and pod.po_header_id = poh.po_header_id
48 and pll.shipment_type = 'STANDARD'
49 and pll.approved_flag = 'Y'
50 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
51 and greatest(pol.last_update_date, pll.last_update_date,
52 poh.last_update_date, pod.last_update_date, nvl(pod.program_update_date, pod.last_update_date))
53 between p_start_date and p_end_date
54 UNION ALL
55 SELECT pod.PO_DISTRIBUTION_ID,pol.item_id,pod.creation_date
56 FROM po_lines_all pol,
57 po_line_locations_all pll,
58 po_headers_all poh,
59 po_releases_all por,
60 po_distributions_all pod
61 WHERE pod.line_location_id = pll.line_location_id
62 and pod.po_release_id = por.po_release_id
63 and pod.po_line_id = pol.po_line_id
64 and pod.po_header_id = poh.po_header_id
65 and pll.shipment_type in ('BLANKET', 'SCHEDULED')
66 and pll.approved_flag = 'Y'
67 and nvl(pod.distribution_type,'-99') <> 'AGREEMENT'
68 and greatest(pol.last_update_date,pll.last_update_date,
69 poh.last_update_date,por.last_update_date,pod.last_update_date, nvl(pod.program_update_date, pod.last_update_date))
70 between p_start_date and p_end_date)
71 order by item_id,creation_date;
72
73 TYPE plsqltable IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
74 l_primary_key plsqltable;
75 l_seq_id plsqltable;
76 l_batch_id plsqltable;
77 l_count NUMBER;
78 l_start_time date;
79 l_end_time date;
80 BEGIN
81 select sysdate into l_start_time from dual;
82 POA_LOG.debug_line('Populate_savings: entered');
83 POA_LOG.debug_line(' ');
84
85 -- check if we need to populate the INC table poa_edw_po_dist_inc.
86 -- For EDW, we don't need to, since that is taken care in Push program.
87 -- For OLTP, we should.
88
89 if p_populate_inc then
90
91 IF (FND_INSTALLATION.GET_APP_INFO('POA',l_status,l_industry,l_poa_schema)) THEN
92 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema ||'.POA_EDW_PO_DIST_INC';
93 EXECUTE IMMEDIATE l_stmt;
94 END IF;
95 open v_changed_rows(p_start_date,p_end_date,l_batch_size);
96 loop
97 /* l_primary_key.delete; l_seq_id.delete; l_batch_id.delete; */
98 fetch v_changed_rows bulk collect into
99 l_primary_key,l_seq_id,l_batch_id limit l_batch_size;
100 l_count := l_primary_key.count;
101 forall i in 1..l_count
102 INSERT INTO poa_edw_po_dist_inc (primary_key, seq_id,batch_id) values(l_primary_key(i),l_seq_id(i),l_batch_id(i));
103 EXIT WHEN l_count < l_batch_size;
104 end loop;
105 close v_changed_rows;
106 select sysdate into l_end_time from dual;
107 poa_log.put_line('time to populate incremental table: '|| poa_log.duration(l_end_time-l_start_time) || ', start time: ' || to_char(l_start_time, 'MM/DD/YYYY HH24:MI:SS') || ', end time: ' || to_char(l_end_time, 'MM/DD/YYYY HH24:MI:SS'));
108 end if;
109 -----------------------------------------------------------------------
110
111
112 -- Temporary table is used for running the report.
113 -- Delete from the temporary table where the entries
114 -- are more than 2 days old.
115
116 x_progress := '015';
117 delete from poa_bis_savings_rpt
118 where last_update_date <= sysdate - 2;
119
120 -- Get the current timestamp. All entries inserted into
121 -- the fact table will use this timestamp as the last updated
122 -- date and date of creation.
123
124 x_progress := '020';
125
126 select max(batch_id) into l_no_batch from poa_edw_po_dist_inc;
127
128 SELECT sysdate INTO v_start_time from sys.dual;
129
130 if (l_no_batch is NOT NULL) then
131 FOR v_batch_no IN 0..l_no_batch LOOP
132 poa_savings_np.populate_npcontract(p_start_date, p_end_date,
133 v_start_time, v_batch_no);
134 poa_savings_con.populate_contract(p_start_date, p_end_date,
135 v_start_time, v_batch_no);
136 commit;
137 END LOOP;
138 end if;
139
140 select sysdate into l_end_time from dual;
141 poa_log.put_line('total time taken for bis savings : '||poa_log.duration(l_end_time-l_start_time));
142
143 /* Loop through to get the account information for each
144 * distribution
145 */
146
147 /* The account information is currently not used by any reports.
148 * Scoping this out for now.
149 */
150
151 /*
152 POA_LOG.debug_line('Opening cursor v_account_cursor');
153 POA_LOG.debug_line(' ');
154
155 OPEN v_account_cursor FOR
156 SELECT distinct pod.code_combination_id
157 , pod.set_of_books_id
158 FROM po_distributions_all pod
159 where pod.po_distribution_id IN
160 (SELECT distinct poa.distribution_transaction_id
161 FROM poa_bis_savings poa);
162
163
164 LOOP
165 FETCH v_account_cursor INTO
166 v_ccid,
167 v_set_of_books_id;
168 EXIT WHEN v_account_cursor%NOTFOUND;
169
170 poa_savings_acct.get_cac_info(v_ccid, v_set_of_books_id,
171 v_cost_center_id,
172 v_account_id,
173 v_company_id);
174
175 x_progress := '030';
176 UPDATE poa_bis_savings poa
177 set cost_center_id = v_cost_center_id,
178 account_id = v_account_id,
179 company_id = v_company_id
180 WHERE poa.distribution_transaction_id IN
181 (SELECT pod.po_distribution_id
182 FROM po_distributions_all pod
183 WHERE pod.code_combination_id = v_ccid
184 and pod.set_of_books_id = v_set_of_books_id);
185
186 END LOOP;
187 CLOSE v_account_cursor;
188
189 POA_LOG.debug_line('Closed cursor v_account_cursor');
190 POA_LOG.debug_line(' ');
191
192 POA_LOG.debug_line('Populate_savings: Updating account complete ');
193 POA_LOG.debug_line(' ');
194 */
195 POA_LOG.debug_line('Populate_savings: exit');
196 POA_LOG.debug_line(' ');
197
198 EXCEPTION
199 WHEN others THEN
200 v_buf := 'Main function: ' || sqlcode || ': ' || sqlerrm || ': ' || x_progress;
201 ROLLBACK;
202
203 POA_LOG.put_line(v_buf);
204 POA_LOG.put_line(' ');
205 RAISE;
206 END populate_savings;
207 --
208
209 END poa_savings_main;
210 --
[Home] [Help]