[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_SHIP_LATE_D_PKG
Source
1 PACKAGE BODY ISC_DBI_SHIP_LATE_D_PKG AS
2 /* $Header: ISCRG68B.pls 120.1 2005/10/17 16:49:13 hprathur 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_schedule_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 CURRENCY,
94 ISC_ATTRIBUTE_2,
95 ISC_MEASURE_1,
96 org.name ISC_ATTRIBUTE_5,
97 cust.value ISC_ATTRIBUTE_3,
98 ISC_ATTRIBUTE_4,
99 ISC_MEASURE_2,
100 ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,ISC_MEASURE_6
101 FROM (select (rank() over (&ORDER_BY_CLAUSE nulls last,ISC_MEASURE_2,ISC_MEASURE_1)) - 1 rnk,
102 customer_id,
103 inv_org_id,
104 ISC_ATTRIBUTE_2,ISC_ATTRIBUTE_4,ISC_MEASURE_1,
105 ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,ISC_MEASURE_6,CURRENCY
106 FROM(select customer_id,
107 inv_org_id,
108 order_number ISC_ATTRIBUTE_2,
109 time_shipped_date_id ISC_ATTRIBUTE_4,
110 line_number ISC_MEASURE_1,
111 header_id ISC_MEASURE_2,
112 days_late ISC_MEASURE_3,
113 null ISC_MEASURE_4, -- obsolted item from DBI 5.0
114 null ISC_MEASURE_5, -- obsolted item from DBI 5.0
115 null ISC_MEASURE_6, -- obsolted item from DBI 5.0
116 null CURRENCY -- obsolted item from DBI 5.0
117 FROM ISC_DBI_FM_0003_MV f
118 WHERE f.time_shipped_date_id between
119 &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE
120 AND f.late_schedule_flag = :ISC_LATE_SCHEDULE_FLAG'
121 ||l_org_where||l_inv_cat_where||l_item_where||l_cust_where||')) a,
122 FII_CUSTOMERS_V cust,
123 HR_ALL_ORGANIZATION_UNITS_TL org
124 WHERE a.customer_id = cust.id
125 AND a.inv_org_id = org.organization_id
126 AND org.language = :ISC_LANG
127 AND ((a.rnk between &START_INDEX and &END_INDEX) OR(&END_INDEX = -1))
128 ORDER BY rnk';
129
130 x_custom_sql := l_stmt;
131
132 l_custom_rec.attribute_name := ':ISC_LATE_SCHEDULE_FLAG';
133 l_custom_rec.attribute_value := to_char(l_late_schedule_flag);
134 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
135 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
136 x_custom_output.EXTEND;
137 x_custom_output(1) := l_custom_rec;
138
139 l_custom_rec.attribute_name := ':ISC_LANG';
140 l_custom_rec.attribute_value := l_lang;
141 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
142 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
143 x_custom_output.EXTEND;
144 x_custom_output(2) := l_custom_rec;
145
146 END get_sql;
147
148 END ISC_DBI_SHIP_LATE_D_PKG ;
149