Oracle Trade Management Queries
1. Get the details of the Accrual type of Trade planning Offers-
SELECT
msib.attribute3,
qpl.list_line_id,
qpl.list_header_id,
qpl.list_line_type_code,
qpl.product_attribute_context,
qpl.product_attr,
qpl.product_attr_val,
qpl.arithmetic_operator,
qpl.operand,
qpl.start_date_active,
qpl.end_date_active,
qpl.product_uom_code,
qpl.price_by_formula_id,
qpl.pricing_attr_value_from,
qpl.pricing_attr,
ardl.estimated_amount_is_max,
ardl.estimated_amount,
ardl.estimated_qty_is_max,
ardl.estimated_qty,
qpl.context,
qpl.attribute1,
qpl.attribute2,
qpl.attribute3,
qpl.attribute4,
qpl.attribute5,
qpl.attribute6,
qpl.attribute7,
qpl.attribute8,
qpl.attribute9,
qpl.attribute10,
qpl.attribute11,
qpl.attribute12,
qpl.attribute13,
qpl.attribute14,
qpl.attribute15,
decode(qpl.product_uom_code,
NULL,
NULL,
ozf_utility_pvt.get_uom_name(qpl.product_uom_code)) uom_name,
qpformula.name formula_name,
decode(ozfoffr.offer_type,
'DEAL',
1,
'ACCRUAL',
1,
0) render_accr_type,
decode(ozfoffr.offer_type,
'DEAL',
1,
'OFFINVOICE',
1,
0) render_disc_type,
fnd_profile.value('QP_ORGANIZATION_ID') inv_org_id,
decode(qpl.list_header_id,
NULL,
0,
1) prod_read_only,
CASE
WHEN ( ozfoffr.offer_type = 'DEAL' )
OR ( ozf_utility_pvt.get_system_status_code(ozfoffr.user_status_id) = 'PENDING' ) THEN
'DisableDtl'
WHEN qpl.list_line_id IS NULL THEN
'DisableDtl'
WHEN nvl(ozfoffr.budget_offer_yn,
'N') = 'Y'
OR ( b.source_system_code <> fnd_profile.value('QP_SOURCE_SYSTEM_CODE') ) THEN
'DisableDtl'
ELSE
'EnableDtl'
END render_dtail,
ozfoffr.offer_type prod_offer_type,
ardl.related_modifier_id,
ardl.modifier_id,
nvl(qpl.accrual_flag, 'N') accrual_flag,
CASE
WHEN qpl.accrual_flag = 'Y' THEN
qpl.arithmetic_operator
ELSE
NULL
END accrual_operator,
CASE
WHEN qpl.accrual_flag = 'Y' THEN
qpl.operand
ELSE
NULL
END accrual_operand,
CASE
WHEN qpl.accrual_flag = 'N' THEN
qpl.arithmetic_operator
ELSE
NULL
END discount_operator,
CASE
WHEN qpl.accrual_flag = 'N' THEN
qpl.operand
ELSE
NULL
END discount_operand,
ardl.object_version_number ardl_obj_version,
qpl.product_attr_value,
ardl.related_deal_lines_id,
qpl.pricing_attr_value_to,
CASE
WHEN nvl(ozfoffr.budget_offer_yn,
'N') = 'Y'
OR ( b.source_system_code <> fnd_profile.value('QP_SOURCE_SYSTEM_CODE') ) THEN
'DisableAddlInfo'
ELSE
'EnableAddlInfo'
END render_addl_info,
qpl.product_attribute_type,
CASE
WHEN qpl.accrual_flag = 'Y' THEN
ozf_utility_pvt.get_lookup_meaning('OZF_QP_ARITHMETIC_OPERATOR',
qpl.arithmetic_operator)
ELSE
NULL
END accrual_operator_meaning,
CASE
WHEN qpl.accrual_flag = 'N' THEN
ozf_utility_pvt.get_lookup_meaning('OZF_QP_ARITHMETIC_OPERATOR',
qpl.arithmetic_operator)
ELSE
NULL
END discount_operator_meaning,
ozf_utility_pvt.get_lookup_meaning('OZF_OFFER_TIER_TYPE',
qpl.list_line_type_code) tier_type_meaning,
ozf_utility_pvt.get_lookup_meaning('OZF_QP_VOLUME_TYPE',
qpl.pricing_attr) vol_type_meaning,
CASE
WHEN ozf_utility_pvt.get_system_status_code(ozfoffr.user_status_id) = 'PENDING' THEN
1
ELSE
0
END disc_read_only,
qpl.print_on_invoice_flag,
qpl.pricing_group_sequence,
qpl.pricing_phase_id,
qpl.incompatibility_grp_code,
qpl.product_precedence,
'RemoveDisabled' render_remove,
'TierDisabled' tier_detail
FROM
qp_modifier_summary_v qpl,
ozf_related_deal_lines ardl,
qp_list_headers_b b,
qp_price_formulas_vl qpformula,
ozf_offers ozfoffr,
mtl_system_items_b msib,
hr_operating_units hou,
org_organization_definitions ood
WHERE
qpl.list_header_id = &qp_list_header_id
AND ozfoffr.offer_code = &offer_code
AND qpl.list_line_id = ardl.modifier_id (+)
AND excluder_flag = 'N'
AND qpl.list_header_id = b.list_header_id
AND qpl.list_header_id = ozfoffr.qp_list_header_id
AND qpl.price_by_formula_id = qpformula.price_formula_id (+)
AND msib.inventory_item_id = qpl.product_attr_val
AND msib.organization_id = ood.organization_id
AND ood.operating_unit = hou.organization_id
ORDER BY
qpl.list_line_id
-------------------------------------------------------------------------
2. Get the Item details from the Offers for Accrual type -
SELECT DISTINCT
ozfoffr.offer_code
,msib.*
FROM
qp_modifier_summary_v qpl,
ozf_offers ozfoffr,
mtl_system_items_b msib,
org_organization_definitions ood
WHERE
ozfoffr.offer_code = &offer_code
AND excluder_flag = 'N'
AND qpl.list_header_id = ozfoffr.qp_list_header_id
AND msib.inventory_item_id = qpl.product_attr_val
AND msib.organization_id = ood.organization_id
AND ood.organization_id = &organization_id
AND ozfoffr.org_id = ood.operating_unit
AND msib.attribute3 IS NOT NULL;
-------------------------------------------------------------------------------------------
3. Get the Item details from the Offers for Lumpsum type -
SELECT distinct
oo.offer_code, msib.*
FROM
ozf_funds_utilized_all_b ofua,
ozf_offers oo,
mtl_system_items_b msib,
org_organization_definitions ood
WHERE
ofua.plan_id = oo.qp_list_header_id
AND offer_code = &OFFER_CODE
AND msib.inventory_item_id = ofua.product_id
AND msib.organization_id = ood.organization_id
AND ood.operating_unit = &OPERATING_UNIT --hou.organization_id
and oo.org_id = ood.operating_unit
and oo.org_id= ofua.org_id
AND msib.attribute3 IS NOT NULL;
Comments
Post a Comment