[Home] [Help]
133: cnt number;
134: begin
135: cnt := 0;
136: select count(*) into cnt
137: from bic_dimv_time
138: where --act_period_start_date = trunc(p_date,'MONTH');
139: trunc(p_date) between trunc(act_period_start_date) and trunc(act_period_end_date); --bug 4308058
140: if cnt = 1 then
141: return true;
142: else
143: return false;
144: end if;
145: exception when others then
146: write_log(' The date ' || p_date || ' does not exist in bic_dimv_time ');
147: return false;
148: end;
149:
150: function set_periods_exist ( p_start_date varchar2,
165: generate_error('Main',' Profile Option BIC_SMRY_EXTRACTION_DATE is not set' );
166: return false;
167: end if;
168: select act_period_end_date +1 into g_period_start_date
169: from bic_dimv_time
170: where trunc(act_period_start_date) = trunc(x_date);
171: write_log('Start Date as obtained from Profile Value:'||
172: to_char(g_period_start_date,'dd-mm-yyyy'));
173: end if;
174: if p_end_date is not null then
175: g_period_end_date := fnd_date.canonical_to_date(p_end_date);
176: else
177: select max(act_period_start_date) into g_period_end_date
178: from bic_dimv_time
179: where trunc(act_period_end_date) < trunc(sysdate);
180: write_log('End Date set as:'||to_char(g_period_end_date,'dd-mm-yyyy'));
181: end if;
182: else
194: if ( if_exists(g_period_start_date) AND
195: if_exists(g_period_end_date) ) then
196: null;
197: else
198: generate_error('Main',' Start Date or End Date doesnot exist in bic_dimv_time ' );
199: return false;
200: end if;
201: return true;
202: exception when others then
216: per_cur curTyp;
217: org_str varchar2(3000);
218: n_org_str varchar2(3000);
219: rec_temp bic_temp_periods%ROWTYPE;
220: rec bic_dimv_time%ROWTYPE;
221: i number;
222: errcode number;
223: errmesg varchar2(200);
224: begin
225: org_str := ' select distinct bdt.* , hou.organization_id
226: from hr_operating_units hou,
227: fnd_product_groups ,
228: bic_party_summ db ,
229: bic_dimv_time bdt
230: where product_group_id = 1
231: and multi_org_flag = ''Y''
232: and ACT_PERIOD_START_DATE between :p_start_date
233: and :p_end_date
240: and bps.org_id = hou.organization_id
241: and '||rtrim(ltrim(p_measure_code))||' is not null ) ' ;
242:
243: n_org_str:= ' select *
244: from bic_dimv_time bdt
245: where ACT_PERIOD_START_DATE between :p_start_date
246: and :p_end_date
247: and not exists (
248: select 1
295: SELECT bdt.act_period_name , bdt.start_date , bdt.act_period_start_date ,
296: bdt.act_period_end_date , bdt.act_year , bdt.act_period_num ,
297: bdt.act_quarter , bdt.act_year_start_date , bdt.act_quarter_start_date ,
298: bdt.act_half_year , null
299: FROM bic_dimv_time bdt
300: where ACT_PERIOD_START_DATE between p_start_date
301: and p_end_date );
302: commit;
303: end;
2215: -- write_log('calling set_period_exist');
2216: if (set_periods_exist ( p_start_date,
2217: p_end_date
2218: ) = false ) then
2219: write_log('All Periods do not exist in the table bic_dimv_time , Plz. run the "Extract Calendar"');
2220: generate_error('Main','Periods should be extracted before running this program');
2221: return;
2222: end if;
2223: write_log('after set_periods_exist ' || g_period_start_date || ' ' || g_period_end_date );
2340: ool.ordered_quantity - nvl(ool.cancelled_quantity,0),
2341: null)) return_qty
2342: FROM aso_i_oe_order_lines_v ool,
2343: aso_i_oe_order_headers_v ooh,
2344: bic_dimv_time bdt,
2345: hz_cust_accounts hca
2346: WHERE ooh.header_id = ool.header_id
2347: AND ooh.sold_to_org_id is not null
2348: AND ooh.ordered_date between bdt.start_date and act_period_end_date
2413: from
2414: aso_i_oe_order_headers_v ooh,
2415: aso_i_oe_order_lines_v ool,
2416: hz_cust_accounts hca,
2417: bic_dimv_time bdt
2418: where
2419: trunc(bdt.start_date)
2420: BETWEEN trunc(g_period_start_date)AND trunc(g_period_end_date)
2421: and ooh.sold_to_org_id is not null