Introduction:
This script will help to extract the price adjustment not populated in order lines for the Customer have the Accrual in Trade Management
Script:
1. Modifiers Exist For Customer, But Not Applied On Sales Order Lines
——————————————————————————-
SELECT oh.creation_date, hca.account_number customer_number,
hp.party_name customer_name, oh.order_number,
ol.line_number || ‘.’ || ol.shipment_number line_number,
msi.segment1 item_number, msi.description item_description,
ordered_item, TRUNC (oh.ordered_date) ordered_date,
TRUNC (ol.schedule_ship_date) schedule_ship_date, ol.ordered_quantity,
ol.order_quantity_uom, ol.unit_selling_price, ol.invoiced_quantity,
oh.flow_status_code header_status, ol.flow_status_code line_status,
oh.header_id, ol.line_id
FROM oe_order_headers_all oh,
oe_order_lines_all ol,
mtl_system_items_b msi,
hz_cust_accounts hca,
hz_parties hp
WHERE oh.header_id = ol.header_id
AND ol.inventory_item_id = msi.inventory_item_id
AND ol.ship_from_org_id = msi.organization_id
AND TO_CHAR (oh.ordered_date, ‘RRRR’) = ‘2020’
AND TO_CHAR (ol.schedule_ship_date, ‘RRRR’) = ‘2021’
AND oh.org_id = :p_org_id
AND hca.cust_account_id = oh.sold_to_org_id
AND hp.party_id = hca.party_id
AND oh.flow_status_code <> ‘ENTERED’
AND hca.account_number IN
(‘16144’)
AND NOT EXISTS (
SELECT 1
FROM oe_price_adjustments_v pa
WHERE pa.header_id = oh.header_id
AND pa.line_id = ol.line_id
AND list_line_type_code = ‘DIS’
AND source_system_code = ‘QP’ –identify the Manual Adj
);
2. Modifiers Exist For Customer, Applied the adjustment but not all modifier applied
————————————————————————————-
SELECT *
FROM (SELECT oh.creation_date, hca.account_number customer_number,
hp.party_name customer_name, oh.order_number,
ol.line_number || ‘.’ || ol.shipment_number line_number,
msi.segment1 item_number, msi.description item_description,
ordered_item, TRUNC (oh.ordered_date) ordered_date,
TRUNC (ol.schedule_ship_date) schedule_ship_date,
ol.ordered_quantity, ol.order_quantity_uom,
ol.unit_selling_price, ol.invoiced_quantity,
oh.flow_status_code header_status,
ol.flow_status_code line_status, oh.header_id, ol.line_id,
pa.list_header_id, pa.list_line_id,
(SELECT COUNT (ofu.fund_number)
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 qq.list_header_id = qlhb.list_header_id(+)
AND ol.transaction_type_id(+) = qq.qualifier_attr_value
AND qms.end_date_active IS NULL
AND qq.qualifier_attr_value = TO_CHAR (hca.cust_account_id)
AND ofu.start_date_active <= TRUNC (ol.schedule_ship_date)
AND ( ofu.end_date_active IS NULL
OR ofu.end_date_active > TRUNC (ol.schedule_ship_date)
)) modifier_count,
(SELECT COUNT (*)
FROM oe_price_adjustments_v pa
WHERE pa.header_id = oh.header_id
AND pa.line_id = ol.line_id
AND pa.source_system_code = ‘QP’) price_adj_count
FROM oe_order_headers_all oh,
oe_order_lines_all ol,
mtl_system_items_b msi,
hz_cust_accounts hca,
hz_parties hp,
oe_price_adjustments_v pa
WHERE oh.header_id = ol.header_id
AND ol.inventory_item_id = msi.inventory_item_id
AND ol.ship_from_org_id = msi.organization_id
AND TO_CHAR (oh.ordered_date, ‘RRRR’) = ‘2020’
AND TO_CHAR (ol.schedule_ship_date, ‘RRRR’) = ‘2021’
AND oh.org_id = :P_ORG_ID
AND hca.cust_account_id = oh.sold_to_org_id
AND hp.party_id = hca.party_id
AND oh.flow_status_code <> ‘ENTERED’
AND pa.header_id = oh.header_id
AND pa.line_id = ol.line_id
AND hca.account_number IN
(‘1000’)
AND EXISTS (
SELECT 1
FROM oe_price_adjustments_v opa
WHERE opa.header_id = oh.header_id
AND opa.line_id = ol.line_id
AND opa.list_line_type_code = ‘DIS’
AND opa.source_system_code = ‘QP’))
WHERE modifier_count <> price_adj_count;
3. Modifiers Exist For Customer, Applied the adjustment lines with all modifier
————————————————————————————-
SELECT *
FROM (SELECT oh.creation_date, hca.account_number customer_number,
hp.party_name customer_name, oh.order_number,
ol.line_number || ‘.’ || ol.shipment_number line_number,
msi.segment1 item_number, msi.description item_description,
ordered_item, TRUNC (oh.ordered_date) ordered_date,
TRUNC (ol.schedule_ship_date) schedule_ship_date,
ol.ordered_quantity, ol.order_quantity_uom,
ol.unit_selling_price, ol.invoiced_quantity,
oh.flow_status_code header_status,
ol.flow_status_code line_status, oh.header_id, ol.line_id,
pa.list_header_id, pa.list_line_id,
(SELECT COUNT (ofu.fund_number)
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 qq.list_header_id = qlhb.list_header_id(+)
AND ol.transaction_type_id(+) = qq.qualifier_attr_value
AND qms.end_date_active IS NULL
AND qq.qualifier_attr_value = TO_CHAR (hca.cust_account_id)
AND ofu.start_date_active <= TRUNC (ol.schedule_ship_date)
AND ( ofu.end_date_active IS NULL
OR ofu.end_date_active > TRUNC (ol.schedule_ship_date)
)) modifier_count,
(SELECT COUNT (*)
FROM oe_price_adjustments_v pa
WHERE pa.header_id = oh.header_id
AND pa.line_id = ol.line_id
AND pa.source_system_code = ‘QP’) price_adj_count
FROM oe_order_headers_all oh,
oe_order_lines_all ol,
mtl_system_items_b msi,
hz_cust_accounts hca,
hz_parties hp,
oe_price_adjustments_v pa
WHERE oh.header_id = ol.header_id
AND ol.inventory_item_id = msi.inventory_item_id
AND ol.ship_from_org_id = msi.organization_id
AND TO_CHAR (oh.ordered_date, ‘RRRR’) = ‘2020’
AND TO_CHAR (ol.schedule_ship_date, ‘RRRR’) = ‘2021’
AND oh.org_id = :P_ORG_ID
AND hca.cust_account_id = oh.sold_to_org_id
AND hp.party_id = hca.party_id
AND oh.flow_status_code <> ‘ENTERED’
AND pa.header_id = oh.header_id
AND pa.line_id = ol.line_id
AND hca.account_number IN
(‘1000’)
AND EXISTS (
SELECT 1
FROM oe_price_adjustments_v opa
WHERE opa.header_id = oh.header_id
AND opa.line_id = ol.line_id
AND opa.list_line_type_code = ‘DIS’
AND opa.source_system_code = ‘QP’))
WHERE modifier_count = price_adj_count;
Got any queries?
Do drop a note by writing us at Venkatesh.b@staging.doyensys.com or use the comment section below to ask your questions
