[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT status_code,
batch_type,
partner_cust_account_id,
partner_party_id,
report_start_date,
report_end_date,
batch_number,
last_updated_by,
NVL(direct_order_flag,'N'), --For Bug#9447673 SSD IDSM
org_id
FROM ozf_resale_batches_all
WHERE resale_batch_id = p_id;
l_last_updated_by NUMBER(15);
SELECT count(1)
From ozf_resale_lines_int
WHERE resale_batch_id = p_id
AND status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN;
SELECT resale_line_int_id
FROM ozf_resale_lines_int
WHERE resale_batch_id = p_id
AND order_number = p_order_number
AND sold_from_cust_account_id = p_cust_id
AND date_ordered = p_date
AND status_code = 'PROCESSED';
SELECT a.resale_header_id
FROM ozf_resale_headers a
, ozf_resale_lines_int b
, ozf_resale_lines c
WHERE b.resale_batch_id = p_id
AND b.order_number = p_order_number
AND b.sold_from_cust_account_id = p_cust_id
AND b.date_ordered = p_date
AND b.status_code IN ('DUPLICATED', 'PROCESSED')
AND b.duplicated_line_id = c.resale_line_id
AND c.resale_header_id = a.resale_header_id;
SELECT DISTINCT order_number,
sold_from_cust_account_id,
date_ordered
FROM ozf_resale_lines_int
WHERE resale_batch_id = p_id
AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED)
--AND duplicated_adjustment_id <> -1
ORDER BY date_ordered;
SELECT end_cust_relation_flag
-- BUG 4992408 (+)
-- FROM ozf_sys_parameters_all;
SELECT DISTINCT bill_to_party_name
, bill_to_address
, bill_to_city
, bill_to_state
, bill_to_postal_code
, bill_to_country
FROM ozf_resale_lines_int_all
WHERE resale_batch_id = cv_resale_batch_id
AND status_code = 'PROCESSED'
AND ( duplicated_line_id IS NULL
OR ( duplicated_line_id IS NOT NULL AND duplicated_adjustment_id = -1))
AND bill_to_party_id IS NULL
AND bill_to_cust_account_id IS NULL
AND bill_to_party_name IS NOT NULL;
SELECT DISTINCT end_cust_party_name
, end_cust_address
, end_cust_city
, end_cust_state
, end_cust_postal_code
, end_cust_country
, end_cust_site_use_code
FROM ozf_resale_lines_int_all
WHERE resale_batch_id = cv_resale_batch_id
AND status_code = 'PROCESSED'
AND ( duplicated_line_id IS NULL
OR ( duplicated_line_id IS NOT NULL AND duplicated_adjustment_id = -1))
AND end_cust_party_id IS NULL
AND end_cust_party_name IS NOT NULL;
l_last_updated_by,
l_direct_order_flag,
l_org_id;
UPDATE ozf_resale_lines_int_all
SET bill_to_party_id = l_new_party_rec.party_id
, bill_to_party_site_id = l_new_party_rec.party_site_id
WHERE resale_batch_id = p_resale_batch_id
AND bill_to_party_id IS NULL
AND bill_to_cust_account_id IS NULL
AND bill_to_party_name = l_orig_billto_cust_tbl(i).bill_to_party_name
AND bill_to_address = l_orig_billto_cust_tbl(i).bill_to_address
AND bill_to_city = l_orig_billto_cust_tbl(i).bill_to_city
AND bill_to_state = l_orig_billto_cust_tbl(i).bill_to_state
AND bill_to_postal_code = l_orig_billto_cust_tbl(i).bill_to_postal_code
AND bill_to_country = l_orig_billto_cust_tbl(i).bill_to_country;
UPDATE ozf_resale_lines_int_all
SET end_cust_party_id = l_new_party_rec.party_id
, end_cust_party_site_id = l_new_party_rec.party_site_id
WHERE resale_batch_id = p_resale_batch_id
AND end_cust_party_id IS NULL
AND end_cust_party_name = l_orig_end_cust_tbl(i).end_cust_party_name
AND end_cust_address = l_orig_end_cust_tbl(i).end_cust_address
AND end_cust_city = l_orig_end_cust_tbl(i).end_cust_city
AND end_cust_state = l_orig_end_cust_tbl(i).end_cust_state
AND end_cust_postal_code = l_orig_end_cust_tbl(i).end_cust_postal_code
AND end_cust_country = l_orig_end_cust_tbl(i).end_cust_country
AND end_cust_site_use_code = l_orig_end_cust_tbl(i).end_cust_site_use_code;
l_order_num_tbl.DELETE;
l_dup_header_id_tbl.DELETE;
UPDATE ozf_resale_lines_int_all
SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
dispute_code = 'OZF_RESALE_MULTI_HEADER',
followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
response_type = 'CA',
response_code = 'N'
WHERE resale_batch_id = p_resale_batch_id
AND order_number = l_order_num_tbl(k).order_number
AND sold_from_cust_account_id = l_order_num_tbl(k).sold_from_cust_account_id
AND date_ordered = l_order_num_tbl(k).date_ordered
AND status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED);
l_valid_line_id_tbl.DELETE;
UPDATE ozf_resale_lines_int
SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
dispute_code = 'OZF_LT_INVT',
followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
response_type = 'CA',
response_code = 'N'
WHERE resale_line_int_id = l_line_int_rec.resale_line_int_id;
UPDATE ozf_resale_batches
SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
WHERE resale_batch_id = l_line_int_rec.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_line_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
);
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_line_int_rec
,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 inventory flow for Direct order only
*/
IF l_inventory_tracking = 'T'
AND (l_direct_order_flag <> 'Y') 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
);
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
);
UPDATE ozf_resale_lines_int_all
SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_CLOSED
WHERE resale_batch_id = p_resale_batch_id
--Bug# 8414563 fixed by ateotia(+)
--AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED);
UPDATE ozf_resale_batches_all
SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED
WHERE resale_batch_id = p_resale_batch_id;
SELECT COUNT(1)
FROM ozf_resale_lines_int
WHERE resale_batch_id = p_id
AND tracing_flag = 'F';
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_NON_TRC',
FND_MESSAGE.get_string('OZF','OZF_RESALE_NON_TRC'),
'TRACING_FLAG',
tracing_flag,
org_id
FROM ozf_resale_lines_int_all b
WHERE b.status_code = 'OPEN'
AND b.tracing_flag = 'F'
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_NON_TRC'
);
UPDATE ozf_resale_lines_int_all
SET status_code = 'DISPUTED',
dispute_code = 'OZF_RESALE_NON_TRC',
followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
response_type = 'CA',
response_code = 'N'
WHERE resale_batch_id = p_resale_batch_id
AND tracing_flag = 'F';
SELECT DISTINCT order_number,
sold_from_cust_account_id,
date_ordered
FROM ozf_resale_lines_int
WHERE status_code = 'OPEN'
-- status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED)
AND duplicated_adjustment_id IS NULL
AND resale_batch_id = p_resale_batch_id
ORDER BY date_ordered;
SELECT *
FROM ozf_resale_lines_int
WHERE order_NUMBER = p_order_NUMBER
AND date_ordered = p_date
AND sold_from_cust_account_id = p_cust_account_id
AND status_code = 'OPEN'
--AND status_code in(OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED)
AND duplicated_adjustment_id is NULL
AND resale_batch_id = p_resale_batch_id;
SELECT COUNT(1)
FROM ozf_resale_lines_int
WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED
AND dispute_code = OZF_RESALE_COMMON_PVT.G_INVALD_DISPUTE_CODE
AND resale_batch_id = p_id;
SELECT partner_party_id,
report_start_date,
report_end_date,
NVL(direct_order_flag,'N') -- For Bug#9447673 SSD IDSM
FROM ozf_resale_batches
WHERE resale_batch_id = p_id;
OZF_RESALE_COMMON_PVT.Delete_Log(
p_api_version => 1.0
,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
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
l_order_num_tbl.DELETE;
l_cust_account_id_tbl.DELETE;
l_order_date_tbl.DELETE;
l_order_set_tbl.DELETE;
DELETE FROM ozf_resale_logs
WHERE resale_id = l_order_set_tbl(J).resale_line_int_id
AND resale_id_type = OZF_RESALE_COMMON_PVT.G_ID_TYPE_IFACE;
UPDATE ozf_resale_lines_int
SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED,
dispute_code = 'OZF_LT_INVT',
followup_action_code = NVL2(followup_action_code,followup_action_code,'C'),
response_type = 'CA',
response_code = 'N'
WHERE resale_line_int_id = l_order_set_tbl(J).resale_line_int_id;
UPDATE ozf_resale_batches
SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
WHERE resale_batch_id = p_resale_batch_id;
UPDATE ozf_resale_lines_int
SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
WHERE resale_line_int_id = l_order_set_tbl(J).resale_line_int_id;
UPDATE ozf_resale_lines_int
SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
WHERE resale_line_int_Id = l_order_set_tbl(j).resale_line_int_id;
UPDATE ozf_resale_batches_all
SET status_code = l_status_code,
--Bug# 8414563 fixed by ateotia(+)
--lines_disputed = l_lines_disputed,
lines_disputed = l_lines_disputed + l_lines_duplicated,
--Bug# 8414563 fixed by ateotia(-)
lines_invalid = l_lines_invalid
WHERE resale_batch_id = p_resale_batch_id;