[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_BACKORDER_DETAIL_PKG
Source
1 PACKAGE BODY ISC_DBI_BACKORDER_DETAIL_PKG AS
2 /* $Header: ISCRGAXB.pls 120.0 2005/05/25 17:22:25 appldev noship $ */
3
4
5 PROCEDURE Get_Sql ( p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
6 x_custom_sql OUT NOCOPY VARCHAR2,
7 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
8
9
10 l_sql_stmt VARCHAR2(10000);
11 l_stmt VARCHAR2(10000);
12 l_outer_sql VARCHAR2(10000);
13 l_inv_org VARCHAR2(10000);
14 l_inv_org_where VARCHAR2(10000);
15 l_item VARCHAR2(10000);
16 l_item_where VARCHAR2(10000);
17 l_inv_cat VARCHAR2(10000);
18 l_inv_cat_where VARCHAR2(10000);
19 l_cust VARCHAR2(10000);
20 l_cust_where VARCHAR2(10000);
21 l_lang VARCHAR2(10);
22 l_custom_rec BIS_QUERY_ATTRIBUTES;
23
24 BEGIN
25
26 FOR i IN 1..p_param.COUNT
27 LOOP
28
29 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
30 THEN l_inv_org := p_param(i).parameter_value;
31 END IF;
32
33 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT')
34 THEN l_inv_cat := p_param(i).parameter_value;
35 END IF;
36
37 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
38 THEN l_item := p_param(i).parameter_value;
39 END IF;
40
41 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
42 THEN l_cust := p_param(i).parameter_value;
43 END IF;
44
45 END LOOP;
46
47 IF(l_inv_org IS NULL OR l_inv_org = 'All')
48 THEN l_inv_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 = mv.inv_org_id)
55 OR EXISTS
56 (SELECT 1
57 FROM mtl_parameters org
58 WHERE org.organization_id = mv.inv_org_id
59 AND NOT EXISTS
60 (SELECT 1
61 FROM org_access ora
62 WHERE org.organization_id = ora.organization_id)))';
63 ELSE l_inv_org_where := '
64 AND mv.inv_org_id = &ORGANIZATION+ORGANIZATION';
65 END IF;
66
67 IF(l_inv_cat IS NULL OR l_inv_cat = 'All')
68 THEN l_inv_cat_where := '';
69 ELSE l_inv_cat_where := '
70 AND mv.item_category_id IN (&ITEM+ENI_ITEM_INV_CAT)';
71 END IF;
72
73 IF(l_item IS NULL OR l_item = 'All')
74 THEN l_item_where := '';
75 ELSE l_item_where := '
76 AND mv.item_id IN (&ITEM+ENI_ITEM_ORG)';
77 END IF;
78
79 IF (l_cust IS NULL OR l_cust = 'All')
80 THEN l_cust_where :='';
81 ELSE l_cust_where :='
82 AND mv.customer_id IN (&CUSTOMER+FII_CUSTOMERS)';
83 END IF;
84
85 l_lang := USERENV('LANG');
86
87 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
88 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
89
90 l_outer_sql:= 'ISC_ATTRIBUTE_1, ISC_ATTRIBUTE_2, ISC_ATTRIBUTE_7, ISC_ATTRIBUTE_8,
91 ISC_MEASURE_1, ISC_MEASURE_2, ISC_MEASURE_3, ISC_MEASURE_4';
92
93 l_sql_stmt := 'SELECT mv.customer_id CUSTOMER_ID,
94 mv.inv_org_id INV_ORG_ID,
95 mv.item_id ITEM_ID,
96 mv.uom UOM,
97 mv.order_number ISC_ATTRIBUTE_1, -- Order Number
98 mv.line_number ISC_ATTRIBUTE_2, -- Line Number
99 mv.time_request_date_id ISC_ATTRIBUTE_7, -- Request Date
100 mv.time_schedule_date_id ISC_ATTRIBUTE_8, -- Schedule Date
101 mv.backorder_qty ISC_MEASURE_1, -- Backordered Quantity
102 mv.days_late_request ISC_MEASURE_2, -- Days Late to Request
103 mv.days_late_schedule ISC_MEASURE_3, -- Days Late to Schedule
104 mv.header_id ISC_MEASURE_4 -- Header ID
105 FROM ISC_DBI_FM_0007_MV mv,
106 (SELECT max(time_snapshot_date_id) DAY
107 FROM ISC_DBI_FM_0007_MV mv
108 WHERE mv.time_snapshot_date_id BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
109 AND &BIS_CURRENT_ASOF_DATE
110 ) a
111 WHERE mv.time_snapshot_date_id = a.day'
112 ||l_inv_org_where||l_cust_where||l_inv_cat_where||l_item_where;
113
114 l_stmt := '
115 SELECT ISC_ATTRIBUTE_1, ISC_ATTRIBUTE_2,
116 org.name ISC_ATTRIBUTE_9,
117 cust.value ISC_ATTRIBUTE_3, -- Customer
118 items.value ISC_ATTRIBUTE_4, -- Item
119 items.description ISC_ATTRIBUTE_5, -- Description
120 mtl.unit_of_measure ISC_ATTRIBUTE_6, -- UOM
121 ISC_MEASURE_1, ISC_ATTRIBUTE_7, ISC_ATTRIBUTE_8, ISC_MEASURE_2, ISC_MEASURE_3, ISC_MEASURE_4
122 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE NULLS LAST, isc_measure_4, isc_attribute_2))-1 RNK,
123 customer_id, item_id, uom, inv_org_id,
124 '||l_outer_sql||'
125 FROM ('||l_sql_stmt||')
126 ) c,
127 FII_CUSTOMERS_V cust,
128 ENI_ITEM_ORG_V items,
129 MTL_UNITS_OF_MEASURE_TL mtl,
130 HR_ALL_ORGANIZATION_UNITS_TL org
131 WHERE c.customer_id = cust.id
132 AND c.item_id = items.id
133 AND c.uom = mtl.uom_code
134 AND mtl.language = :ISC_LANG
135 AND c.inv_org_id = org.organization_id
136 AND org.language = :ISC_LANG
137 AND ((c.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
138 ORDER BY rnk';
139
140 x_custom_sql := l_stmt;
141
142 l_custom_rec.attribute_name := ':ISC_LANG';
143 l_custom_rec.attribute_value := l_lang;
144 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
145 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
146 x_custom_output.extend;
147 x_custom_output(1) := l_custom_rec;
148
149 END Get_Sql;
150
151 END ISC_DBI_BACKORDER_DETAIL_PKG ;
152