[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_SHIP_LATE_TP_D_PKG
Source
1 PACKAGE BODY ISC_DBI_SHIP_LATE_TP_D_PKG AS
2 /* $Header: ISCRG91B.pls 120.1 2006/06/22 07:14:54 abhdixi noship $ */
3
4 PROCEDURE GET_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,x_custom_sql OUT NOCOPY VARCHAR2,
5 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
6
7
8 l_stmt VARCHAR2(32000);
9 l_org VARCHAR2(32000);
10 l_org_where VARCHAR2(32000);
11 l_item VARCHAR2(32000);
12 l_item_where VARCHAR2(32000);
13 l_inv_cat VARCHAR2(32000);
14 l_inv_cat_where VARCHAR2(32000);
15 l_cust VARCHAR2(32000);
16 l_cust_where VARCHAR2(32000);
17 l_lang VARCHAR2(10);
18 l_late_promise_flag NUMBER :=1;
19 l_custom_rec BIS_QUERY_ATTRIBUTES ;
20
21 BEGIN
22
23 l_lang := userenv('LANG');
24
25 FOR i IN 1..p_param.COUNT
26 LOOP
27
28 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
29 THEN l_org := p_param(i).parameter_value;
30 END IF;
31
32 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT') THEN
33 l_inv_cat := p_param(i).parameter_value;
34 END IF;
35
36 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG') THEN
37 l_item := p_param(i).parameter_value;
38 END IF;
39
40 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS') THEN
41 l_cust := p_param(i).parameter_value;
42 END IF;
43
44 END LOOP;
45
46
47 IF ( l_org IS NULL OR l_org = 'All' ) THEN
48 l_org_where := '
49 AND (EXISTS
50 (SELECT 1
51 FROM org_access o
52 WHERE o.responsibility_id = fnd_global.resp_id
53 AND o.resp_application_id = fnd_global.resp_appl_id
54 AND o.organization_id = f.inv_org_id)
55 OR EXISTS
56 (SELECT 1
57 FROM mtl_parameters org
58 WHERE org.organization_id = f.inv_org_id
59 AND NOT EXISTS
60 (SELECT 1
61 FROM org_access ora
62 WHERE org.organization_id = ora.organization_id)))';
63
64 ELSE
65 l_org_where := '
66 AND f.inv_org_id = &ORGANIZATION+ORGANIZATION';
67 END IF;
68
69 IF ( l_inv_cat IS NULL OR l_inv_cat = 'All' ) THEN
70 l_inv_cat_where :='';
71 ELSE
72 l_inv_cat_where := '
73 AND f.item_category_id in (&ITEM+ENI_ITEM_INV_CAT)';
74 END IF;
75
76 IF ( l_item IS NULL OR l_item = 'All' ) THEN
77 l_item_where :='';
78 ELSE
79 l_item_where := '
80 AND f.item_id in (&ITEM+ENI_ITEM_ORG)';
81 END IF;
82
83 IF (l_cust IS NULL OR l_cust = 'All') THEN
84 l_cust_where:='';
85 ELSE
86 l_cust_where :='
87 AND f.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
88 END IF;
89
90 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
91 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
92
93 l_stmt := 'SELECT ISC_ATTRIBUTE_2,
94 ISC_MEASURE_1,
95 org.name ISC_ATTRIBUTE_5,
96 cust.value ISC_ATTRIBUTE_3,
97 ISC_ATTRIBUTE_4,
98 ISC_MEASURE_2,ISC_MEASURE_3
99 FROM (select (rank() over (&ORDER_BY_CLAUSE nulls last,ISC_MEASURE_3,ISC_MEASURE_1)) - 1 rnk,
100 customer_id,
101 inv_org_id,
102 ISC_ATTRIBUTE_2,ISC_ATTRIBUTE_4,ISC_MEASURE_1,
103 ISC_MEASURE_2,ISC_MEASURE_3
104 FROM(select customer_id,
105 inv_org_id,
106 order_number ISC_ATTRIBUTE_2,
107 time_shipped_date_id ISC_ATTRIBUTE_4,
108 line_number ISC_MEASURE_1,
109 days_late_promise ISC_MEASURE_2,
110 header_id ISC_MEASURE_3
111 FROM ISC_DBI_FM_0003_MV f
112 WHERE f.time_shipped_date_id between
113 &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE
114 AND f.late_promise_flag = :ISC_LATE_PROMISE_FLAG'
115 ||l_org_where||l_inv_cat_where||l_item_where||l_cust_where||')) a,
116 FII_CUSTOMERS_V cust,
117 HR_ALL_ORGANIZATION_UNITS_TL org
118 WHERE a.customer_id = cust.id
119 AND a.inv_org_id = org.organization_id
120 AND org.language = :ISC_LANG
121 AND ((a.rnk between &START_INDEX and &END_INDEX) OR(&END_INDEX = -1))
122 ORDER BY rnk';
123
124 x_custom_sql := l_stmt;
125
126 l_custom_rec.attribute_name := ':ISC_LATE_PROMISE_FLAG';
127 l_custom_rec.attribute_value := to_char(l_late_promise_flag);
128 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
129 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
130 x_custom_output.EXTEND;
131 x_custom_output(1) := l_custom_rec;
132
133 l_custom_rec.attribute_name := ':ISC_LANG';
134 l_custom_rec.attribute_value := l_lang;
135 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
136 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
137 x_custom_output.EXTEND;
138 x_custom_output(2) := l_custom_rec;
139
140 END get_sql;
141
142 END ISC_DBI_SHIP_LATE_TP_D_PKG ;
143