[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_SAM_TOP_AGR_PKG
Source
1 package body isc_dbi_sam_top_agr_pkg as
2 /* $Header: ISCRGCGB.pls 120.7 2005/12/13 18:16:26 scheung noship $ */
3
4 procedure get_sql ( p_param in bis_pmv_page_parameter_tbl,
5 x_custom_sql out nocopy varchar2,
6 x_custom_output out nocopy bis_query_attributes_tbl) is
7
8 l_func varchar2(32000);
9 l_sgid varchar2(32000);
10 l_agree varchar2(32000);
11 l_class varchar2(32000);
12 l_cust varchar2(32000);
13 l_curr varchar2(32000);
14 l_curr_suffix varchar2(32000);
15 l_class_from varchar2(32000);
16 l_sg_sg number;
17 l_sg_res number;
18 l_sg_where varchar2(32000);
19 l_agree_where varchar2(32000);
20 l_class_where varchar2(32000);
21 l_cust_where varchar2(32000);
22 l_time_where varchar2(32000);
23 l_col varchar2(32000);
24 l_query varchar2(32000);
25 l_custom_rec bis_query_attributes;
26
27 begin
28
29 -- Get all necessary parameters from PMV
30 for i in 1..p_param.count loop
31
32 if (p_param(i).parameter_name = 'BIS_FXN_NAME') then
33 l_func := p_param(i).parameter_value;
34 end if;
35
36 if (p_param(i).parameter_name = 'ORGANIZATION+JTF_ORG_SALES_GROUP') then
37 l_sgid := p_param(i).parameter_id;
38 end if;
39
40 if (p_param(i).parameter_name = 'ISC_AGREEMENT_TYPE+ISC_AGREEMENT_TYPE') then
41 l_agree := p_param(i).parameter_id;
42 end if;
43
44 if (p_param(i).parameter_name = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') then
45 l_class := p_param(i).parameter_id;
46 end if;
47
48 if (p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS') then
49 l_cust := p_param(i).parameter_id;
50 end if;
51
52 if (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES') then
53 l_curr := p_param(i).parameter_id;
54 end if;
55
56 end loop;
57
58 if (l_curr = '''FII_GLOBAL1''') then
59 l_curr_suffix := 'g';
60 else -- (l_curr = '''FII_GLOBAL2''')
61 l_curr_suffix := 'g1';
62 end if;
63
64 -- Figure out where clauses
65 l_sg_sg := to_number(replace(substr(l_sgid,instr(l_sgid,'.') + 1),''''));
66 l_sg_res := to_number(replace(substr(l_sgid,1,instr(l_sgid,'.') - 1),''''));
67
68 if (l_sg_res is null) then -- when a sales group is chosen
69 l_sg_where := ' and f.sales_grp_id = (&ORGANIZATION+JTF_ORG_SALES_GROUP) ';
70 else -- when the LOV parameter is a Salesrep (no need to go through the SG hierarchy)
71 l_sg_where := ' and f.sales_grp_id = :ISC_SG and f.salesrep_id = :ISC_RES';
72 end if;
73
74 if (l_agree is null) then
75 l_agree_where := '';
76 else
77 l_agree_where := ' and f.agreement_type_id in (&ISC_AGREEMENT_TYPE+ISC_AGREEMENT_TYPE)';
78 end if;
79
80 if (l_class is null) then
81 l_class_where := '';
82 l_class_from := '';
83 else
84 l_class_where := ' and f.customer_id = cc.party_id and cc.class_code in (&FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
85 l_class_from := ', fii_party_mkt_class cc ';
86 end if;
87
88 if (l_cust is null) then
89 l_cust_where := '';
90 else
91 l_cust_where := ' and f.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
92 end if;
93
94 if (l_func = 'ISC_DBI_SAM_TOP_AGR') then
95 l_time_where := ' and &BIS_CURRENT_ASOF_DATE >= f.time_activation_date_id
96 and &BIS_CURRENT_ASOF_DATE < nvl(f.time_effective_end_date_id, trunc(&BIS_CURRENT_ASOF_DATE) + 1)';
97 l_col := 'expiration';
98 elsif (l_func = 'ISC_DBI_SAM_NEW_AGR') then
99 l_time_where := ' and f.time_activation_date_id between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE';
100 l_col := 'expiration';
101 elsif (l_func = 'ISC_DBI_SAM_EXD_AGR') then
102 l_time_where := ' and f.time_expiration_date_id between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE
103 and &BIS_CURRENT_ASOF_DATE < nvl(f.time_termination_date_id, trunc(&BIS_CURRENT_ASOF_DATE)+1)';
104 l_col := 'expiration';
105 elsif (l_func = 'ISC_DBI_SAM_TRM_AGR') then
106 l_time_where := ' and f.time_termination_date_id is not null
107 and f.time_termination_date_id between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE';
108 l_col := 'termination';
109 elsif (l_func = 'ISC_DBI_SAM_EXG_AGR') then
110 l_time_where := ' and &BIS_CURRENT_ASOF_DATE >= f.time_activation_date_id
111 and &BIS_CURRENT_ASOF_DATE < nvl(f.time_effective_end_date_id, trunc(&BIS_CURRENT_ASOF_DATE) + 1)
112 and f.time_expiration_date_id between &BIS_CURRENT_ASOF_DATE and &BIS_CURRENT_EFFECTIVE_END_DATE';
113 l_col := 'expiration';
114 end if;
115
116 l_query := '
117 select oset.isc_attribute_1 ISC_ATTRIBUTE_1,
118 ccv.value ISC_ATTRIBUTE_2,
119 cust.value ISC_ATTRIBUTE_3,
120 sg.group_name ISC_ATTRIBUTE_4,
121 agree.value ISC_ATTRIBUTE_5,
122 ISC_MEASURE_1,
123 ISC_MEASURE_2,
124 ISC_MEASURE_3,
125 ISC_MEASURE_4,
126 ISC_MEASURE_5,
127 ISC_MEASURE_6,
128 ISC_MEASURE_7,
129 ISC_MEASURE_8,
130 oset.isc_attribute_6 ISC_ATTRIBUTE_6,
131 oset.isc_attribute_7 ISC_ATTRIBUTE_7,
132 ''pFunctionName=OKC_REP_SALES_BSA_HEADER_VIEW&mode=view&headerId=''||oset.blanket_header_id||''&moContextOrgId=''||oset.org_id||''&addBreadCrumb=Y&retainAM=Y'' ISC_ATTRIBUTE_8
133 from
134 (
135 select (rank() over (&ORDER_BY_CLAUSE nulls last, isc_attribute_1))-1 rnk,
136 isc_attribute_1,
137 isc_attribute_6,
138 isc_attribute_7,
139 blanket_header_id,
140 org_id,
141 customer_id,
142 sales_grp_id,
143 agreement_type_id,
144 isc_measure_1,
145 isc_measure_2,
146 isc_measure_3,
147 isc_measure_4,
148 sum(isc_measure_1) over () isc_measure_5,
149 sum(isc_measure_2) over () isc_measure_6,
150 sum(isc_measure_3) over () isc_measure_7,
151 sum(isc_measure_4) over () isc_measure_8
152 from
153 (
154 select f.blanket_number ISC_ATTRIBUTE_1,
155 f.time_activation_date_id ISC_ATTRIBUTE_6,
156 f.time_'||l_col||'_date_id ISC_ATTRIBUTE_7,
157 f.blanket_header_id,
158 f.org_id,
159 f.customer_id,
160 f.sales_grp_id,
161 f.agreement_type_id,
162 sum(case when f.order_line_id is null then 0 else 1 end) ISC_MEASURE_1,
163 sum(case when (f.time_fulfilled_date_id <= &BIS_CURRENT_ASOF_DATE)
164 then f.fulfilled_amt_'||l_curr_suffix||' else 0 end) ISC_MEASURE_2,
165 sum(f.commit_prorated_amt_'||l_curr_suffix||')
166 - sum(case when (f.time_fulfilled_date_id <= &BIS_CURRENT_ASOF_DATE)
167 then f.fulfilled_outstand_amt_'||l_curr_suffix||' else 0 end) ISC_MEASURE_3,
168 sum(f.commit_prorated_amt_'||l_curr_suffix||') ISC_MEASURE_4
169 from isc_dbi_bsa_order_lines_f f'||l_class_from||'
170 where f.transaction_phase_code = ''F''
171 and f.commit_prorated_amt_g is not null
172 and f.blanket_line_id is not null
173 and nvl(f.time_termination_date_id, f.time_activation_date_id + 1) >= f.time_activation_date_id
174 '||l_time_where||l_sg_where||l_agree_where||l_class_where||l_cust_where||'
175 group by f.blanket_number,
176 f.blanket_header_id,
177 f.org_id,
178 f.time_activation_date_id,
179 f.time_'||l_col||'_date_id,
180 f.customer_id,
181 f.sales_grp_id,
182 f.agreement_type_id
183 ) ) oset,
184 fii_party_mkt_class cc,
185 fii_partner_mkt_class_v ccv,
186 fii_customers_v cust,
187 jtf_rs_groups_vl sg,
188 isc_agreement_type_v agree
189 where oset.customer_id = cc.party_id
190 and cc.class_code = ccv.id
191 and oset.customer_id = cust.id
192 and oset.sales_grp_id = sg.group_id
193 and oset.agreement_type_id = agree.id
194 and((oset.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
195 &ORDER_BY_CLAUSE nulls last
196 ';
197
198
199 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
200 x_custom_output := bis_query_attributes_tbl();
201
202 l_custom_rec.attribute_name := ':ISC_SG';
203 l_custom_rec.attribute_value := to_char(l_sg_sg);
204 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
205 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
206 x_custom_output.extend;
207 x_custom_output(x_custom_output.count) := l_custom_rec;
208
209 l_custom_rec.attribute_name := ':ISC_RES';
210 l_custom_rec.attribute_value := to_char(l_sg_res);
211 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
212 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
213 x_custom_output.extend;
214 x_custom_output(x_custom_output.count) := l_custom_rec;
215
216 x_custom_sql := l_query;
217
218 end get_sql;
219
220 end isc_dbi_sam_top_agr_pkg;