[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
SELECT 1
FROM ozf_resale_adjustments
WHERE resale_line_id = p_line_id
AND list_header_id = p_list_header_id
AND list_line_id IN
(SELECT from_list_line_id
FROM ozf_offer_adj_rltd_lines
START WITH to_list_line_id = p_list_line_id
CONNECT BY PRIOR from_list_line_id = to_list_line_id
UNION ALL
SELECT to_list_line_id FROM ozf_offer_adj_rltd_lines where to_list_line_id = p_list_line_id);*/
SELECT 1
FROM ozf_resale_adjustments
WHERE resale_line_id = p_line_id
AND list_header_id = p_list_header_id
AND((list_line_id = p_list_line_id)
OR(list_line_id IN
(SELECT from_list_line_id
FROM ozf_offer_adj_rltd_lines
START WITH to_list_line_id = p_list_line_id
CONNECT BY PRIOR from_list_line_id = to_list_line_id)));
SELECT status_code
, org_id
from ozf_resale_batches
WHERE resale_batch_id = p_resale_batch_id;
INSERT INTO ozf_resale_logs_all(
resale_log_id,
resale_id,
resale_id_type,
error_code,
error_message,
column_name,
column_value,
org_id
) SELECT
ozf_resale_logs_all_s.nextval,
p_resale_batch_id,
'BATCH',
'OZF_BATCH_STATUS_WNG',
FND_MESSAGE.get_string('OZF','OZF_BATCH_STATUS_WNG'),
'STATUS_CODE',
l_status_code,
l_org_id
FROM dual
WHERE NOT EXISTS (
SELECT 1
FROM ozf_resale_logs a
WHERE a.resale_id = p_resale_batch_id
AND a.resale_id_type = 'BATCH'
AND a.error_code = 'OZF_BATCH_STATUS_WNG'
);
INSERT INTO ozf_resale_logs_all(
resale_log_id,
resale_id,
resale_id_type,
error_code,
error_message,
column_name,
column_value,
org_id
) SELECT
ozf_resale_logs_all_s.nextval,
resale_line_int_id,
'IFACE',
'OZF_RESALE_SELL_PRICE_NULL',
FND_MESSAGE.get_string('OZF','OZF_RESALE_SELL_PRICE_NULL'),
'SELLING_PRICE',
NULL,
org_id
FROM ozf_resale_lines_int_all b
WHERE b.status_code = 'OPEN'
AND b.direct_customer_flag = 'T'
AND b.selling_price IS NULL
AND b.resale_batch_id = p_resale_batch_id
AND NOT EXISTS(
SELECT 1
FROM ozf_resale_logs_all a
WHERE a.resale_id = b.resale_line_int_id
AND a.resale_id_type = 'IFACE'
AND a.error_code ='OZF_RESALE_SELL_PRICE_NULL'
);
UPDATE ozf_resale_lines_int_all
SET status_code = 'DISPUTED',
dispute_code = 'OZF_RESALE_SELL_PRICE_NULL'
WHERE status_code = 'OPEN'
AND direct_customer_flag = 'T'
AND selling_price IS NULL
AND resale_batch_id = p_resale_batch_id;
INSERT INTO ozf_resale_logs_all(
resale_log_id,
resale_id,
resale_id_type,
error_code,
error_message,
column_name,
column_value,
org_id
) SELECT
ozf_resale_logs_all_s.nextval,
b.resale_line_id,
'LINE',
'OZF_RESALE_SELL_PRICE_NULL',
FND_MESSAGE.get_string('OZF','OZF_RESALE_SELL_PRICE_NULL'),
'SELLING_PRICE',
NULL,
b.org_id
FROM ozf_resale_lines_all b
, ozf_resale_batch_line_maps_all c
WHERE b.direct_customer_flag = 'T'
AND b.selling_price IS NULL
AND b.resale_line_id = c.resale_line_id
AND c.resale_batch_id = p_resale_batch_id
AND NOT EXISTS(SELECT 1
FROM ozf_resale_logs_all a
WHERE a.resale_id = b.resale_line_id
AND a.resale_id_type = 'LINE'
AND a.error_code ='OZF_RESALE_SELL_PRICE_NULL'
);
SELECT *
FROM qp_ldets_v
WHERE line_index = p_index
ORDER BY pricing_group_sequence;
SELECT a.orig_system_agreement_uom,
a.orig_system_agreement_name,
a.orig_system_agreement_type,
a.orig_system_agreement_status,
a.orig_system_agreement_curr,
a.orig_system_agreement_price,
a.orig_system_agreement_quantity,
a.agreement_id, a.agreement_type,
a.agreement_name, a.agreement_price,
a.agreement_uom_code,
a.corrected_agreement_id,
a.corrected_agreement_name,
a.credit_code,
a.credit_advice_date
FROM ozf_resale_adjustments a, ozf_resale_batches b
WHERE a.resale_line_id = p_line_id
AND a.resale_batch_id = p_batch_id
AND a.line_agreement_flag = 'T'
AND a.resale_batch_id = b.resale_batch_id
AND b.batch_type = OZF_RESALE_COMMON_PVT.G_CHARGEBACK
AND rownum = 1;
SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = p_cust_account_id;
SELECT party_site_id
FROM hz_cust_acct_sites
WHERE cust_acct_site_id = p_account_site_id;
SELECT offer_type, custom_setup_id, description, offer_id
FROM ozf_offers off, qp_list_headers_all qp
WHERE off.qp_list_header_id = p_qp_list_header_id
AND off.qp_list_header_id = qp.list_header_id;
SELECT supplier_item_cost
FROM ozf_resale_lines_all
WHERE resale_line_id = p_resale_line_id;
SELECT request_header_id
FROM ozf_sd_request_headers_all_b
WHERE offer_id =p_list_header_id;
SELECT NVL(qpll.price_by_formula_id, qpll.operand),
qpll.arithmetic_operator
FROM qp_list_lines qpll
WHERE qpll.list_line_id = p_list_line_id;
OZF_RESALE_COMMON_PVT.Insert_resale_line(
p_api_version => 1
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_line_int_rec => p_resale_line_int_rec
,p_header_id => p_header_id
,x_line_id => l_line_id
,x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
);
OZF_RESALE_COMMON_PVT.Insert_Resale_Line_Mapping(
p_api_version => 1
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_resale_batch_id => p_resale_batch_id
,p_line_id => l_line_id
,x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
);
-- Bug 4380203 Fixing: Inventory Temp table is already updated in Validate_Inventory_Level
/*
IF p_inventory_tracking THEN
OZF_SALES_TRANSACTIONS_PVT.update_inventory_tmp (
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_sales_transaction_id => l_sales_transaction_id
,x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
);
l_ldets_tbl.DELETE;
-- TPA in DRAFT mode ER : Update tracking line(ozf_order_group_prod table) only in FINAL mode
IF l_object_type = 'OFFR' AND p_run_mode <> 'DRAFT' THEN
OZF_VOLUME_CALCULATION_PUB.Update_Tracking_Line(
p_init_msg_list => FND_API.g_false
,p_api_version => 1.0
,p_commit => FND_API.g_false
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_list_header_id => l_ldets_tbl(k).list_header_id
,p_interface_line_id => p_resale_line_int_rec.resale_line_int_id
,p_resale_line_id => l_line_id
);
SELECT order_number
, bill_to_cust_account_id
, date_ordered
FROM ozf_resale_lines_int_all
WHERE resale_line_int_id = p_id;
SELECT resale_header_id
FROM ozf_resale_lines
WHERE resale_line_id = p_id;
SELECT *
FROM ozf_resale_lines
WHERE resale_line_id = p_id;
SELECT exchange_rate_type
FROM ozf_sys_parameters;
SELECT gs.currency_code
FROM gl_sets_of_books gs,
ozf_sys_parameters osp
WHERE gs.set_of_books_id = osp.set_of_books_id
AND osp.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID(); -- BUG 5058027
SELECT 1
FROM ozf_resale_lines_int_all
WHERE resale_batch_id = p_id
AND order_number = p_order_number
AND bill_to_cust_account_id = p_cust_id
AND date_ordered = p_date
AND status_code = 'DUPLICATED';
SELECT a.resale_header_id
FROM ozf_resale_headers_all a
, ozf_resale_lines_int_all b
, ozf_resale_lines_all c
WHERE b.resale_batch_id = p_id
AND b.order_number = p_order_number
AND b.bill_to_cust_account_id = p_cust_id
AND b.date_ordered = p_date
AND b.status_code = 'DUPLICATED'
AND b.duplicated_line_id = c.resale_line_id
AND c.resale_header_id = a.resale_header_id;
SELECT partner_cust_account_id,
partner_party_id,
report_start_date,
report_end_date,
last_updated_by,
NVL(direct_order_flag,'N') -- For Bug#9447673 SSD IDSM
FROM ozf_resale_batches_all
WHERE resale_batch_id = p_id;
l_last_updated_by NUMBER(15);
l_last_updated_by,
l_direct_order_flag;
p_line_tbl(i).pricing_status_code <> QP_PREQ_PUB.G_STATUS_UPDATED;
UPDATE ozf_resale_lines_int_all
SET status_code = 'DISPUTED'
WHERE status_code = 'OPEN'
AND order_NUMBER = l_order_number
AND bill_to_cust_account_id = l_cust_account_id
AND date_ordered = l_date_ordered
AND resale_batch_id = p_resale_batch_id;
OZF_RESALE_LOGS_PKG.Insert_Row(
px_resale_log_id => l_log_id,
p_resale_id => p_line_tbl(i).chargeback_int_id,
p_resale_id_type => l_id_type,
p_error_code => p_line_tbl(i).pricing_status_code,
p_error_message => p_line_tbl(i).pricing_status_text,
p_column_name => NULL,
p_column_value => NULL,
--px_org_id => OZF_RESALE_COMMON_PVT.g_org_id
px_org_id => l_org_id
);
/* OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
p_id_value => l_resale_int_rec.resale_line_int_id,
p_id_type => 'IFACE',
p_error_code => 'OZF_RESALE_INV_LEVEL_ERROR',
p_column_name => NULL,
p_column_value => NULL,
x_return_status => l_return_status
);
UPDATE ozf_resale_lines_int_all
SET resale_batch_id = null,
request_id = null,
status_code = 'DISPUTED',
dispute_code = 'OZF_LT_INVT'
WHERE resale_line_int_id = l_resale_int_rec.resale_line_int_id;*/
OZF_RESALE_COMMON_PVT.Insert_Resale_Header(
p_api_version => 1
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_line_int_rec => l_resale_int_rec
,x_header_id => l_header_id
,x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
);
p_approver_id => l_last_updated_by,
p_run_mode => p_run_mode, -- Bug 14194884 - TPA In Draft Mode ER
p_draft_run_id => p_draft_run_id,
x_return_status => l_return_status,
x_ozf_act_budgets_tbl => x_ozf_act_budgets_tbl,
x_ozf_funds_new_tbl => x_ozf_funds_new_tbl,
x_draft_accrual_tbl => x_draft_accrual_tbl
);
UPDATE ozf_resale_lines_int_all
SET resale_batch_id = NULL,
request_id = NULL,
status_code = 'DISPUTED',
dispute_code = 'OZF_PRIC_RESULT_ERR'
WHERE resale_line_int_id = p_line_tbl(i).chargeback_int_id;
UPDATE ozf_resale_lines_int_all
SET status_code= 'CLOSED'
WHERE resale_line_int_id = p_line_tbl(i).chargeback_int_id;
OZF_RESALE_COMMON_PVT.Insert_Resale_Log (
p_id_value => l_resale_int_rec.resale_line_int_id,
p_id_type => 'IFACE',
p_error_code => l_dispute_code,
p_column_name => NULL,
p_column_value => NULL,
x_return_status => l_return_status
);
UPDATE ozf_resale_lines_int_all
SET resale_batch_id = null,
request_id = null,
status_code = 'DISPUTED',
dispute_code = l_dispute_code
WHERE resale_line_int_id = l_resale_int_rec.resale_line_int_id;
UPDATE ozf_resale_lines_int_all
SET status_code = 'DISPUTED',
dispute_code = l_dispute_code
WHERE resale_line_int_id = l_resale_int_rec.resale_line_int_id;
SELECT DISTINCT a.resale_header_id --Bug# 8328719 fixed by ateotia
, a.header_attribute_category
, a.header_attribute1
, a.header_attribute2
, a.header_attribute3
, a.header_attribute4
, a.header_attribute5
, a.header_attribute6
, a.header_attribute7
, a.header_attribute8
, a.header_attribute9
, a.header_attribute10
, a.header_attribute11
, a.header_attribute12
, a.header_attribute13
, a.header_attribute14
, a.header_attribute15
FROM ozf_resale_headers_all a
, ozf_resale_lines_all b
, ozf_resale_batch_line_maps_all c
WHERE a.resale_header_id = b.resale_header_id
AND b.resale_line_id = c.resale_line_id
AND c.resale_batch_id = p_resale_batch_id;
SELECT *
FROM ozf_resale_headers
WHERE resale_header_id = p_header_id;
SELECT *
FROM ozf_resale_lines
WHERE resale_header_id = p_header_id;
x_fund_update_status VARCHAR2(30) := OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED;
x_budget_update_status VARCHAR2(30) := OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED;
l_control_rec.TEMP_TABLE_INSERT_FLAG := 'N';
l_header_id_tbl.DELETE;
IF l_order_set_tbl.EXISTS(1) THEN l_order_set_tbl.DELETE; END IF;
IF l_line_tbl.EXISTS(1) THEN l_line_tbl.DELETE; END IF;
IF l_ldets_tbl.EXISTS(1) THEN l_ldets_tbl.DELETE; END IF;
IF l_related_lines_tbl.EXISTS(1) THEN l_related_lines_tbl.DELETE; END IF;
IF OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL.EXISTS(1) THEN OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL.DELETE; END IF;
IF OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL.EXISTS(1) THEN OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL.DELETE; END IF;
DELETE FROM ozf_resale_logs
WHERE resale_id = l_order_set_tbl(J).resale_line_id
AND resale_id_type = OZF_RESALE_COMMON_PVT.G_ID_TYPE_LINE;
OZF_RESALE_LOGS_PKG.Insert_Row(
px_resale_log_id => l_log_id,
p_resale_id => l_order_set_tbl(p).resale_line_id,
p_resale_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_LINE,
p_error_code => 'OZF_GET_ORDER_PRIC_ERR',
p_error_message => fnd_message.get_string('OZF','OZF_GET_ORDER_PRIC_ERR'),
p_column_name => NULL,
p_column_value => NULL,
--px_org_id => OZF_RESALE_COMMON_PVT.g_org_id
px_org_id => l_org_id
);
OZF_RESALE_LOGS_PKG.Insert_Row(
px_resale_log_id => l_log_id,
p_resale_id => l_order_set_tbl(i).resale_line_id,
p_resale_id_type => OZF_RESALE_COMMON_PVT.G_ID_TYPE_LINE,
p_error_code => 'OZF_PROC_PRIC_RESLT_ERR',
p_error_message => fnd_message.get_string('OZF','OZF_PROC_PRIC_RESLT_ERR'),
p_column_name => NULL,
p_column_value => NULL,
--px_org_id => OZF_RESALE_COMMON_PVT.g_org_id
px_org_id => l_org_id
);
OZF_UTILITY_PVT.UPDATE_OZF_FUNDS_ALL_B(p_resale_batch_id, x_return_status, x_fund_update_status, l_ozf_funds_new_tbl, 'TP_ACCRUAL');
OZF_UTILITY_PVT.UPDATE_OZF_ACT_BUDGETS(p_resale_batch_id, x_return_status, x_budget_update_status, l_ozf_act_budgets_tbl, 'TP_ACCRUAL');
IF x_fund_update_status = 'PENDING_ACCRUALS' OR x_budget_update_status = 'PENDING_ACCRUALS' THEN
UPDATE ozf_resale_batches_all
SET status_code = 'PENDING_ACCRUALS'
WHERE resale_batch_id = p_resale_batch_id;
ozf_utility_pvt.write_conc_log('Updated batch status to Pending Accruals - p_resale_batch_id: ' || p_resale_batch_id);
OZF_UTILITY_PVT.debug_message('x_fund_update_status: ' || x_fund_update_status);
OZF_UTILITY_PVT.debug_message('x_budget_update_status: ' || x_budget_update_status);
OZF_UTILITY_PVT.debug_message('Updated batch status to Pending Accruals');
SELECT (TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10)))
FROM dual; */
SELECT org_id
FROM ozf_resale_batches_all
WHERE resale_batch_id = cv_resale_batch_id;
SELECT distinct order_NUMBER,
bill_to_cust_account_id,
date_ordered
FROM ozf_resale_lines_int
WHERE status_code=OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
AND direct_customer_flag = 'F'
AND resale_batch_id = p_resale_batch_id
ORDER BY date_ordered;
SELECT *
FROM ozf_resale_lines_int
WHERE order_NUMBER = p_num
AND bill_to_cust_account_id = p_name
AND date_ordered = p_date
AND direct_customer_flag ='F'
AND status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
AND resale_batch_id = p_resale_batch_id;
l_order_num_tbl.DELETE;
DELETE FROM ozf_resale_logs_all a
WHERE a.resale_id_type = 'IFACE'
AND a.resale_id IN (
SELECT resale_line_int_id
FROM ozf_resale_lines_int_all b
WHERE b.direct_customer_flag = 'F'
AND b.status_code = 'OPEN'
AND b.order_number = l_order_num_tbl(i).order_number
AND b.bill_to_cust_account_id = l_order_num_tbl(i).bill_to_cust_account_id
AND b.date_ordered = l_order_num_tbl(i).date_ordered
AND b.resale_batch_id = p_resale_batch_id
);
OZF_RESALE_COMMON_PVT.Insert_resale_header(
p_api_version => 1
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_line_int_rec => l_resale_int_tbl(k)
,x_header_id => l_header_id
,x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
);
OZF_RESALE_COMMON_PVT.Insert_resale_line(
p_api_version => 1
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_line_int_rec => l_resale_int_tbl(k)
,p_header_id => l_header_id
,x_line_id => l_line_id
,x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
);
UPDATE ozf_resale_lines_int
SET status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
WHERE direct_customer_flag ='F'
AND status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
AND resale_batch_id = p_resale_batch_id;
SELECT DISTINCT order_NUMBER,
bill_to_cust_account_id,
date_ordered
FROM ozf_resale_lines_int_all
WHERE status_code IN ('OPEN', 'DUPLICATED')
AND duplicated_adjustment_id IS NULL
AND resale_batch_id = p_resale_batch_id
AND direct_customer_flag <> 'F' -- TPA in DRAFT mode ER : will pick up only the direct customer orders
ORDER BY date_ordered; -- as the indirect customer orders are taken care of in Move_Indirect_Customer_Order
SELECT *
FROM ozf_resale_lines_int_all
WHERE order_number = p_order_number
AND bill_to_cust_account_id= p_id
AND date_ordered = p_date
AND status_code IN ('OPEN', 'DUPLICATED')
AND duplicated_adjustment_id is NULL
AND resale_batch_id = p_resale_batch_id
AND tracing_flag = 'F';
SELECT *
FROM ozf_resale_lines_int_all
WHERE order_number = p_order_number
AND bill_to_cust_account_id = p_id
AND date_ordered = p_date
AND status_code = 'DUPLICATED'
AND duplicated_adjustment_id IS NOT NULL
AND resale_batch_id = p_resale_batch_id
AND tracing_flag = 'F';
SELECT *
FROM ozf_resale_lines_int_all
WHERE order_number = p_order_number -- ?? need this
AND bill_to_cust_account_id = p_id -- ?? need this
AND date_ordered = p_date
AND status_code IN ('OPEN', 'DUPLICATED')
AND resale_batch_id = p_resale_batch_id
AND tracing_flag = 'T';
SELECT COUNT(1)
FROM ozf_resale_lines_int_all
WHERE status_code IN ('PROCESSED', 'CLOSED', 'DUPLICATED')
AND resale_batch_id = cv_batch_id;
SELECT batch_number
FROM ozf_resale_batches_all
WHERE resale_batch_id = cv_batch_id;
SELECT i.resale_line_int_id id
, lk.meaning dispute_code
, lg.error_message
, lg.column_name
, lg.column_value
FROM ozf_resale_lines_int_all i
, ozf_resale_logs_all lg
, ozf_lookups lk
WHERE i.dispute_code = lk.lookup_code(+)
AND lk.lookup_type(+) = 'OZF_RESALE_DISPUTE_CODE'
AND i.status_code = 'DISPUTED'
AND i.resale_batch_id = cv_batch_id
AND i.resale_line_int_id = lg.resale_id (+)
ORDER BY i.resale_line_int_id;
SELECT i.resale_line_int_id id
, DECODE(lg.column_value,'-1', 'RESALE_LINE_INT_ID', '-2', 'RESALE_LINE_ID') column_name
, i.duplicated_line_id column_value
FROM ozf_resale_lines_int_all i
, ozf_resale_logs_all lg
WHERE i.status_code = 'DUPLICATED'
AND i.resale_batch_id = cv_batch_id
AND i.resale_line_int_id = lg.resale_id
ORDER BY i.resale_line_int_id;
x_fund_update_status VARCHAR2(30) := OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED;
x_budget_update_status VARCHAR2(30) := OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED;
l_output_dispute_line_tbl.DELETE;
OZF_RESALE_COMMON_PVT.Update_Duplicates (
p_api_version => 1
,p_init_msg_LIST => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_resale_batch_id => p_resale_batch_id
,p_resale_batch_type => OZF_RESALE_COMMON_PVT.G_TP_ACCRUAL
,p_batch_status => l_batch_status
,x_batch_status => l_new_batch_status
,x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
);
l_output_duplicate_line_tbl.DELETE;
UPDATE ozf_resale_lines_int
SET resale_batch_id = null
, request_id = null -- [BUG 4233341 FIXING]
Where resale_batch_id = p_resale_batch_id
and status_code = 'DISPUTED';
l_control_rec.temp_table_insert_flag := 'N';
l_order_num_tbl.DELETE;
UPDATE ozf_resale_lines_int_all
SET status_code= 'PROCESSED'
WHERE status_code = 'OPEN'
AND order_number = l_order_num_tbl(i).order_number
AND bill_to_cust_account_id = l_order_num_tbl(i).bill_to_cust_account_id
AND date_ordered = l_order_num_tbl(i).date_ordered
AND tracing_flag = 'T'
AND resale_batch_id = p_resale_batch_id; -- bug 5222273
IF l_order_set_tbl.EXISTS(1) THEN l_order_set_tbl.DELETE; END IF;
IF l_line_tbl.EXISTS(1) THEN l_line_tbl.DELETE; END IF;
IF l_ldets_tbl.EXISTS(1) THEN l_ldets_tbl.DELETE; END IF;
IF l_related_lines_tbl.EXISTS(1) THEN l_related_lines_tbl.DELETE; END IF;
IF OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL.EXISTS(1) THEN OZF_ORDER_PRICE_PVT.G_LINE_REC_TBL.DELETE; END IF;
IF OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL.EXISTS(1) THEN OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL.DELETE; END IF;
DELETE FROM ozf_resale_logs
WHERE resale_id = l_order_set_tbl(j).resale_line_int_id
AND resale_id_type = 'IFACE';
UPDATE ozf_resale_lines_int
SET status_code = 'DISPUTED'
WHERE status_code = 'OPEN'
AND order_number = l_order_num_tbl(i).order_number
AND bill_to_cust_account_id = l_order_num_tbl(i).bill_to_cust_account_id
AND date_ordered = l_order_num_tbl(i).date_ordered
AND resale_batch_id = p_resale_batch_id;
OZF_RESALE_LOGS_PKG.Insert_Row(
px_resale_log_id => l_log_id,
p_resale_id => l_order_set_tbl(p).resale_line_int_id,
p_resale_id_type => 'IFACE',
p_error_code => 'OZF_GET_ORDER_PRIC_ERR',
p_error_message => FND_MESSAGE.get_string('OZF','OZF_GET_ORDER_PRIC_ERR'),
p_column_name => NULL,
p_column_value => NULL,
--px_org_id => OZF_RESALE_COMMON_PVT.g_org_id
px_org_id => l_org_id
);
OZF_RESALE_LOGS_PKG.Insert_Row(
px_resale_log_id => l_log_id,
p_resale_id => l_order_set_tbl(p).resale_line_int_id,
p_resale_id_type => 'IFACE',
p_error_code => 'OZF_PRIC_RESULT_ERR',
p_error_message => FND_MESSAGE.get_string('OZF','OZF_PRIC_RESULT_ERR'),
p_column_name => NULL,
p_column_value => NULL,
--px_org_id => OZF_RESALE_COMMON_PVT.g_org_id
px_org_id => l_org_id
);
UPDATE ozf_resale_lines_int_all
SET status_code = 'DISPUTED'
WHERE status_code = 'OPEN'
AND order_number = l_order_num_tbl(i).order_number
AND bill_to_cust_account_id = l_order_num_tbl(i).bill_to_cust_account_id
AND date_ordered = l_order_num_tbl(i).date_ordered
AND resale_batch_id = p_resale_batch_id;
INSERT INTO
(SELECT DRAFT_UTILIZATION_ID,
DRAFT_RUN_ID,
OBJECT_TYPE,
OBJECT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
UTILIZATION_TYPE,
PLAN_TYPE,
PLAN_ID,
ACCTD_AMOUNT,
EXCHANGE_RATE_TYPE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE,
ORG_ID,
PRODUCT_ID,
CUST_ACCOUNT_ID,
PRODUCT_LEVEL_TYPE,
PLAN_CURR_AMOUNT,
ACTIVITY_PRODUCT_ID,
BILLTO_CUST_ACCOUNT_ID,
REFERENCE_TYPE,
REFERENCE_ID,
SHIP_TO_SITE_USE_ID,
BILL_TO_SITE_USE_ID,
PLAN_CURRENCY_CODE,
DISCOUNT_TYPE,
DISCOUNT_AMOUNT,
DISCOUNT_AMOUNT_CURRENCY_CODE,
LIST_LINE_ID
FROM OZF_FUNDS_UTILIZED_DRAFT_ALL)
VALUES l_ozf_draft_accrual_tbl(ip);
INSERT INTO OZF_FUNDS_UTILIZED_DRAFT_ALL
( DRAFT_UTILIZATION_ID,
DRAFT_RUN_ID,
OBJECT_TYPE,
OBJECT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
UTILIZATION_TYPE,
PLAN_TYPE,
PLAN_ID,
ACCTD_AMOUNT,
EXCHANGE_RATE_TYPE,
EXCHANGE_RATE_DATE,
EXCHANGE_RATE,
ORG_ID,
PRODUCT_ID,
CUST_ACCOUNT_ID,
PRODUCT_LEVEL_TYPE,
PLAN_CURR_AMOUNT,
ACTIVITY_PRODUCT_ID,
BILLTO_CUST_ACCOUNT_ID,
REFERENCE_TYPE,
REFERENCE_ID,
SHIP_TO_SITE_USE_ID,
BILL_TO_SITE_USE_ID,
PLAN_CURRENCY_CODE,
DISCOUNT_TYPE,
DISCOUNT_AMOUNT,
DISCOUNT_AMOUNT_CURRENCY_CODE,
LIST_LINE_ID)
VALUES ( l_ozf_draft_accrual_tbl(ip).DRAFT_UTILIZATION_ID
, l_ozf_draft_accrual_tbl(ip).DRAFT_RUN_ID
, l_ozf_draft_accrual_tbl(ip).object_type
, l_ozf_draft_accrual_tbl(ip).object_id
, l_ozf_draft_accrual_tbl(ip).LAST_UPDATE_DATE
, l_ozf_draft_accrual_tbl(ip).LAST_UPDATED_BY
, l_ozf_draft_accrual_tbl(ip).LAST_UPDATE_LOGIN
, l_ozf_draft_accrual_tbl(ip).CREATION_DATE
, l_ozf_draft_accrual_tbl(ip).CREATED_BY
, l_ozf_draft_accrual_tbl(ip).REQUEST_ID
, l_ozf_draft_accrual_tbl(ip).PROGRAM_APPLICATION_ID
, l_ozf_draft_accrual_tbl(ip).PROGRAM_ID
, l_ozf_draft_accrual_tbl(ip).PROGRAM_UPDATE_DATE
, l_ozf_draft_accrual_tbl(ip).utilization_type
, l_ozf_draft_accrual_tbl(ip).plan_type
, l_ozf_draft_accrual_tbl(ip).plan_id
, l_ozf_draft_accrual_tbl(ip).acctd_amount
, l_ozf_draft_accrual_tbl(ip).exchange_rate_type
, l_ozf_draft_accrual_tbl(ip).EXCHANGE_RATE_DATE
, l_ozf_draft_accrual_tbl(ip).exchange_rate
, l_ozf_draft_accrual_tbl(ip).org_id
, l_ozf_draft_accrual_tbl(ip).product_id
, l_ozf_draft_accrual_tbl(ip).cust_account_id
, l_ozf_draft_accrual_tbl(ip).product_level_type
, l_ozf_draft_accrual_tbl(ip).plan_curr_amount
, l_ozf_draft_accrual_tbl(ip).activity_product_id
, l_ozf_draft_accrual_tbl(ip).billto_cust_account_id
, l_ozf_draft_accrual_tbl(ip).REFERENCE_TYPE -- Null for TPA From Interface Table
, l_ozf_draft_accrual_tbl(ip).REFERENCE_ID -- Null for TPA From Interface Table
, l_ozf_draft_accrual_tbl(ip).ship_to_site_use_id
, l_ozf_draft_accrual_tbl(ip).bill_to_site_use_id
, l_ozf_draft_accrual_tbl(ip).plan_currency_code
, l_ozf_draft_accrual_tbl(ip).discount_type
, l_ozf_draft_accrual_tbl(ip).discount_amount
, l_ozf_draft_accrual_tbl(ip).discount_amount_currency_code
, l_ozf_draft_accrual_tbl(ip).LIST_LINE_ID
);
OZF_UTILITY_PVT.UPDATE_OZF_FUNDS_ALL_B(p_resale_batch_id, x_return_status, x_fund_update_status, l_ozf_funds_new_tbl, 'TP_ACCRUAL');
OZF_UTILITY_PVT.UPDATE_OZF_ACT_BUDGETS(p_resale_batch_id, x_return_status, x_budget_update_status, l_ozf_act_budgets_tbl, 'TP_ACCRUAL');
IF x_fund_update_status = 'PENDING_ACCRUALS' OR x_budget_update_status = 'PENDING_ACCRUALS' THEN
l_batch_status := 'PENDING_ACCRUALS';
OZF_UTILITY_PVT.debug_message('x_fund_update_status: ' || x_fund_update_status);
OZF_UTILITY_PVT.debug_message('x_budget_update_status: ' || x_budget_update_status);
OZF_UTILITY_PVT.debug_message('Updated batch status to Pending Accruals');
l_output_dispute_line_tbl.DELETE;
UPDATE ozf_resale_lines_int_all
SET resale_batch_id = null
, request_id = null
WHERE resale_batch_id = p_resale_batch_id
AND status_code IN ('NEW', 'OPEN', 'DISPUTED'); -- 'PROCESSED'
UPDATE ozf_resale_batches_all
SET status_code = l_batch_status
, batch_count = l_valid_line_count
WHERE resale_batch_id = p_resale_batch_id;
DELETE FROM ozf_resale_batches_all
WHERE resale_batch_id = p_resale_batch_id;
SELECT resale_batch_id
FROM ozf_resale_batches
WHERE data_source_code = p_data_source_code;
SELECT distinct sold_from_cust_account_id, org_id, currency_code
FROM ozf_resale_lines_int
WHERE resale_batch_id IS NULL
AND request_id = p_id
AND org_id = l_org_id; -- nepanda : Fix for bug # 15900683
SELECT hca.party_id
-- bug # 7375849 fixed by ateotia (+)
--, substr(hp.party_name, 1,30)
, hp.party_name
-- bug # 7375849 fixed by ateotia (-)
FROM hz_cust_accounts hca
, hz_parties hp
WHERE hca.cust_account_id = p_id
AND hca.party_id = hp.party_id;
SELECT ozf_resale_batches_all_s.nextval
FROM dual;
SELECT to_char(ozf_resale_batch_number_s.nextval)
FROM dual;
SELECT orli.created_from
, orli.data_source_code
, orli.sold_from_cust_account_id
, orli.sold_from_site_id
, orli.sold_from_contact_party_id
, orli.sold_from_contact_name
, orli.sold_from_email
, orli.sold_from_phone
, orli.sold_from_fax
,orli.currency_code
FROM ozf_resale_lines_int orli
WHERE orli.resale_batch_id IS NULL
AND orli.sold_from_cust_account_id = p_id
AND orli.request_id = G_CONC_REQUEST_ID
AND orli.org_id = p_org_id
AND orli.currency_code = p_currency_code
AND rownum = 1;
SELECT MIN(date_ordered), MAX(date_ordered)
FROM ozf_resale_lines_int_all
WHERE sold_from_cust_account_id = p_account_id
AND request_id = FND_GLOBAL.CONC_REQUEST_ID
AND org_id = p_org_id
AND currency_code = p_currency_code;
SELECT resale_line_int_id FROM OZF_RESALE_LINES_INT_ALL_TEMP ;
SELECT
OZF_RESALE_DRAFT_LOG_S.NEXTVAL RESALE_DRAFT_LOG_ID
,logs.resale_id RESALE_ID
,l_draft_run_id DRAFT_RUN_ID
,logs.resale_id_type RESALE_ID_TYPE
,logs.error_code ERROR_CODE
,logs.error_message ERROR_MESSAGE
,logs.column_name COLUMN_NAME
,logs.column_value COLUMN_VALUE
,logs.org_id ORG_ID
,SYSDATE CREATION_DATE
,NVL (fnd_global.user_id, -1) CREATED_BY
,SYSDATE LAST_UPDATE_DATE
,NVL (fnd_global.user_id, -1) LAST_UPDATED_BY
,NVL (fnd_global.conc_login_id, -1) LAST_UPDATE_LOGIN
,OZF_TP_ACCRUAL_PVT.G_CONC_REQUEST_ID REQUEST_ID
,fnd_global.prog_appl_id PROGRAM_APPLICATION_ID
,fnd_global.conc_program_id PROGRAM_ID
,SYSDATE PROGRAM_UPDATE_DATE
FROM ozf_resale_logs_all logs, ozf_resale_lines_int_draft_all intDraft -- Fix for Bug 14584673
WHERE logs.resale_id = intDraft.resale_line_int_id
AND intDraft.draft_run_id = l_draft_run_id
AND intDraft.org_id = l_org_id; -- nepanda : Fix for bug # 15900683
SELECT COUNT(*) FROM ozf_resale_lines_int_all
WHERE REQUEST_ID = G_CONC_REQUEST_ID
AND ORG_ID = l_org_id ;
UPDATE ozf_resale_lines_int_all
SET request_id = G_CONC_REQUEST_ID
, dispute_code = null
, program_application_id = FND_GLOBAL.PROG_APPL_ID
, program_update_date = SYSDATE
, program_id = FND_GLOBAL.CONC_PROGRAM_ID
WHERE resale_batch_id IS NULL
AND request_id IS NULL
AND org_id = l_org_id; -- nepanda : Fix for bug # 15900683
UPDATE ozf_resale_lines_int_all
SET request_id = G_CONC_REQUEST_ID
, dispute_code = null
, program_application_id = FND_GLOBAL.PROG_APPL_ID
, program_update_date = SYSDATE
, program_id = FND_GLOBAL.CONC_PROGRAM_ID
WHERE resale_batch_id IS NULL
AND data_source_code = p_data_source_code
AND request_id IS NULL
AND org_id = l_org_id; -- nepanda : Fix for bug # 15900683
DELETE FROM ozf_resale_logs_all
WHERE resale_id IN (SELECT resale_line_int_id
FROM ozf_resale_lines_int
WHERE request_id = G_CONC_REQUEST_ID
AND org_id = l_org_id ) -- nepanda : Fix for bug # 15900683
AND resale_id_type = 'IFACE';
SELECT OZF_DRAFT_RUN_S.nextval INTO l_draft_run_id FROM DUAL;
OZF_RESALE_BATCHES_PKG.Insert_Row(
px_resale_batch_id => l_resale_batch_id,
px_object_version_number => l_obj_number,
p_last_update_date => SYSdate,
p_last_updated_by => NVL(FND_GLOBAL.user_id,-1),
p_creation_date => SYSdate,
p_request_id => G_CONC_REQUEST_ID,
p_created_by => NVL(FND_GLOBAL.user_id,-1),
p_last_update_login => NVL(FND_GLOBAL.conc_login_id,-1),
p_program_application_id => FND_GLOBAL.PROG_APPL_ID,
p_program_update_date => SYSdate,
p_program_id => FND_GLOBAL.CONC_PROGRAM_ID,
p_created_from => l_created_from,
p_batch_number => l_resale_batch_number,
p_batch_type => OZF_RESALE_COMMON_PVT.G_TP_ACCRUAL,
p_batch_count => NULL,
p_year => NULL,
p_month => NULL,
p_report_date => trunc(sysdate),
p_report_start_date => trunc(l_start_date),
p_report_end_date => trunc(l_end_date),
p_status_code => OZF_RESALE_COMMON_PVT.G_BATCH_NEW,
p_data_source_code => l_data_source_code,
p_reference_type => NULL,
p_reference_number => NULL,
p_comments => NULL,
p_partner_claim_number => NULL,
p_transaction_purpose_code => NULL,
p_transaction_type_code => NULL,
p_partner_type => NULL,
p_partner_id => NULL,
p_partner_party_id => l_party_id,
p_partner_cust_account_id => l_sold_from_cust_id_tbl(j) ,
p_partner_site_id => l_sold_from_site_id,
p_partner_contact_party_id => l_sold_from_contact_party_id ,
p_partner_contact_name => l_sold_from_contact_name,
p_partner_email => l_sold_from_email,
p_partner_phone => l_sold_from_phone,
p_partner_fax => l_sold_from_fax,
p_header_tolerance_operand => NULL,
p_header_tolerance_calc_code => NULL,
p_line_tolerance_operand => NULL,
p_line_tolerance_calc_code => NULL,
p_currency_code => l_currency_code_tbl(j),
p_claimed_amount => NULL,
p_allowed_amount => NULL,
p_paid_amount => NULL,
p_disputed_amount => NULL,
p_accepted_amount => NULL,
p_lines_invalid => NULL,
p_lines_w_tolerance => NULL,
p_lines_disputed => NULL,
p_batch_set_id_code => NULL,
p_credit_code => NULL,
p_credit_advice_date => NULL,
p_purge_flag => NULL,
p_attribute_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
px_org_id => l_org_id_tbl(j),
p_direct_order_flag => l_direct_order_flag); -- For Bug#9447673 SSD IDSM ER
UPDATE ozf_resale_lines_int_all orli
SET resale_batch_id = l_resale_batch_id
WHERE orli.sold_from_cust_account_id = l_sold_from_cust_id_tbl(j)
AND orli.org_id = l_org_id_tbl(j)
AND orli.currency_code = l_currency_code_tbl(j)
AND orli.request_id = G_CONC_REQUEST_ID;
INSERT INTO OZF_RESALE_LINES_INT_DRAFT_ALL
( RESALE_LINES_INT_DRAFT_ID,
DRAFT_RUN_ID,
RESALE_LINE_INT_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
REQUEST_ID,
CREATED_BY,
CREATED_FROM,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
PROGRAM_ID,
STATUS_CODE,
RESALE_TRANSFER_TYPE,
PRODUCT_TRANSFER_MOVEMENT_TYPE,
PRODUCT_TRANSFER_DATE,
TRACING_FLAG,
SHIP_FROM_CUST_ACCOUNT_ID,
SHIP_FROM_SITE_ID,
SHIP_FROM_PARTY_NAME,
SHIP_FROM_LOCATION,
SHIP_FROM_ADDRESS,
SHIP_FROM_CITY,
SHIP_FROM_STATE,
SHIP_FROM_POSTAL_CODE,
SHIP_FROM_COUNTRY,
SHIP_FROM_CONTACT_PARTY_ID,
SHIP_FROM_CONTACT_NAME,
SHIP_FROM_EMAIL,
SHIP_FROM_FAX,
SHIP_FROM_PHONE,
SOLD_FROM_CUST_ACCOUNT_ID,
SOLD_FROM_SITE_ID,
SOLD_FROM_PARTY_NAME,
SOLD_FROM_LOCATION,
SOLD_FROM_ADDRESS,
SOLD_FROM_CITY,
SOLD_FROM_STATE,
SOLD_FROM_POSTAL_CODE,
SOLD_FROM_COUNTRY,
SOLD_FROM_CONTACT_PARTY_ID,
SOLD_FROM_CONTACT_NAME,
SOLD_FROM_EMAIL,
SOLD_FROM_PHONE,
SOLD_FROM_FAX,
BILL_TO_CUST_ACCOUNT_ID,
BILL_TO_SITE_USE_ID,
BILL_TO_PARTY_ID,
BILL_TO_PARTY_SITE_ID,
BILL_TO_PARTY_NAME,
BILL_TO_DUNS_NUMBER,
BILL_TO_LOCATION,
BILL_TO_ADDRESS,
BILL_TO_CITY,
BILL_TO_STATE,
BILL_TO_POSTAL_CODE,
BILL_TO_COUNTRY,
BILL_TO_CONTACT_PARTY_ID,
BILL_TO_CONTACT_NAME,
BILL_TO_EMAIL,
BILL_TO_PHONE,
BILL_TO_FAX,
SHIP_TO_CUST_ACCOUNT_ID,
SHIP_TO_SITE_USE_ID,
SHIP_TO_PARTY_ID,
SHIP_TO_PARTY_SITE_ID,
SHIP_TO_PARTY_NAME,
SHIP_TO_DUNS_NUMBER,
SHIP_TO_LOCATION,
SHIP_TO_ADDRESS,
SHIP_TO_CITY,
SHIP_TO_COUNTRY,
SHIP_TO_POSTAL_CODE,
SHIP_TO_STATE,
SHIP_TO_CONTACT_PARTY_ID,
SHIP_TO_CONTACT_NAME,
SHIP_TO_EMAIL,
SHIP_TO_PHONE,
SHIP_TO_FAX,
END_CUST_PARTY_ID,
END_CUST_SITE_USE_ID,
END_CUST_SITE_USE_CODE,
END_CUST_PARTY_SITE_ID,
END_CUST_PARTY_NAME,
END_CUST_LOCATION,
END_CUST_ADDRESS,
END_CUST_CITY,
END_CUST_STATE,
END_CUST_POSTAL_CODE,
END_CUST_COUNTRY,
END_CUST_CONTACT_PARTY_ID,
END_CUST_CONTACT_NAME,
END_CUST_EMAIL,
END_CUST_PHONE,
END_CUST_FAX,
DIRECT_CUSTOMER_FLAG,
ORDER_TYPE_ID,
ORDER_TYPE,
ORDER_CATEGORY,
AGREEMENT_TYPE,
AGREEMENT_ID,
AGREEMENT_NAME,
AGREEMENT_PRICE,
AGREEMENT_UOM_CODE,
CORRECTED_AGREEMENT_ID,
CORRECTED_AGREEMENT_NAME,
PRICE_LIST_ID,
PRICE_LIST_NAME,
ORIG_SYSTEM_REFERENCE,
ORIG_SYSTEM_LINE_REFERENCE,
ORIG_SYSTEM_CURRENCY_CODE,
ORIG_SYSTEM_SELLING_PRICE,
ORIG_SYSTEM_QUANTITY,
ORIG_SYSTEM_UOM,
ORIG_SYSTEM_PURCHASE_UOM,
ORIG_SYSTEM_PURCHASE_CURR,
ORIG_SYSTEM_PURCHASE_PRICE,
ORIG_SYSTEM_PURCHASE_QUANTITY,
ORIG_SYSTEM_AGREEMENT_UOM,
ORIG_SYSTEM_AGREEMENT_NAME,
ORIG_SYSTEM_AGREEMENT_TYPE,
ORIG_SYSTEM_AGREEMENT_STATUS,
ORIG_SYSTEM_AGREEMENT_CURR,
ORIG_SYSTEM_AGREEMENT_PRICE,
ORIG_SYSTEM_AGREEMENT_QUANTITY,
ORIG_SYSTEM_ITEM_NUMBER,
CURRENCY_CODE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
EXCHANGE_RATE_DATE,
PO_NUMBER,
PO_RELEASE_NUMBER,
PO_TYPE,
INVOICE_NUMBER,
DATE_INVOICED,
ORDER_NUMBER,
DATE_ORDERED,
DATE_SHIPPED,
CLAIMED_AMOUNT,
ALLOWED_AMOUNT,
TOTAL_ALLOWED_AMOUNT,
ACCEPTED_AMOUNT,
TOTAL_ACCEPTED_AMOUNT,
LINE_TOLERANCE_AMOUNT,
TOLERANCE_FLAG,
TOTAL_CLAIMED_AMOUNT,
PURCHASE_PRICE,
PURCHASE_UOM_CODE,
ACCTD_PURCHASE_PRICE,
SELLING_PRICE,
ACCTD_SELLING_PRICE,
UOM_CODE,
QUANTITY,
CALCULATED_PRICE,
ACCTD_CALCULATED_PRICE,
CALCULATED_AMOUNT,
CREDIT_CODE,
CREDIT_ADVICE_DATE,
UPC_CODE,
INVENTORY_ITEM_ID,
ITEM_NUMBER,
ITEM_DESCRIPTION,
INVENTORY_ITEM_SEGMENT1,
INVENTORY_ITEM_SEGMENT2,
INVENTORY_ITEM_SEGMENT3,
INVENTORY_ITEM_SEGMENT4,
INVENTORY_ITEM_SEGMENT5,
INVENTORY_ITEM_SEGMENT6,
INVENTORY_ITEM_SEGMENT7,
INVENTORY_ITEM_SEGMENT8,
INVENTORY_ITEM_SEGMENT9,
INVENTORY_ITEM_SEGMENT10,
INVENTORY_ITEM_SEGMENT11,
INVENTORY_ITEM_SEGMENT12,
INVENTORY_ITEM_SEGMENT13,
INVENTORY_ITEM_SEGMENT14,
INVENTORY_ITEM_SEGMENT15,
INVENTORY_ITEM_SEGMENT16,
INVENTORY_ITEM_SEGMENT17,
INVENTORY_ITEM_SEGMENT18,
INVENTORY_ITEM_SEGMENT19,
INVENTORY_ITEM_SEGMENT20,
PRODUCT_CATEGORY_ID,
CATEGORY_NAME,
DUPLICATED_LINE_ID,
DUPLICATED_ADJUSTMENT_ID,
RESPONSE_TYPE,
RESPONSE_CODE,
REJECT_REASON_CODE,
FOLLOWUP_ACTION_CODE,
NET_ADJUSTED_AMOUNT,
DISPUTE_CODE,
DATA_SOURCE_CODE,
HEADER_ATTRIBUTE_CATEGORY,
HEADER_ATTRIBUTE1,
HEADER_ATTRIBUTE2,
HEADER_ATTRIBUTE3,
HEADER_ATTRIBUTE4,
HEADER_ATTRIBUTE5,
HEADER_ATTRIBUTE6,
HEADER_ATTRIBUTE7,
HEADER_ATTRIBUTE8,
HEADER_ATTRIBUTE9,
HEADER_ATTRIBUTE10,
HEADER_ATTRIBUTE11,
HEADER_ATTRIBUTE12,
HEADER_ATTRIBUTE13,
HEADER_ATTRIBUTE14,
HEADER_ATTRIBUTE15,
LINE_ATTRIBUTE_CATEGORY,
LINE_ATTRIBUTE1,
LINE_ATTRIBUTE2,
LINE_ATTRIBUTE3,
LINE_ATTRIBUTE4,
LINE_ATTRIBUTE5,
LINE_ATTRIBUTE6,
LINE_ATTRIBUTE7,
LINE_ATTRIBUTE8,
LINE_ATTRIBUTE9,
LINE_ATTRIBUTE10,
LINE_ATTRIBUTE11,
LINE_ATTRIBUTE12,
LINE_ATTRIBUTE13,
LINE_ATTRIBUTE14,
LINE_ATTRIBUTE15,
ORG_ID,
SECURITY_GROUP_ID,
INVOICE_LINE_NUMBER,
ORDER_LINE_NUMBER,
SUPPLIER_ITEM_COST,
SUPPLIER_ITEM_UOM)
(SELECT OZF_RESALE_LINES_INT_DRAFT_S.NEXTVAL, -- RESALE_LINES_INT_DRAFT_ID
l_draft_run_id, -- DRAFT_RUN_ID
RESALE_LINE_INT_ID, -- RESALE_LINE_INT_ID
OBJECT_VERSION_NUMBER, -- OBJECT_VERSION_NUMBER
LAST_UPDATE_DATE, -- LAST_UPDATE_DATE
LAST_UPDATED_BY, -- LAST_UPDATED_BY
CREATION_DATE, -- CREATION_DATE
REQUEST_ID, -- REQUEST_ID
CREATED_BY, -- CREATED_BY
CREATED_FROM, -- CREATED_FROM
LAST_UPDATE_LOGIN, -- LAST_UPDATE_LOGIN
PROGRAM_APPLICATION_ID, -- PROGRAM_APPLICATION_ID
PROGRAM_UPDATE_DATE, -- PROGRAM_UPDATE_DATE
PROGRAM_ID, -- PROGRAM_ID
STATUS_CODE, -- STATUS_CODE
RESALE_TRANSFER_TYPE, -- RESALE_TRANSFER_TYPE
PRODUCT_TRANSFER_MOVEMENT_TYPE, -- PRODUCT_TRANSFER_MOVEMENT_TYPE
PRODUCT_TRANSFER_DATE, -- PRODUCT_TRANSFER_DATE
TRACING_FLAG, -- TRACING_FLAG
SHIP_FROM_CUST_ACCOUNT_ID, -- SHIP_FROM_CUST_ACCOUNT_ID
SHIP_FROM_SITE_ID, -- SHIP_FROM_SITE_ID
SHIP_FROM_PARTY_NAME, -- SHIP_FROM_PARTY_NAME
SHIP_FROM_LOCATION, -- SHIP_FROM_LOCATION
SHIP_FROM_ADDRESS, -- SHIP_FROM_ADDRESS
SHIP_FROM_CITY, -- SHIP_FROM_CITY
SHIP_FROM_STATE, -- SHIP_FROM_STATE
SHIP_FROM_POSTAL_CODE, -- SHIP_FROM_POSTAL_CODE
SHIP_FROM_COUNTRY, -- SHIP_FROM_COUNTRY
SHIP_FROM_CONTACT_PARTY_ID, -- SHIP_FROM_CONTACT_PARTY_ID
SHIP_FROM_CONTACT_NAME, -- SHIP_FROM_CONTACT_NAME
SHIP_FROM_EMAIL, -- SHIP_FROM_EMAIL
SHIP_FROM_FAX, -- SHIP_FROM_FAX
SHIP_FROM_PHONE, -- SHIP_FROM_PHONE
SOLD_FROM_CUST_ACCOUNT_ID, -- SOLD_FROM_CUST_ACCOUNT_ID
SOLD_FROM_SITE_ID, -- SOLD_FROM_SITE_ID
SOLD_FROM_PARTY_NAME, -- SOLD_FROM_PARTY_NAME
SOLD_FROM_LOCATION, -- SOLD_FROM_LOCATION
SOLD_FROM_ADDRESS, -- SOLD_FROM_ADDRESS
SOLD_FROM_CITY, -- SOLD_FROM_CITY
SOLD_FROM_STATE, -- SOLD_FROM_STATE
SOLD_FROM_POSTAL_CODE, -- SOLD_FROM_POSTAL_CODE
SOLD_FROM_COUNTRY, -- SOLD_FROM_COUNTRY
SOLD_FROM_CONTACT_PARTY_ID, -- SOLD_FROM_CONTACT_PARTY_ID
SOLD_FROM_CONTACT_NAME, -- SOLD_FROM_CONTACT_NAME
SOLD_FROM_EMAIL, -- SOLD_FROM_EMAIL
SOLD_FROM_PHONE, -- SOLD_FROM_PHONE
SOLD_FROM_FAX, -- SOLD_FROM_FAX
BILL_TO_CUST_ACCOUNT_ID, -- BILL_TO_CUST_ACCOUNT_ID
BILL_TO_SITE_USE_ID, -- BILL_TO_SITE_USE_ID
BILL_TO_PARTY_ID, -- BILL_TO_PARTY_ID
BILL_TO_PARTY_SITE_ID, -- BILL_TO_PARTY_SITE_ID
BILL_TO_PARTY_NAME, -- BILL_TO_PARTY_NAME
BILL_TO_DUNS_NUMBER, -- BILL_TO_DUNS_NUMBER
BILL_TO_LOCATION, -- BILL_TO_LOCATION
BILL_TO_ADDRESS, -- BILL_TO_ADDRESS
BILL_TO_CITY, -- BILL_TO_CITY
BILL_TO_STATE, -- BILL_TO_STATE
BILL_TO_POSTAL_CODE, -- BILL_TO_POSTAL_CODE
BILL_TO_COUNTRY, -- BILL_TO_COUNTRY
BILL_TO_CONTACT_PARTY_ID, -- BILL_TO_CONTACT_PARTY_ID
BILL_TO_CONTACT_NAME, -- BILL_TO_CONTACT_NAME
BILL_TO_EMAIL, -- BILL_TO_EMAIL
BILL_TO_PHONE, -- BILL_TO_PHONE
BILL_TO_FAX, -- BILL_TO_FAX
SHIP_TO_CUST_ACCOUNT_ID, -- SHIP_TO_CUST_ACCOUNT_ID
SHIP_TO_SITE_USE_ID, -- SHIP_TO_SITE_USE_ID
SHIP_TO_PARTY_ID, -- SHIP_TO_PARTY_ID
SHIP_TO_PARTY_SITE_ID, -- SHIP_TO_PARTY_SITE_ID
SHIP_TO_PARTY_NAME, -- SHIP_TO_PARTY_NAME
SHIP_TO_DUNS_NUMBER, -- SHIP_TO_DUNS_NUMBER
SHIP_TO_LOCATION, -- SHIP_TO_LOCATION
SHIP_TO_ADDRESS, -- SHIP_TO_ADDRESS
SHIP_TO_CITY, -- SHIP_TO_CITY
SHIP_TO_COUNTRY, -- SHIP_TO_COUNTRY
SHIP_TO_POSTAL_CODE, -- SHIP_TO_POSTAL_CODE
SHIP_TO_STATE, -- SHIP_TO_STATE
SHIP_TO_CONTACT_PARTY_ID, -- SHIP_TO_CONTACT_PARTY_ID
SHIP_TO_CONTACT_NAME, -- SHIP_TO_CONTACT_NAME
SHIP_TO_EMAIL, -- SHIP_TO_EMAIL
SHIP_TO_PHONE, -- SHIP_TO_PHONE
SHIP_TO_FAX, -- SHIP_TO_FAX
END_CUST_PARTY_ID, -- END_CUST_PARTY_ID
END_CUST_SITE_USE_ID, -- END_CUST_SITE_USE_ID
END_CUST_SITE_USE_CODE, -- END_CUST_SITE_USE_CODE
END_CUST_PARTY_SITE_ID, -- END_CUST_PARTY_SITE_ID
END_CUST_PARTY_NAME, -- END_CUST_PARTY_NAME
END_CUST_LOCATION, -- END_CUST_LOCATION
END_CUST_ADDRESS, -- END_CUST_ADDRESS
END_CUST_CITY, -- END_CUST_CITY
END_CUST_STATE, -- END_CUST_STATE
END_CUST_POSTAL_CODE, -- END_CUST_POSTAL_CODE
END_CUST_COUNTRY, -- END_CUST_COUNTRY
END_CUST_CONTACT_PARTY_ID, -- END_CUST_CONTACT_PARTY_ID
END_CUST_CONTACT_NAME, -- END_CUST_CONTACT_NAME
END_CUST_EMAIL, -- END_CUST_EMAIL
END_CUST_PHONE, -- END_CUST_PHONE
END_CUST_FAX, -- END_CUST_FAX
DIRECT_CUSTOMER_FLAG, -- DIRECT_CUSTOMER_FLAG
ORDER_TYPE_ID, -- ORDER_TYPE_ID
ORDER_TYPE, -- ORDER_TYPE
ORDER_CATEGORY, -- ORDER_CATEGORY
AGREEMENT_TYPE, -- AGREEMENT_TYPE
AGREEMENT_ID, -- AGREEMENT_ID
AGREEMENT_NAME, -- AGREEMENT_NAME
AGREEMENT_PRICE, -- AGREEMENT_PRICE
AGREEMENT_UOM_CODE, -- AGREEMENT_UOM_CODE
CORRECTED_AGREEMENT_ID, -- CORRECTED_AGREEMENT_ID
CORRECTED_AGREEMENT_NAME, -- CORRECTED_AGREEMENT_NAME
PRICE_LIST_ID, -- PRICE_LIST_ID
PRICE_LIST_NAME, -- PRICE_LIST_NAME
ORIG_SYSTEM_REFERENCE, -- ORIG_SYSTEM_REFERENCE
ORIG_SYSTEM_LINE_REFERENCE, -- ORIG_SYSTEM_LINE_REFERENCE
ORIG_SYSTEM_CURRENCY_CODE, -- ORIG_SYSTEM_CURRENCY_CODE
ORIG_SYSTEM_SELLING_PRICE, -- ORIG_SYSTEM_SELLING_PRICE
ORIG_SYSTEM_QUANTITY, -- ORIG_SYSTEM_QUANTITY
ORIG_SYSTEM_UOM, -- ORIG_SYSTEM_UOM
ORIG_SYSTEM_PURCHASE_UOM, -- ORIG_SYSTEM_PURCHASE_UOM
ORIG_SYSTEM_PURCHASE_CURR, -- ORIG_SYSTEM_PURCHASE_CURR
ORIG_SYSTEM_PURCHASE_PRICE, -- ORIG_SYSTEM_PURCHASE_PRICE
ORIG_SYSTEM_PURCHASE_QUANTITY, -- ORIG_SYSTEM_PURCHASE_QUANTITY
ORIG_SYSTEM_AGREEMENT_UOM, -- ORIG_SYSTEM_AGREEMENT_UOM
ORIG_SYSTEM_AGREEMENT_NAME, -- ORIG_SYSTEM_AGREEMENT_NAME
ORIG_SYSTEM_AGREEMENT_TYPE, -- ORIG_SYSTEM_AGREEMENT_TYPE
ORIG_SYSTEM_AGREEMENT_STATUS, -- ORIG_SYSTEM_AGREEMENT_STATUS
ORIG_SYSTEM_AGREEMENT_CURR, -- ORIG_SYSTEM_AGREEMENT_CURR
ORIG_SYSTEM_AGREEMENT_PRICE, -- ORIG_SYSTEM_AGREEMENT_PRICE
ORIG_SYSTEM_AGREEMENT_QUANTITY, -- ORIG_SYSTEM_AGREEMENT_QUANTITY
ORIG_SYSTEM_ITEM_NUMBER, -- ORIG_SYSTEM_ITEM_NUMBER
CURRENCY_CODE, -- CURRENCY_CODE
EXCHANGE_RATE, -- EXCHANGE_RATE
EXCHANGE_RATE_TYPE, -- EXCHANGE_RATE_TYPE
EXCHANGE_RATE_DATE, -- EXCHANGE_RATE_DATE
PO_NUMBER, -- PO_NUMBER
PO_RELEASE_NUMBER, -- PO_RELEASE_NUMBER
PO_TYPE, -- PO_TYPE
INVOICE_NUMBER, -- INVOICE_NUMBER
DATE_INVOICED, -- DATE_INVOICED
ORDER_NUMBER, -- ORDER_NUMBER
DATE_ORDERED, -- DATE_ORDERED
DATE_SHIPPED, -- DATE_SHIPPED
CLAIMED_AMOUNT, -- CLAIMED_AMOUNT
ALLOWED_AMOUNT, -- ALLOWED_AMOUNT
TOTAL_ALLOWED_AMOUNT, -- TOTAL_ALLOWED_AMOUNT
ACCEPTED_AMOUNT, -- ACCEPTED_AMOUNT
TOTAL_ACCEPTED_AMOUNT, -- TOTAL_ACCEPTED_AMOUNT
LINE_TOLERANCE_AMOUNT, -- LINE_TOLERANCE_AMOUNT
TOLERANCE_FLAG, -- TOLERANCE_FLAG
TOTAL_CLAIMED_AMOUNT, -- TOTAL_CLAIMED_AMOUNT
PURCHASE_PRICE, -- PURCHASE_PRICE
PURCHASE_UOM_CODE, -- PURCHASE_UOM_CODE
ACCTD_PURCHASE_PRICE, -- ACCTD_PURCHASE_PRICE
SELLING_PRICE, -- SELLING_PRICE
ACCTD_SELLING_PRICE, -- ACCTD_SELLING_PRICE
UOM_CODE, -- UOM_CODE
QUANTITY, -- QUANTITY
CALCULATED_PRICE, -- CALCULATED_PRICE
ACCTD_CALCULATED_PRICE, -- ACCTD_CALCULATED_PRICE
CALCULATED_AMOUNT, -- CALCULATED_AMOUNT
CREDIT_CODE, -- CREDIT_CODE
CREDIT_ADVICE_DATE, -- CREDIT_ADVICE_DATE
UPC_CODE, -- UPC_CODE
INVENTORY_ITEM_ID, -- INVENTORY_ITEM_ID
ITEM_NUMBER, -- ITEM_NUMBER
ITEM_DESCRIPTION, -- ITEM_DESCRIPTION
INVENTORY_ITEM_SEGMENT1, -- INVENTORY_ITEM_SEGMENT1
INVENTORY_ITEM_SEGMENT2, -- INVENTORY_ITEM_SEGMENT2
INVENTORY_ITEM_SEGMENT3, -- INVENTORY_ITEM_SEGMENT3
INVENTORY_ITEM_SEGMENT4, -- INVENTORY_ITEM_SEGMENT4
INVENTORY_ITEM_SEGMENT5, -- INVENTORY_ITEM_SEGMENT5
INVENTORY_ITEM_SEGMENT6, -- INVENTORY_ITEM_SEGMENT6
INVENTORY_ITEM_SEGMENT7, -- INVENTORY_ITEM_SEGMENT7
INVENTORY_ITEM_SEGMENT8, -- INVENTORY_ITEM_SEGMENT8
INVENTORY_ITEM_SEGMENT9, -- INVENTORY_ITEM_SEGMENT9
INVENTORY_ITEM_SEGMENT10, -- INVENTORY_ITEM_SEGMENT10
INVENTORY_ITEM_SEGMENT11, -- INVENTORY_ITEM_SEGMENT11
INVENTORY_ITEM_SEGMENT12, -- INVENTORY_ITEM_SEGMENT12
INVENTORY_ITEM_SEGMENT13, -- INVENTORY_ITEM_SEGMENT13
INVENTORY_ITEM_SEGMENT14, -- INVENTORY_ITEM_SEGMENT14
INVENTORY_ITEM_SEGMENT15, -- INVENTORY_ITEM_SEGMENT15
INVENTORY_ITEM_SEGMENT16, -- INVENTORY_ITEM_SEGMENT16
INVENTORY_ITEM_SEGMENT17, -- INVENTORY_ITEM_SEGMENT17
INVENTORY_ITEM_SEGMENT18, -- INVENTORY_ITEM_SEGMENT18
INVENTORY_ITEM_SEGMENT19, -- INVENTORY_ITEM_SEGMENT19
INVENTORY_ITEM_SEGMENT20, -- INVENTORY_ITEM_SEGMENT20
PRODUCT_CATEGORY_ID, -- PRODUCT_CATEGORY_ID
CATEGORY_NAME, -- CATEGORY_NAME
DUPLICATED_LINE_ID, -- DUPLICATED_LINE_ID
DUPLICATED_ADJUSTMENT_ID, -- DUPLICATED_ADJUSTMENT_ID
RESPONSE_TYPE, -- RESPONSE_TYPE
RESPONSE_CODE, -- RESPONSE_CODE
REJECT_REASON_CODE, -- REJECT_REASON_CODE
FOLLOWUP_ACTION_CODE, -- FOLLOWUP_ACTION_CODE
NET_ADJUSTED_AMOUNT, -- NET_ADJUSTED_AMOUNT
DISPUTE_CODE, -- DISPUTE_CODE
DATA_SOURCE_CODE, -- DATA_SOURCE_CODE
HEADER_ATTRIBUTE_CATEGORY, -- HEADER_ATTRIBUTE_CATEGORY
HEADER_ATTRIBUTE1, -- HEADER_ATTRIBUTE1
HEADER_ATTRIBUTE2, -- HEADER_ATTRIBUTE2
HEADER_ATTRIBUTE3, -- HEADER_ATTRIBUTE3
HEADER_ATTRIBUTE4, -- HEADER_ATTRIBUTE4
HEADER_ATTRIBUTE5, -- HEADER_ATTRIBUTE5
HEADER_ATTRIBUTE6, -- HEADER_ATTRIBUTE6
HEADER_ATTRIBUTE7, -- HEADER_ATTRIBUTE7
HEADER_ATTRIBUTE8, -- HEADER_ATTRIBUTE8
HEADER_ATTRIBUTE9, -- HEADER_ATTRIBUTE9
HEADER_ATTRIBUTE10, -- HEADER_ATTRIBUTE10
HEADER_ATTRIBUTE11, -- HEADER_ATTRIBUTE11
HEADER_ATTRIBUTE12, -- HEADER_ATTRIBUTE12
HEADER_ATTRIBUTE13, -- HEADER_ATTRIBUTE13
HEADER_ATTRIBUTE14, -- HEADER_ATTRIBUTE14
HEADER_ATTRIBUTE15, -- HEADER_ATTRIBUTE15
LINE_ATTRIBUTE_CATEGORY, -- LINE_ATTRIBUTE_CATEGORY
LINE_ATTRIBUTE1, -- LINE_ATTRIBUTE1
LINE_ATTRIBUTE2, -- LINE_ATTRIBUTE2
LINE_ATTRIBUTE3, -- LINE_ATTRIBUTE3
LINE_ATTRIBUTE4, -- LINE_ATTRIBUTE4
LINE_ATTRIBUTE5, -- LINE_ATTRIBUTE5
LINE_ATTRIBUTE6, -- LINE_ATTRIBUTE6
LINE_ATTRIBUTE7, -- LINE_ATTRIBUTE7
LINE_ATTRIBUTE8, -- LINE_ATTRIBUTE8
LINE_ATTRIBUTE9, -- LINE_ATTRIBUTE9
LINE_ATTRIBUTE10, -- LINE_ATTRIBUTE10
LINE_ATTRIBUTE11, -- LINE_ATTRIBUTE11
LINE_ATTRIBUTE12, -- LINE_ATTRIBUTE12
LINE_ATTRIBUTE13, -- LINE_ATTRIBUTE13
LINE_ATTRIBUTE14, -- LINE_ATTRIBUTE14
LINE_ATTRIBUTE15, -- LINE_ATTRIBUTE15
ORG_ID, -- ORG_ID
SECURITY_GROUP_ID, -- SECURITY_GROUP_ID
INVOICE_LINE_NUMBER, -- INVOICE_LINE_NUMBER
ORDER_LINE_NUMBER, -- ORDER_LINE_NUMBER
SUPPLIER_ITEM_COST, -- SUPPLIER_ITEM_COST
SUPPLIER_ITEM_UOM -- SUPPLIER_ITEM_UOM
FROM OZF_RESALE_LINES_INT_ALL
WHERE REQUEST_ID = G_CONC_REQUEST_ID
AND ORG_ID = l_org_id -- nepanda : Fix for bug # 15900683
);
DELETE FROM ozf_resale_batches_all
WHERE request_id = G_CONC_REQUEST_ID
AND org_id = l_org_id; -- nepanda : Fix for bug # 15900683
UPDATE ozf_resale_lines_int_all
SET request_id = null, resale_batch_id = null
WHERE REQUEST_ID = G_CONC_REQUEST_ID
AND ORG_ID = l_org_id ; -- nepanda : Fix for bug # 15900683
INSERT INTO (SELECT
RESALE_DRAFT_LOG_ID,
RESALE_ID,
DRAFT_RUN_ID,
RESALE_ID_TYPE,
ERROR_CODE,
ERROR_MESSAGE,
COLUMN_NAME,
COLUMN_VALUE,
ORG_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
FROM OZF_RESALE_DRAFT_LOGS_ALL)
VALUES l_resale_logs_tbl(i);
INSERT INTO OZF_RESALE_DRAFT_LOGS_ALL
( RESALE_DRAFT_LOG_ID,
RESALE_ID,
DRAFT_RUN_ID,
RESALE_ID_TYPE,
ERROR_CODE,
ERROR_MESSAGE,
COLUMN_NAME,
COLUMN_VALUE,
ORG_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
VALUES
( l_resale_logs_tbl(i).resale_draft_log_id,
l_resale_logs_tbl(i).resale_id,
l_resale_logs_tbl(i).draft_run_id,
l_resale_logs_tbl(i).resale_id_type,
l_resale_logs_tbl(i).error_code,
-- nepanda : Fix for bug # 15900683
-- Error message is coming as null from pricing in some error scenarios, will insert '' in case of error_message = null
NVL(l_resale_logs_tbl(i).error_message, ' '),
l_resale_logs_tbl(i).column_name,
l_resale_logs_tbl(i).column_value,
l_resale_logs_tbl(i).org_id,
l_resale_logs_tbl(i).creation_date,
l_resale_logs_tbl(i).created_by,
l_resale_logs_tbl(i).last_update_date,
l_resale_logs_tbl(i).last_updated_by,
l_resale_logs_tbl(i).last_update_login,
l_resale_logs_tbl(i).request_id,
l_resale_logs_tbl(i).program_application_id,
l_resale_logs_tbl(i).program_id,
l_resale_logs_tbl(i).program_update_date
);
SELECT ou.organization_id org_id
FROM hr_operating_units ou
WHERE mo_global.check_access(ou.organization_id) = 'Y';
l_budget_data := DBMS_XMLGEN.getXml('SELECT ''FUND'' AccrualType,
util.utilization_id UtilizationId,
util.fund_id FundId,
NVL(map.xref_line_id_value,oe.list_line_id) DiscountLineId,
util.product_id ProductId,
line.resale_header_id OrderId,
line.resale_line_id OrderLineId,
line.quantity ShippedQuantity,
(line.quantity*NVL(line.selling_price,line.purchase_price)) Revenue,
NVL(util.plan_curr_amount,0) AccrualAmount
FROM ozf_resale_lines_all line, ozf_funds_utilized_all_b util,
ozf_offers off, ozf_resale_adjustments_all oe,
ozf_xref_map map
WHERE line.resale_line_id = util.object_id
AND util.object_type = ''TP_ORDER''
AND util.request_id = ' || G_CONC_REQUEST_ID ||
' AND util.plan_type = ''OFFR''
AND util.plan_id = off.qp_list_header_id
AND util.price_adjustment_id = oe.resale_adjustment_id
AND util.gl_posted_flag IN (''Y'', ''X'')
AND oe.list_line_id = map.list_line_id(+)
AND NVL(off.budget_offer_yn, ''N'') = ''Y''', 0);
l_offer_data := DBMS_XMLGEN.getXml('SELECT ''OFFR'' AccrualType,
util.utilization_id UtilizationId,
util.plan_id PromotionId,
util.fund_id FundId,
NVL(map.xref_line_id_value,oe.list_line_id) DiscountLineId,
util.product_id ProductId,
line.resale_header_id OrderId,
line.resale_line_id OrderLineId,
util.cust_account_id CustomerId,
line.quantity ShippedQuantity,
(line.quantity*NVL(line.selling_price,line.purchase_price)) Revenue,
NVL(util.plan_curr_amount,0) AccrualAmount
FROM ozf_resale_lines_all line, ozf_funds_utilized_all_b util,
ozf_offers off, ozf_resale_adjustments_all oe,
ozf_xref_map map
WHERE line.resale_line_id = util.object_id
AND util.object_type = ''TP_ORDER''
AND util.request_id = ' || G_CONC_REQUEST_ID ||
' AND util.plan_type = ''OFFR''
AND util.plan_id = off.qp_list_header_id
AND util.price_adjustment_id = oe.resale_adjustment_id
AND util.gl_posted_flag = ''Y''
AND oe.list_line_id = map.list_line_id(+)
AND NVL(off.budget_offer_yn, ''N'') = ''N''', 0);