Introduction
This Query will help to Extract the Customer Wise Accrual Details in Trade Management in Oracle Apps.
SQL Query:
SELECT DECODE (qualifier_context,
‘CUSTOMER’, (SELECT b.party_name
FROM hz_cust_accounts a, hz_parties b
WHERE cust_account_id = qq.qualifier_attr_value
AND a.party_id = b.party_id)
) “Customer Name”,
DECODE (qualifier_context,
‘CUSTOMER’, (SELECT a.account_number
FROM hz_cust_accounts a, hz_parties b
WHERE cust_account_id = qq.qualifier_attr_value
AND a.party_id = b.party_id)
) “Customer Number”,
DECODE (qualifier_context,
‘CUSTOMER’, qq.qualifier_attr_value
) “Customer id”,
qms.product_attr_val, qms.product_attr_value product_line,
qms.product_uom_code, oft.description, act.category_name,
qlhb.start_date_active_second, qlhb.end_date_active_second,
qms.operand discount, oft.short_name,
INITCAP (TRIM (REPLACE (TRANSLATE (oft.description, ‘1234567890%.’,
‘ ‘),
”,
”
)
)
) accrual_type,
ofu.org_id
FROM ozf_funds_all_b ofu,
ams_act_products aap,
ozf_funds_all_b ofu1,
ozf_funds_all_tl oft,
ozf_funds_all_tl oft1,
ams_categories_tl act,
mtl_categories_b mc,
qp_list_headers qlhb,
ozf_offers oo,
qp_modifier_summary_v qms,
oe_transaction_types_tl ol,
qp_qualifiers qq
WHERE ofu.end_date_active IS NOT NULL
AND qlhb.end_date_active_second IS NOT NULL
AND qms.operand <> 0
AND qms.excluder_flag <> ‘Y’
AND ofu.status_code = ‘ACTIVE’
AND ofu.fund_id = aap.act_product_used_by_id(+)
AND aap.arc_act_product_used_by(+) = ‘FUND’
AND ofu1.fund_id(+) = ofu.parent_fund_id
AND ofu.fund_id = oft.fund_id
AND ofu1.fund_id = oft1.fund_id(+)
AND oft.LANGUAGE = ‘US’
AND oft1.LANGUAGE(+) = ‘US’
AND ofu.category_id = act.category_id
AND act.LANGUAGE = ‘US’
AND aap.category_id = mc.category_id(+)
AND oo.qp_list_header_id = ofu.plan_id
AND oo.qp_list_header_id = qlhb.list_header_id
AND qms.list_header_id = qlhb.list_header_id
–AND amt.media_id = oo.activity_media_id (+)
AND qq.list_header_id = qlhb.list_header_id(+)
AND ol.transaction_type_id(+) = qq.qualifier_attr_value
AND qms.end_date_active IS NULL
Got any queries?
Do drop a note by writing us at Venkatesh.b@doyensys.com or use the comment section below to ask your question
