[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT precedence, offer_market_option_id
FROM ozf_offr_market_options
WHERE group_number = p_group_no
AND qp_list_header_id = p_list_header_id;
SELECT combine_schedule_flag, volume_tracking_level_code
FROM ozf_offr_market_options
WHERE offer_id = p_offer_id
AND offer_market_option_id = p_offer_market_option_id;
SELECT offer_discount_line_id
FROM ozf_qp_discounts
WHERE list_line_id = p_list_line_id;
SELECT group_no, volume_track_type, combine_schedule_yn, pbh_line_id, prod_attribute, prod_attr_value
FROM ozf_order_group_prod
WHERE order_line_id = l_order_line_id
AND indirect_flag = p_indirect_flag
AND offer_id = p_offer_id;
SELECT split_from_line_id
FROM OE_ORDER_LINES_ALL
WHERE line_id = p_order_line_id; --AMITAMKU fixed Bug 14262288
PROCEDURE insert_volume(
p_init_msg_list IN VARCHAR2
,p_api_version IN NUMBER
,p_commit IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_volume_detail_rec IN ozf_sales_transactions_pvt.sales_transaction_rec_type
,p_qp_list_header_id IN NUMBER
,p_offer_id IN NUMBER
,p_indirect_flag IN VARCHAR2
,p_sign IN NUMBER
,p_use_vol_smry_stg_tbl IN VARCHAR2 DEFAULT 'F'
,p_resale_batch_id IN NUMBER DEFAULT NULL
)
IS
CURSOR c_group_prod(p_offer_id NUMBER, p_line_id NUMBER, p_indirect_flag VARCHAR2) IS
SELECT group_no, volume_track_type, combine_schedule_yn, pbh_line_id, volume_type, include_volume_flag
FROM ozf_order_group_prod
WHERE offer_id = p_offer_id
AND order_line_id = p_line_id
AND indirect_flag = p_indirect_flag;
SELECT 'Y'
FROM ozf_volume_summary
WHERE offer_id = p_offer_id
AND group_no = p_group_no
AND pbh_line_id = p_pbh_lind_id;
SELECT 'Y'
FROM ozf_volume_summary
WHERE offer_id = p_offer_id
AND individual_type = p_individual_type
AND individual_id = p_individual_id
AND pbh_line_id = p_pbh_line_id;
SELECT 'Y'
FROM ozf_volume_summary
WHERE offer_id = p_offer_id
AND individual_type = p_individual_type
AND individual_id IS NULL
AND pbh_line_id = p_pbh_line_id;
SELECT offer_discount_line_id
FROM ozf_offer_discount_lines
WHERE offer_id = p_offer_id
AND tier_type = 'PBH';
SELECT 'Y'
FROM ozf_volume_detail
WHERE offer_id = p_offer_id
AND source_code = p_source_code
AND order_line_id = p_line_id;
SELECT NVL(transaction_currency_code, fund_request_curr_code)
FROM ozf_offers
WHERE offer_id = p_offer_id;
SELECT reference_header_id, reference_line_id, line_category_code, return_context, return_attribute1, return_attribute2
FROM oe_order_lines_all
WHERE line_id = p_line_id;
SELECT offer_id, qp_list_header_id, group_no, volume_track_type, combine_schedule_yn, pbh_line_id, volume_type, prod_attribute, prod_attr_value, apply_discount_flag, include_volume_flag, indirect_flag
FROM ozf_order_group_prod
WHERE offer_id = p_offer_id
AND order_line_id = p_line_id
AND indirect_flag = p_indirect_flag;
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
l_api_name CONSTANT VARCHAR2(30) := 'insert_volume';
ozf_utility_pvt.write_conc_log('========================= Insert Volume =========================');
INSERT INTO ozf_order_group_prod
(
order_group_prod_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,order_line_id
,offer_id
,qp_list_header_id
,group_no
,volume_track_type
,combine_schedule_yn
,pbh_line_id
,volume_type
,prod_attribute
,prod_attr_value
,apply_discount_flag
,include_volume_flag
,indirect_flag
)
VALUES
( ozf_order_group_prod_s.NEXTVAL
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,p_volume_detail_rec.line_id
,l_rma_ref_line_detail.offer_id
,l_rma_ref_line_detail.qp_list_header_id
,l_rma_ref_line_detail.group_no
,l_rma_ref_line_detail.volume_track_type
,l_rma_ref_line_detail.combine_schedule_yn
,l_rma_ref_line_detail.pbh_line_id
,l_rma_ref_line_detail.volume_type
,l_rma_ref_line_detail.prod_attribute
,l_rma_ref_line_detail.prod_attr_value
,l_rma_ref_line_detail.apply_discount_flag
,l_rma_ref_line_detail.include_volume_flag
,l_rma_ref_line_detail.indirect_flag
);
select split_from_line_id into l_split_from_line_id from OE_ORDER_LINES_ALL where line_id = p_volume_detail_rec.line_id;
INSERT INTO ozf_volume_detail
(
volume_detail_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,offer_id
,qp_list_header_id
,distributor_acct_id
,cust_account_id
,bill_to_site_use_id
,ship_to_site_use_id
,inventory_item_id
,volume_type
,uom_code
,currency_code
,volume
,group_no
,volume_track_type
,order_line_id
,transaction_date
,pbh_line_id
,include_volume_flag
,source_code
)
VALUES
(
ozf_volume_detail_s.NEXTVAL
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,p_offer_id
,p_qp_list_header_id
,p_volume_detail_rec.sold_from_cust_account_id
,p_volume_detail_rec.sold_to_cust_account_id
,p_volume_detail_rec.bill_to_site_use_id
,p_volume_detail_rec.ship_to_site_use_id
,p_volume_detail_rec.inventory_item_id
,l_volume_type
,p_volume_detail_rec.uom_code
,l_currency_code
,l_current_volume
,l_group_no
,l_volume_track_type
,p_volume_detail_rec.line_id
,p_volume_detail_rec.transaction_date
,l_pbh_line_id
,l_include_volume
,p_volume_detail_rec.source_code
);
INSERT INTO ozf_volume_summary_int
(
resale_batch_id
,offer_id
,group_no
,group_volume
,pbh_line_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,batch_type
,request_id
)
VALUES
(
p_resale_batch_id
,p_offer_id
,l_group_no
,l_current_volume
,l_pbh_line.offer_discount_line_id
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,'TP_ACCRUAL'
,FND_GLOBAL.conc_request_id
);
ELSE -- insert one pbh line only in to ozf_volume_summary_int table
INSERT INTO ozf_volume_summary_int
(
resale_batch_id
,offer_id
,group_no
,group_volume
,pbh_line_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,batch_type
,request_id
)
VALUES
(
p_resale_batch_id
,p_offer_id
,l_group_no
,l_current_volume
,l_pbh_line_id
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,'TP_ACCRUAL'
,FND_GLOBAL.conc_request_id
);
INSERT INTO ozf_volume_summary_int
(
resale_batch_id
,offer_id
,individual_type
,individual_id
,individual_volume
,pbh_line_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,batch_type
,request_id
)
VALUES
(
p_resale_batch_id
,p_offer_id
,'DISTRIBUTOR'
,p_volume_detail_rec.sold_from_cust_account_id
,l_current_volume
,l_pbh_line_id
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,'TP_ACCRUAL'
,FND_GLOBAL.conc_request_id
);
INSERT INTO ozf_volume_summary_int
(
resale_batch_id
,offer_id
,individual_type
,individual_id
,individual_volume
,pbh_line_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,batch_type
,request_id
)
VALUES
(
p_resale_batch_id
,p_offer_id
,'ACCOUNT'
,p_volume_detail_rec.sold_to_cust_account_id
,l_current_volume
,l_pbh_line_id
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,'TP_ACCRUAL'
,FND_GLOBAL.conc_request_id
);
INSERT INTO ozf_volume_summary_int
(
resale_batch_id
,offer_id
,individual_type
,individual_id
,individual_volume
,pbh_line_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,batch_type
,request_id
)
VALUES
(
p_resale_batch_id
,p_offer_id
,'BILL_TO'
, p_volume_detail_rec.bill_to_site_use_id
,l_current_volume
,l_pbh_line_id
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,'TP_ACCRUAL'
,FND_GLOBAL.conc_request_id
);
INSERT INTO ozf_volume_summary_int
(
resale_batch_id
,offer_id
,individual_type
,individual_id
,individual_volume
,pbh_line_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,batch_type
,request_id
)
VALUES
(
p_resale_batch_id
,p_offer_id
,'SHIP_TO'
, p_volume_detail_rec.ship_to_site_use_id
,l_current_volume
,l_pbh_line_id
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,'TP_ACCRUAL'
,FND_GLOBAL.conc_request_id
);
IF l_volume_exists = 'Y' THEN -- update group's volume
IF l_combine_schedule_yn = 'Y' THEN -- update all pbh lines
UPDATE ozf_volume_summary
SET group_volume = group_volume + l_current_volume,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.conc_login_id
WHERE offer_id = p_offer_id
AND group_no = l_group_no;
ELSE -- update one pbh line only
UPDATE ozf_volume_summary
SET group_volume = group_volume + l_current_volume,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.conc_login_id
WHERE offer_id = p_offer_id
AND group_no = l_group_no
AND pbh_line_id = l_pbh_line_id;
ELSE -- insert group's volume
IF l_combine_schedule_yn = 'Y' THEN -- insert all pbh lines
FOR l_pbh_line IN c_pbh_lines(p_offer_id) LOOP
INSERT INTO ozf_volume_summary
(
volume_summary_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,offer_id
,qp_list_header_id
,group_no
,group_volume
,pbh_line_id
)
VALUES
(
ozf_volume_summary_s.NEXTVAL
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,p_offer_id
,p_qp_list_header_id
,l_group_no
,l_current_volume
,l_pbh_line.offer_discount_line_id
);
ELSE -- insert one pbh line
INSERT INTO ozf_volume_summary
(
volume_summary_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,offer_id
,qp_list_header_id
,group_no
,group_volume
,pbh_line_id
)
VALUES
(
ozf_volume_summary_s.NEXTVAL
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,p_offer_id
,p_qp_list_header_id
,l_group_no
,l_current_volume
,l_pbh_line_id
);
IF l_volume_exists = 'Y' THEN -- update distributor's volume
UPDATE ozf_volume_summary
SET individual_volume = individual_volume + l_current_volume,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.conc_login_id
WHERE offer_id = p_offer_id
AND individual_type = 'DISTRIBUTOR'
AND individual_id = p_volume_detail_rec.sold_from_cust_account_id
AND pbh_line_id = l_pbh_line_id;
ELSE -- insert distributor's volume
INSERT INTO ozf_volume_summary
(
volume_summary_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,offer_id
,qp_list_header_id
,individual_type
,individual_id
,individual_volume
,pbh_line_id
)
VALUES
(
ozf_volume_summary_s.NEXTVAL
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,p_offer_id
,p_qp_list_header_id
,'DISTRIBUTOR'
,p_volume_detail_rec.sold_from_cust_account_id
,l_current_volume
,l_pbh_line_id
);
IF l_volume_exists = 'Y' THEN -- update customer's volume
UPDATE ozf_volume_summary
SET individual_volume = individual_volume + l_current_volume,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.conc_login_id
WHERE offer_id = p_offer_id
AND individual_type = 'ACCOUNT'
AND individual_id = p_volume_detail_rec.sold_to_cust_account_id
AND pbh_line_id = l_pbh_line_id;
ELSE -- insert customer's volume
INSERT INTO ozf_volume_summary
(
volume_summary_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,offer_id
,qp_list_header_id
,individual_type
,individual_id
,individual_volume
,pbh_line_id
)
VALUES
(
ozf_volume_summary_s.NEXTVAL
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,p_offer_id
,p_qp_list_header_id
,'ACCOUNT'
,p_volume_detail_rec.sold_to_cust_account_id
,l_current_volume
,l_pbh_line_id
);
IF l_volume_exists = 'Y' THEN -- update bill_to's volume
UPDATE ozf_volume_summary
SET individual_volume = individual_volume + l_current_volume,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.conc_login_id
WHERE offer_id = p_offer_id
AND individual_type = 'BILL_TO'
AND individual_id = p_volume_detail_rec.bill_to_site_use_id
AND pbh_line_id = l_pbh_line_id;
ELSE -- insert bill_to's volume
INSERT INTO ozf_volume_summary
(
volume_summary_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,offer_id
,qp_list_header_id
,individual_type
,individual_id
,individual_volume
,pbh_line_id
)
VALUES
(
ozf_volume_summary_s.NEXTVAL
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,p_offer_id
,p_qp_list_header_id
,'BILL_TO'
,p_volume_detail_rec.bill_to_site_use_id
,l_current_volume
,l_pbh_line_id
);
IF l_volume_exists = 'Y' THEN -- update ship_to's volume
UPDATE ozf_volume_summary
SET individual_volume = individual_volume + l_current_volume,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.conc_login_id
WHERE offer_id = p_offer_id
AND individual_type = 'SHIP_TO'
AND individual_id = p_volume_detail_rec.ship_to_site_use_id
AND pbh_line_id = l_pbh_line_id;
ELSE -- insert ship_to's volume
INSERT INTO ozf_volume_summary
(
volume_summary_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,offer_id
,qp_list_header_id
,individual_type
,individual_id
,individual_volume
,pbh_line_id
)
VALUES
(
ozf_volume_summary_s.NEXTVAL
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,p_offer_id
,p_qp_list_header_id
,'SHIP_TO'
,p_volume_detail_rec.ship_to_site_use_id
,l_current_volume
,l_pbh_line_id
);
ROLLBACK TO insert_volume;
END insert_volume;
SELECT off.offer_id, off.qp_list_header_id
FROM ozf_offers off, ozf_funds_utilized_all_b utl
WHERE off.qp_list_header_id = utl.plan_id
AND off.offer_type = 'VOLUME_OFFER'
AND utl.plan_type = 'OFFR'
AND utl.order_line_id = p_order_line_id
--AND ((off.volume_offer_type = 'ACCRUAL' AND utl.utilization_type IN ('ACCRUAL', 'SALES_ACCRUAL'))
-- Need to consider the backdated adjustment created for booked orders
--changed for bug 6021635
AND ((off.volume_offer_type = 'ACCRUAL' AND (utl.utilization_type IN ('ACCRUAL', 'SALES_ACCRUAL') or (utl.utilization_type IN ('ACCRUAL', 'SALES_ACCRUAL','ADJUSTMENT')
AND utl.price_adjustment_id=-1)))
OR (off.volume_offer_type = 'OFF_INVOICE' AND utl.utilization_type = 'UTILIZED'))
-- AND utl.utilization_type = DECODE(off.volume_offer_type, 'ACCRUAL', 'ACCRUAL', 'OFF_INVOICE', 'UTILIZED')
AND utl.object_type = p_object_type;
SELECT off.offer_id, off.qp_list_header_id
FROM ozf_offers off, ozf_funds_utilized_all_b utl
WHERE off.qp_list_header_id = utl.plan_id
AND off.offer_type = 'VOLUME_OFFER'
AND utl.plan_type = 'OFFR'
AND utl.object_id = p_order_line_id
AND ((off.volume_offer_type = 'ACCRUAL' AND utl.utilization_type IN ('ACCRUAL', 'SALES_ACCRUAL'))
OR (off.volume_offer_type = 'OFF_INVOICE' AND utl.utilization_type = 'UTILIZED'))
-- AND utl.utilization_type = DECODE(off.volume_offer_type, 'ACCRUAL', 'ACCRUAL', 'OFF_INVOICE', 'UTILIZED')
AND utl.object_type = p_object_type;
SELECT offer_id, qp_list_header_id
FROM ozf_offers
WHERE offer_type = 'VOLUME_OFFER'
AND qp_list_header_id = p_qp_list_header_id;
SELECT uom_code
FROM ozf_offer_discount_lines
WHERE offer_id = l_offer_id;
l_volume_detail_rec := p_volume_detail_rec ; -- Catch Weight ER assigning the input rec to a local variable of same type to change the quantity value before passing to insert_volume
insert_volume(
p_init_msg_list => p_init_msg_list
,p_api_version => p_api_version
,p_commit => p_commit
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_volume_detail_rec => l_volume_detail_rec -- Catch Weight ER
,p_qp_list_header_id => l_offer_id_om.qp_list_header_id
,p_offer_id => l_offer_id_om.offer_id
,p_indirect_flag => l_indirect_flag
,p_sign => l_sign
,p_use_vol_smry_stg_tbl => p_use_vol_smry_stg_tbl
,p_resale_batch_id => p_resale_batch_id
);
insert_volume(
p_init_msg_list => p_init_msg_list
,p_api_version => p_api_version
,p_commit => p_commit
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_volume_detail_rec => l_volume_detail_rec -- Catch Weight ER
,p_qp_list_header_id => l_qp_list_header_id
,p_offer_id => l_offer_id
,p_indirect_flag => l_indirect_flag
,p_sign => l_sign
,p_use_vol_smry_stg_tbl => p_use_vol_smry_stg_tbl
,p_resale_batch_id => p_resale_batch_id
);
insert_volume(
p_init_msg_list => p_init_msg_list
,p_api_version => p_api_version
,p_commit => p_commit
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_volume_detail_rec => l_volume_detail_rec -- Catch Weight ER
,p_qp_list_header_id => l_offer_id_is.qp_list_header_id
,p_offer_id => l_offer_id_is.offer_id
,p_indirect_flag => l_indirect_flag
,p_sign => l_sign
,p_use_vol_smry_stg_tbl => p_use_vol_smry_stg_tbl
,p_resale_batch_id => p_resale_batch_id );
insert_volume(
p_init_msg_list => p_init_msg_list
,p_api_version => p_api_version
,p_commit => p_commit
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_volume_detail_rec => l_volume_detail_rec -- Catch Weight ER
,p_qp_list_header_id => l_qp_list_header_id
,p_offer_id => l_offer_id
,p_indirect_flag => l_indirect_flag
,p_sign => l_sign
,p_use_vol_smry_stg_tbl => p_use_vol_smry_stg_tbl
,p_resale_batch_id => p_resale_batch_id );
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND group_no = p_volume_track_id
AND pbh_line_id = p_pbh_line_id
AND transaction_date < p_transaction_date;
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND distributor_acct_id = p_volume_track_id
AND pbh_line_id = p_pbh_line_id
AND transaction_date < p_transaction_date;
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND cust_account_id = p_volume_track_id
AND pbh_line_id = p_pbh_line_id
AND transaction_date < p_transaction_date;
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND bill_to_site_use_id = p_volume_track_id
AND pbh_line_id = p_pbh_line_id
AND transaction_date < p_transaction_date;
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND ship_to_site_use_id = p_volume_track_id
AND pbh_line_id = p_pbh_line_id
AND transaction_date < p_transaction_date;
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND group_no = p_volume_track_id
AND transaction_date < p_transaction_date;
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND distributor_acct_id = p_volume_track_id
AND transaction_date < p_transaction_date;
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND cust_account_id = p_volume_track_id
AND transaction_date < p_transaction_date;
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND bill_to_site_use_id = p_volume_track_id
AND transaction_date < p_transaction_date;
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND ship_to_site_use_id = p_volume_track_id
AND transaction_date < p_transaction_date;
SELECT volume_detail_id
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND source_code = p_source_code
AND order_line_id = p_order_line_id;
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND group_no = p_volume_track_id
AND pbh_line_id = p_pbh_line_id
AND transaction_date = p_transaction_date
AND volume_detail_id <= p_volume_detail_id;
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND distributor_acct_id = p_volume_track_id
AND pbh_line_id = p_pbh_line_id
AND transaction_date = p_transaction_date
AND volume_detail_id <= p_volume_detail_id;
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND cust_account_id = p_volume_track_id
AND pbh_line_id = p_pbh_line_id
AND transaction_date = p_transaction_date
AND volume_detail_id <= p_volume_detail_id;
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND cust_account_id = p_volume_track_id
AND pbh_line_id = p_pbh_line_id
AND trunc(transaction_date) = trunc(p_transaction_date)
AND volume_detail_id <= p_volume_detail_id;
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND bill_to_site_use_id = p_volume_track_id
AND pbh_line_id = p_pbh_line_id
AND transaction_date = p_transaction_date
AND volume_detail_id <= p_volume_detail_id;
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND ship_to_site_use_id = p_volume_track_id
AND pbh_line_id = p_pbh_line_id
AND transaction_date = p_transaction_date
AND volume_detail_id <= p_volume_detail_id;
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND group_no = p_volume_track_id
AND transaction_date = p_transaction_date
AND volume_detail_id <= p_volume_detail_id;
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND distributor_acct_id = p_volume_track_id
AND transaction_date = p_transaction_date
AND volume_detail_id <= p_volume_detail_id;
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND cust_account_id = p_volume_track_id
AND transaction_date = p_transaction_date
AND volume_detail_id <= p_volume_detail_id;
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND bill_to_site_use_id = p_volume_track_id
AND transaction_date = p_transaction_date
AND volume_detail_id <= p_volume_detail_id;
SELECT NVL(SUM(volume), 0)
FROM ozf_volume_detail
WHERE include_volume_flag = 'Y'
AND offer_id = p_offer_id
AND ship_to_site_use_id = p_volume_track_id
AND transaction_date = p_transaction_date
AND volume_detail_id <= p_volume_detail_id;
SELECT volume_offer_type
FROM ozf_offers
WHERE offer_id = p_offer_id;
SELECT group_volume
FROM ozf_volume_summary
WHERE offer_id = p_offer_id
AND group_no = p_group_no
AND pbh_line_id = p_pbh_line_id;
SELECT individual_volume
FROM ozf_volume_summary
WHERE offer_id = p_offer_id
AND individual_type = p_vol_track_type
AND individual_id = p_volume_track_id
AND pbh_line_id = p_pbh_line_id;
SELECT SUM(individual_volume)
FROM ozf_volume_summary
WHERE offer_id = p_offer_id
AND individual_type = p_vol_track_type
AND individual_id = p_volume_track_id;
SELECT billto_cust_account_id, bill_to_site_use_id, ship_to_site_use_id
FROM ozf_funds_utilized_all_b
WHERE (p_source_code = 'OM' AND object_type = 'ORDER' AND order_line_id = p_order_line_id)
OR (p_source_code = 'IS' AND object_type = 'TP_ORDER' AND object_id = p_order_line_id);
SELECT sold_from_cust_account_id
FROM ozf_resale_lines_all
WHERE resale_line_id = p_order_line_id;
SELECT offer_id, combine_schedule_yn, apply_discount_flag, group_no, volume_track_type, pbh_line_id
FROM ozf_order_group_prod
WHERE qp_list_header_id = l_qp_list_header_id
AND order_line_id = l_order_line_id
AND indirect_flag = DECODE(p_source_code, 'OM', 'O', 'IS', 'R');
SELECT a.volume_from
FROM ozf_offer_discount_lines a, ozf_market_preset_tiers b, ozf_offr_market_options c
WHERE a.offer_discount_line_id = b.dis_offer_discount_id
AND b. pbh_offer_discount_id = p_pbh_line_id
AND b.offer_market_option_id = c.offer_market_option_id
AND c.offer_id = p_offer_id
AND c.group_number = p_group_no;
SELECT split_from_line_id into l_split_from_line_id from OE_ORDER_LINES_ALL where line_id = p_order_line_id;
SELECT o.qp_list_header_id, o.offer_id
FROM ozf_offers o, qp_list_lines q
WHERE o.qp_list_header_id = q.list_header_id
AND q.list_line_id = p_list_line_id;
SELECT unit_selling_price, pricing_quantity, sold_to_org_id, ship_to_org_id, invoice_to_org_id, actual_shipment_date
FROM oe_order_lines_all
WHERE line_id = p_order_line_id;
SELECT quantity, amount, sold_from_cust_account_id, sold_to_cust_account_id, ship_to_site_use_id, bill_to_site_use_id, transaction_date
FROM ozf_sales_transactions
WHERE line_id = p_order_line_id;
SELECT quantity, quantity * selling_price, sold_from_cust_account_id, bill_to_cust_account_id, ship_to_site_use_id, bill_to_site_use_id, date_ordered
FROM ozf_resale_lines_int_all
WHERE resale_line_int_id = p_order_line_id;
SELECT apply_discount_flag, include_volume_flag
FROM ozf_offer_discount_products
WHERE product_context = 'ITEM'
AND product_attribute = p_prod_attribute
AND product_attr_value = p_prod_attr_value
AND offer_id = p_offer_id;
SELECT order_group_prod_id
FROM ozf_order_group_prod
WHERE order_line_id = p_line_id
AND offer_id = p_offer_id
AND indirect_flag = p_indirect_flag;
SELECT volume_type
FROM ozf_offer_discount_lines
WHERE offer_discount_line_id = p_pbh_line_id;
SELECT a.volume_from
FROM ozf_offer_discount_lines a, ozf_market_preset_tiers b, ozf_offr_market_options c
WHERE a.offer_discount_line_id = b.dis_offer_discount_id
AND b. pbh_offer_discount_id = p_pbh_line_id
AND b.offer_market_option_id = c.offer_market_option_id
AND c.offer_id = p_offer_id
AND c.group_number = p_group_no;
SELECT split_from_line_id
FROM OE_ORDER_LINES_ALL
WHERE line_id = p_order_line_id; --AMITAMKU fixed Bug 14262288
INSERT INTO om_qp_temp(id, rec_req_type, access_date, line_index)
VALUES(om_qp_temp_s.nextval, 'START', SYSDATE, -9999);
INSERT INTO om_qp_temp(
id,
rec_context,
rec_attr,
access_date,
line_index,
group_num,
order_line_id,
list_line_id)
VALUES(om_qp_temp_s.nextval,
p_accum_rec.context,
p_accum_rec.attribute,
sysdate,
0,
l_rec_count,
p_order_line_id,
p_list_line_id);
INSERT INTO om_qp_temp(
id,
access_date,
line_index,
attr_type,
context,
attr,
attr_value,
group_num,
order_line_id,
list_line_id)
VALUES(
om_qp_temp_s.nextval,
sysdate,
p_req_line_attrs_tbl(i).line_index,
p_req_line_attrs_tbl(i).attribute_type,
p_req_line_attrs_tbl(i).context,
p_req_line_attrs_tbl(i).attribute,
p_req_line_attrs_tbl(i).value,
p_req_line_attrs_tbl(i).grouping_no,
p_order_line_id,
p_list_line_id);
INSERT INTO ozf_order_group_prod
(
order_group_prod_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,order_line_id
,offer_id
,qp_list_header_id
,group_no
,volume_track_type
,combine_schedule_yn
,pbh_line_id
,volume_type
,prod_attribute
,prod_attr_value
,apply_discount_flag
,include_volume_flag
,indirect_flag
)
VALUES
( ozf_order_group_prod_s.NEXTVAL
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,p_order_line_id
,l_offer_id
,l_list_header_id
,l_group_no
,l_vol_track_type
,l_combine_schedule
,l_pbh_line_id
,l_volume_type
,l_prod_attribute
,l_prod_attr_value
,l_apply_discount
,l_include_volume
,l_indirect_flag
);
UPDATE ozf_order_group_prod
SET last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.conc_login_id,
order_line_id = p_order_line_id,
offer_id = l_offer_id,
qp_list_header_id = l_list_header_id,
group_no = l_group_no,
volume_track_type = l_vol_track_type,
combine_schedule_yn = l_combine_schedule,
pbh_line_id = l_pbh_line_id,
volume_type = l_volume_type,
prod_attribute = l_prod_attribute,
prod_attr_value = l_prod_attr_value,
apply_discount_flag = l_apply_discount,
include_volume_flag = l_include_volume,
indirect_flag = l_indirect_flag
WHERE order_group_prod_id = l_id;
SELECT group_no
FROM ozf_volume_detail
WHERE offer_id = p_offer_id
AND cust_account_id = p_cust_account_id
AND transaction_date =
(
SELECT MAX(transaction_date)
FROM ozf_volume_detail
WHERE cust_account_id = p_cust_account_id
AND offer_id = p_offer_id
);
SELECT group_no
FROM ozf_order_group_prod
WHERE offer_id = p_offer_id
AND order_line_id = p_order_line_id;
SELECT a.beneficiary_party_id
FROM ozf_offr_market_options a, ozf_offr_market_options b
WHERE a.offer_market_option_id = b.offer_market_option_id
AND b.offer_id = p_offer_id
AND b.group_number = p_group_no;
SELECT beneficiary_party_id
FROM ozf_offr_market_options
WHERE offer_id = p_offer_id
AND group_number = p_group_no;
PROCEDURE update_tracking_line
(
p_init_msg_list IN VARCHAR2
,p_api_version IN NUMBER
,p_commit IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_list_header_id IN NUMBER
,p_interface_line_id IN NUMBER
,p_resale_line_id IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_tracking_line';
SAVEPOINT update_tracking_line;
UPDATE ozf_order_group_prod
SET order_line_id = p_resale_line_id,
indirect_flag = 'R'
WHERE qp_list_header_id = p_list_header_id
AND order_line_id = p_interface_line_id
AND indirect_flag = 'I';
ROLLBACK TO update_tracking_line;
END update_tracking_line;
select nvl(group_volume,0) into l_group_volume from ozf_volume_summary
where offer_id = p_offer_id
and group_no = p_group_number
and pbh_line_id = p_pbh_line_id;
select sum(volume) customer_volume
into l_product_volume
from ozf_volume_detail
where offer_id = p_offer_id
and cust_account_id = p_cust_account_id
and pbh_line_id = p_pbh_line_id
and group_no = p_group_no --kdass bug 13256460 - added p_group_no
and (p_bill_to_id is null or (p_bill_to_id is not null and (bill_to_site_use_id = p_bill_to_id))) -- bill_to_site_use_id = nvl(p_bill_to_id, bill_to_site_use_id)
and (p_ship_to_id is null or (p_ship_to_id is not null and (ship_to_site_use_id = p_ship_to_id))); -- ship_to_site_use_id = nvl(p_ship_to_id, ship_to_site_use_id);
select volume_from ||'-' || volume_to into l_volume_range
from ozf_offer_discount_lines
where offer_id = p_offer_id
and parent_discount_line_id = p_pbh_line_id
and l_volume >= volume_from and l_volume < volume_to; --kdass bug 13256460
select discount into l_actual_discount
from ozf_offer_discount_lines
where offer_id = p_offer_id
and parent_discount_line_id = p_pbh_line_id
and l_volume >= volume_from and l_volume < volume_to; --kdass bug 13256460
select c.volume_from ||'-' || c.volume_to into l_volume_range
from ozf_offr_market_options a,
ozf_market_preset_tiers b,
ozf_offer_discount_lines c
where a.offer_id = p_offer_id
and b.offer_market_option_id = a.offer_market_option_id
and a.group_number = p_group_no
and b.pbh_offer_discount_id = p_pbh_line_id
and c.offer_discount_line_id = b.dis_offer_discount_id;
select c.discount into l_actual_discount
from ozf_offr_market_options a,
ozf_market_preset_tiers b,
ozf_offer_discount_lines c
where a.offer_id = p_offer_id
and b.offer_market_option_id = a.offer_market_option_id
and a.group_number = p_group_no
and b.pbh_offer_discount_id = p_pbh_line_id
and c.offer_discount_line_id = b.dis_offer_discount_id;
select qp_list_header_id into l_qp_list_header_id from ozf_offers where offer_id = p_offer_id;
SELECT SUM(uti.plan_curr_amount) into l_payout_accrual
FROM ozf_funds_utilized_all_b uti
WHERE uti.utilization_type IN ('UTILIZED','ACCRUAL','ADJUSTMENT','CHARGEBACK','LEAD_ACCRUAL')
AND plan_type = 'OFFR'
AND plan_id = l_qp_list_header_id
and product_id = p_item_id
and cust_account_id = p_cust_account_id
and (p_bill_to_id is null or (p_bill_to_id is not null and (bill_to_site_use_id = p_bill_to_id))) -- bill_to_site_use_id = nvl(p_bill_to_id, bill_to_site_use_id)
and (p_ship_to_id is null or (p_ship_to_id is not null and (ship_to_site_use_id = p_ship_to_id))) -- ship_to_site_use_id = nvl(p_ship_to_id, ship_to_site_use_id);
select list_line_id
from oe_price_adjustments
where list_line_type_code='PBH'
and list_header_id=p_qp_list_header_id
and line_id= p_to_order_line_id;
SELECT group_no, volume_track_type, combine_schedule_yn, pbh_line_id, prod_attribute, prod_attr_value,qp_list_header_id
FROM ozf_order_group_prod
WHERE order_line_id = p_from_order_line_id
AND indirect_flag = 'O';