[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_SAM_AGR_ORD_DTL_PKG
Source
1 package body isc_dbi_sam_agr_ord_dtl_pkg as
2 /* $Header: ISCRGCLB.pls 120.6 2006/01/04 16:07:22 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_sgid varchar2(32000);
9 l_class varchar2(32000);
10 l_cust varchar2(32000);
11 l_prod_cat varchar2(32000);
12 l_item varchar2(32000);
13 l_curr varchar2(32000);
14 l_curr_suffix varchar2(32000);
15 l_sg_sg number;
16 l_sg_res number;
17 l_sg_where varchar2(32000);
18 l_class_from varchar2(32000);
19 l_class_where varchar2(32000);
20 l_cust_where varchar2(32000);
21 l_prod_cat_from varchar2(32000);
22 l_prod_cat_where varchar2(32000);
23 l_item_where 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 = 'ORGANIZATION+JTF_ORG_SALES_GROUP') then
33 l_sgid := p_param(i).parameter_id;
34 end if;
35
36 if (p_param(i).parameter_name = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') then
37 l_class := p_param(i).parameter_id;
38 end if;
39
40 if (p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS') then
41 l_cust := p_param(i).parameter_id;
42 end if;
43
44 if (p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT') then
45 l_prod_cat := p_param(i).parameter_id;
46 end if;
47
48 if (p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG') then
49 l_item := 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 sc.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 sc.sales_grp_id = :ISC_SG and sc.resource_id = :ISC_RES';
72 end if;
73
74 if (l_class is null) then
75 l_class_where := '';
76 l_class_from := '';
77 else
78 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)';
79 l_class_from := ', fii_party_mkt_class cc ';
80 end if;
81
82 if (l_cust is null) then
83 l_cust_where := '';
84 else
85 l_cust_where := ' and f.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
86 end if;
87
88 if (l_prod_cat is null and l_item is null) then
89 l_prod_cat_from := '';
90 l_prod_cat_where := '';
91 l_item_where := '';
92 elsif (l_item is null) then
93 l_prod_cat_from := ',
94 eni_denorm_hierarchies eni_cat,
95 mtl_default_category_sets mdcs';
96 l_prod_cat_where := '
97 AND star.vbh_category_id = eni_cat.child_id
98 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
99 AND eni_cat.dbi_flag = ''Y''
100 AND eni_cat.object_type = ''CATEGORY_SET''
101 AND eni_cat.object_id = mdcs.category_set_id
102 AND mdcs.functional_area_id = 11';
103 l_item_where := '';
104 else
105 l_prod_cat_from := '';
106 l_prod_cat_where := '';
107 l_item_where := '
108 AND star.master_id IN (&ITEM+ENI_ITEM_ORG)';
109 end if;
110
111 l_query := '
112 select oset.isc_attribute_1 ISC_ATTRIBUTE_1,
113 oset.isc_attribute_2 ISC_ATTRIBUTE_2,
114 oset.isc_attribute_3 ISC_ATTRIBUTE_3,
115 oset.isc_attribute_4 ISC_ATTRIBUTE_4,
116 cust.value ISC_ATTRIBUTE_5,
117 ccv.value ISC_ATTRIBUTE_6,
118 item.value ISC_ATTRIBUTE_7,
119 item.description ISC_ATTRIBUTE_8,
120 sg.group_name ISC_ATTRIBUTE_9,
121 sr.resource_name ISC_ATTRIBUTE_10,
122 oset.isc_measure_1 ISC_MEASURE_1,
123 oset.isc_measure_2 ISC_MEASURE_2,
124 oset.isc_attribute_11 ISC_ATTRIBUTE_11,
125 ''pFunctionName=OKC_REP_SALES_BSA_HEADER_VIEW&mode=view&headerId=''||oset.blanket_header_id||''&moContextOrgId=''||oset.org_id||''&addBreadCrumb=Y&retainAM=Y'' ISC_ATTRIBUTE_12
126 from
127 (select (rank() over (&ORDER_BY_CLAUSE nulls last, isc_attribute_1, isc_attribute_2))-1 rnk,
128 blanket_header_id, org_id, customer_id, item_id, sales_grp_id, resource_id,
129 isc_attribute_1,isc_attribute_2,isc_attribute_3,isc_attribute_4,isc_measure_1,isc_measure_2,
130 isc_attribute_11
131 from
132 (select f.order_number isc_attribute_1,
133 f.line_number isc_attribute_2,
134 f.blanket_number isc_attribute_3,
135 f.time_fulfilled_date_id isc_attribute_4,
136 f.order_line_header_id isc_attribute_11,
137 f.blanket_header_id,
138 f.org_id,
139 f.customer_id,
140 star.id item_id,
141 sc.sales_grp_id,
142 sc.resource_id,
143 f.fulfilled_amt_'||l_curr_suffix||' * sc.sales_credit_percent / 100 isc_measure_1,
144 sum(f.fulfilled_amt_'||l_curr_suffix||' * sc.sales_credit_percent / 100)over() isc_measure_2
145 from isc_dbi_bsa_order_lines_f f,
146 isc_sales_credits_f sc,
147 eni_oltp_item_star star'||l_prod_cat_from||l_class_from||'
148 where f.time_fulfilled_date_id between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE
149 and f.order_line_id = sc.line_id
150 and f.inventory_item_id = star.inventory_item_id
151 and f.item_inv_org_id = star.organization_id
152 and f.transaction_phase_code = ''F''
153 and f.commit_prorated_amt_g is not null
154 and f.blanket_line_id is not null
155 and nvl(f.time_termination_date_id, f.time_activation_date_id + 1) >= f.time_activation_date_id
156 '||l_sg_where||l_class_where||l_cust_where||l_prod_cat_where||l_item_where||'
157 ) )oset,
158 fii_party_mkt_class cc,
159 fii_partner_mkt_class_v ccv,
160 fii_customers_v cust,
161 eni_item_org_v item,
162 jtf_rs_groups_vl sg,
163 jtf_rs_resource_extns_vl sr
164 where oset.customer_id = cc.party_id
165 and cc.class_code = ccv.id
166 and oset.customer_id = cust.id
167 and oset.item_id = item.id
168 and oset.sales_grp_id = sg.group_id
169 and oset.resource_id = sr.resource_id
170 and((oset.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
171 &ORDER_BY_CLAUSE nulls last
172 ';
173
174 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
175 x_custom_output := bis_query_attributes_tbl();
176
177 l_custom_rec.attribute_name := ':ISC_SG';
178 l_custom_rec.attribute_value := to_char(l_sg_sg);
179 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
180 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
181 x_custom_output.extend;
182 x_custom_output(x_custom_output.count) := l_custom_rec;
183
184 l_custom_rec.attribute_name := ':ISC_RES';
185 l_custom_rec.attribute_value := to_char(l_sg_res);
186 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
187 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
188 x_custom_output.extend;
189 x_custom_output(x_custom_output.count) := l_custom_rec;
190
191 x_custom_sql := l_query;
192
193 end get_sql;
194
195 end isc_dbi_sam_agr_ord_dtl_pkg;