[Home] [Help]
SELECT /* Fix for bug # 5024093 */
'ORDERS' umarker,
ord.time_booked_date_id booked_date_id,
ord.header_id,
NULL order_id,
ord.minisite_id,
ord.currency_cd_f,
cat.imm_child_id category_id,
cat.parent_id parent_category_id,
cat.top_node_flag,
cat.item_assgn_flag leaf_node,
ord.resource_flag assist_flag,
SUM (ord.booked_amt_g) booked_amt_g,
SUM (ord.booked_amt_g1) booked_amt_g1,
SUM (ord.booked_amt_f) booked_amt_f,
SUM (ord.booked_list_amt_g) list_amt_g,
SUM (ord.booked_list_amt_g1) list_amt_g1,
SUM (ord.booked_list_amt_f) list_amt_f,
SUM (ord.booked_list_amt_g - ord.booked_amt_g
) discount_amt_g,
SUM (ord.booked_list_amt_g1 - ord.booked_amt_g1
) discount_amt_g1,
SUM (ord.booked_list_amt_f - ord.booked_amt_f
) discount_amt_f,
COUNT (ord.booked_amt_g
) d_booked_amt_g_cnt,
COUNT (ord.booked_amt_g1
) d_booked_amt_g1_cnt,
COUNT (ord.booked_amt_f
) d_booked_amt_f_cnt,
COUNT (ord.booked_list_amt_g
) d_list_amt_g_cnt,
COUNT (ord.booked_list_amt_g1
) d_list_amt_g1_cnt,
COUNT (ord.booked_list_amt_f
) d_list_amt_f_cnt,
COUNT (ord.booked_list_amt_g - ord.booked_amt_g
) d_discount_amt_g_cnt,
COUNT (ord.booked_list_amt_g1 - ord.booked_amt_g1
) d_discount_amt_g1_cnt,
COUNT (ord.booked_list_amt_f - ord.booked_amt_f
) d_discount_amt_f_cnt,
COUNT (*) d_tot_cnt,
GROUPING_ID (ord.time_booked_date_id,
ord.header_id,
ord.resource_flag,
ord.currency_cd_f,
ord.minisite_id,
cat.parent_id,
cat.top_node_flag,
cat.item_assgn_flag,
cat.imm_child_id
) d_gid
FROM APPS.IBE_BI_ORDERBOM_MV ord,
INV.MTL_DEFAULT_CATEGORY_SETS mtl,
ENI.ENI_OLTP_ITEM_STAR item,
ENI.ENI_DENORM_HIERARCHIES cat
WHERE ord.published_quote_flag = 'N'
AND ord.inventory_item_id = item.inventory_item_id
AND ord.organization_id = item.organization_id
AND item.vbh_category_id = cat.child_id
AND cat.object_type = 'CATEGORY_SET'
AND cat.object_id = mtl.category_set_id
AND mtl.functional_area_id = 11
AND cat.dbi_flag = 'Y'
AND ( cat.parent_id <> cat.child_id
OR (top_node_flag = 'Y' AND leaf_node_flag = 'Y')
)
GROUP BY ord.time_booked_date_id,
ord.header_id,
ord.resource_flag,
ord.minisite_id,
ord.currency_cd_f,
cat.parent_id,
cat.top_node_flag,
cat.item_assgn_flag,
ROLLUP (cat.imm_child_id)
UNION ALL
SELECT 'CARTS' umarker,
TRUNC (qot.quote_creation_date) booked_date_id,
qot.quote_header_id header_id,
qot.order_id,
qot.minisite_id_hdr minisite_id,
qot.functional_currency currency_cd_f,
cat.imm_child_id category_id,
cat.parent_id parent_category_id,
cat.top_node_flag,
cat.item_assgn_flag leaf_node,
DECODE (qot.resource_id, NULL, 'N', 'Y'
) assist_flag,
SUM ( NVL (qot.line_quote_value, 0)
* NVL (qot.reporting_conversion_rate, 0)
) booked_amt_g,
SUM (NVL (qot.line_quote_value, 0) * NVL (qot.sec_conversion_rate, 0)
) booked_amt_g1,
SUM ( NVL (qot.line_quote_value, 0)
* NVL (qot.functional_conversion_rate, 0)
) booked_amt_f,
0 list_amt_g,
0 list_amt_g1,
0 list_amt_f,
0 discount_amt_g,
0 discount_amt_g1,
0 discount_amt_f,
COUNT ( NVL (qot.line_quote_value, 0)
* NVL (qot.reporting_conversion_rate, 0)
) d_booked_amt_g_cnt,
COUNT ( NVL (qot.line_quote_value, 0)
* NVL (qot.sec_conversion_rate, 0)
) d_booked_amt_g1_cnt,
COUNT ( NVL (qot.line_quote_value, 0)
* NVL (qot.functional_conversion_rate, 0)
) d_booked_amt_f_cnt,
0 d_list_amt_g_cnt,
0 d_list_amt_g1_cnt,
0 d_list_amt_f_cnt,
0 d_discount_amt_g_cnt,
0 d_discount_amt_g1_cnt,
0 d_discount_amt_f_cnt,
COUNT (*) d_tot_cnt,
GROUPING_ID (TRUNC (qot.quote_creation_date),
qot.quote_header_id,
qot.order_id,
DECODE (qot.resource_id, NULL, 'N', 'Y'),
qot.functional_currency,
qot.minisite_id_hdr,
cat.parent_id,
cat.top_node_flag,
cat.item_assgn_flag,
cat.imm_child_id
) d_gid
FROM ASO.ASO_BI_QUOTE_LINES_ALL qot,
INV.MTL_DEFAULT_CATEGORY_SETS mtl,
ENI.ENI_OLTP_ITEM_STAR item,
ENI.ENI_DENORM_HIERARCHIES cat
WHERE qot.minisite_id_hdr IS NOT NULL
AND DECODE (UPPER (SUBSTR (qot.quote_source_code, 1, 6)),
'ISTORE', 'N','Y' ) = 'N'
AND qot.top_inventory_item_id = item.inventory_item_id
AND qot.top_organization_id = item.organization_id
AND qot.line_category_code = 'ORDER'
AND item.vbh_category_id = cat.child_id
AND cat.object_type = 'CATEGORY_SET'
AND cat.object_id = mtl.category_set_id
AND mtl.functional_area_id = 11
AND cat.dbi_flag = 'Y'
AND qot.charge_periodicity_code IS NULL
AND ( cat.parent_id <> cat.child_id
OR (top_node_flag = 'Y' AND leaf_node_flag = 'Y')
)
GROUP BY TRUNC (qot.quote_creation_date),
qot.quote_header_id,
qot.order_id,
DECODE (qot.resource_id, NULL, 'N', 'Y'),
qot.minisite_id_hdr,
qot.functional_currency,
cat.parent_id,
cat.top_node_flag,
cat.item_assgn_flag,
ROLLUP (cat.imm_child_id)
|
|
|
|