[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Resale_Log (
p_id_value IN VARCHAR2,
p_id_type IN VARCHAR2,
p_error_code IN VARCHAR2,
p_error_message IN VARCHAR2 := NULL,
p_column_name IN VARCHAR2,
p_column_value IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2 )
IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Resale_Log';
OZF_RESALE_LOGS_PKG.Insert_Row(
px_resale_log_id => l_log_id,
p_resale_id => p_id_value,
p_resale_id_type => p_id_type,
p_error_code => p_error_code,
p_error_message => nvl(p_error_message, fnd_message.get_string('OZF',p_error_code)),
p_column_name => p_column_name,
p_column_value => p_column_value,
--px_org_id => OZF_RESALE_COMMON_PVT.g_org_id
px_org_id => l_org_id
);
END Insert_Resale_Log;
PROCEDURE Bulk_Insert_Resale_Log (
p_id_value IN number_tbl_type,
p_id_type IN VARCHAR2,
p_error_code IN varchar_tbl_type,
p_column_name IN varchar_tbl_type,
p_column_value IN long_varchar_tbl_type,
p_batch_id IN NUMBER, -- bug # 5997978 fixed
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name varchar2(30) := 'Bulk_Insert_Resale_Log';
INSERT INTO ozf_resale_logs_all (
RESALE_LOG_ID,
RESALE_ID,
RESALE_ID_TYPE,
ERROR_CODE,
ERROR_MESSAGE,
COLUMN_NAME,
COLUMN_VALUE,
ORG_ID
) VALUES (
ozf_resale_logs_all_s.nextval,
p_id_value(i),
p_id_type,
p_error_code(i),
FND_MESSAGE.get_string('OZF',p_error_code(i)),
p_column_name(i),
p_column_value(i),
-- Start: bug # 5997978 fixed
-- NVL(SUBSTRB(USERENV('CLIENT_INFO'),1,10),-99)
l_org_id
-- End: bug # 5997978 fixed
);
END Bulk_Insert_Resale_Log;
SELECT report_start_date, report_end_date
FROM ozf_resale_batches
WHERE resale_batch_id = p_id;
SELECT resale_line_int_id, 'OZF_RESALE_ORD_NUM_MISS', 'ORDER_NUMBER', NULL
FROM ozf_resale_lines_int_all
WHERE resale_batch_id = p_batch_id
AND status_code IN (G_BATCH_ADJ_OPEN, G_BATCH_ADJ_DISPUTED)
AND order_number IS NULL
UNION ALL
SELECT resale_line_int_id, 'OZF_RESALE_ORD_DATE_MISS', 'DATE_ORDERED', NULL
FROM ozf_resale_lines_int_all
WHERE resale_batch_id = p_batch_id
AND status_code IN (G_BATCH_ADJ_OPEN, G_BATCH_ADJ_DISPUTED)
AND date_ordered IS NULL
UNION ALL
SELECT resale_line_int_id, 'OZF_ORD_DATE_LT_START', 'DATE_ORDERED', TO_CHAR(date_ordered)
FROM ozf_resale_lines_int_all
WHERE resale_batch_id = p_batch_id
AND status_code IN (G_BATCH_ADJ_OPEN, G_BATCH_ADJ_DISPUTED)
AND date_ordered IS NOT NULL
AND date_ordered < p_start_date
UNION ALL
SELECT resale_line_int_id, 'OZF_ORD_DATE_GT_END', 'DATE_ORDERED', TO_CHAR(date_ordered)
FROM ozf_resale_lines_int_all
WHERE resale_batch_id = p_batch_id
AND status_code IN (G_BATCH_ADJ_OPEN, G_BATCH_ADJ_DISPUTED)
AND date_ordered IS NOT NULL
AND date_ordered > p_end_date
UNION ALL
SELECT resale_line_int_id, 'OZF_RESALE_PRODUCT_ID_MISS', 'INVENTORY_ITEM_ID', NULL
FROM ozf_resale_lines_int_all
WHERE resale_batch_id = p_batch_id
AND status_code IN (G_BATCH_ADJ_OPEN, G_BATCH_ADJ_DISPUTED)
AND inventory_item_id IS NULL
UNION ALL
SELECT resale_line_int_id, 'OZF_RESALE_UOM_MISS', 'UOM_CODE', NULL
FROM ozf_resale_lines_int_all
WHERE resale_batch_id = p_batch_id
AND status_code IN (G_BATCH_ADJ_OPEN, G_BATCH_ADJ_DISPUTED)
AND uom_code IS NULL
UNION ALL
SELECT resale_line_int_id, 'OZF_RESALE_SOLD_FROM_MISS', 'SOLD_FROM_CUST_ACCOUNT_ID', NULL
FROM ozf_resale_lines_int_all
WHERE resale_batch_id = p_batch_id
AND status_code IN (G_BATCH_ADJ_OPEN, G_BATCH_ADJ_DISPUTED)
AND sold_from_cust_account_id IS NULL
UNION ALL
SELECT resale_line_int_id, 'OZF_RESALE_SHIP_FROM_MISS', 'SHIP_FROM_CUST_ACCOUNT_ID', NULL
FROM ozf_resale_lines_int_all
WHERE resale_batch_id = p_batch_id
AND status_code IN (G_BATCH_ADJ_OPEN, G_BATCH_ADJ_DISPUTED)
AND ship_from_cust_account_id IS NULL
UNION ALL
SELECT resale_line_int_id, 'OZF_RESALE_PRICE_LIST_NULL', 'AGREEMENT_ID', NULL
FROM ozf_resale_lines_int_all
WHERE resale_batch_id = p_batch_id
AND status_code IN (G_BATCH_ADJ_OPEN, G_BATCH_ADJ_DISPUTED)
AND agreement_type = 'PL'
AND agreement_id IS NULL
UNION ALL
SELECT resale_line_int_id, 'OZF_RESALE_AGREE_NUM_NULL', 'AGREEMENT_ID', NULL
FROM ozf_resale_lines_int_all
WHERE resale_batch_id = p_batch_id
AND status_code IN (G_BATCH_ADJ_OPEN, G_BATCH_ADJ_DISPUTED)
AND agreement_type = 'SPO'
AND agreement_id IS NULL;
Bulk_Insert_Resale_Log (
p_id_value => l_id_tbl,
p_id_type => G_ID_TYPE_IFACE,
p_error_code => l_err_tbl,
p_column_name => l_col_tbl,
p_column_value => l_val_tbl,
p_batch_id => p_batch_id, --bug # 5997978 fixed
x_return_status => l_return_status
);
SELECT orsl.resale_line_int_id
, 'OZF_CLAIM_CUST_NOT_IN_DB'
, 'SOLD_FROM_CUST_ACCOUNT_ID'
, to_char(orsl.sold_from_cust_account_id)
FROM ozf_resale_lines_int_all orsl
WHERE orsl.status_code = 'OPEN'
AND orsl.direct_customer_flag = 'T'
AND orsl.resale_batch_id = p_resale_batch_id
AND orsl.sold_from_cust_account_id IS NOT NULL
AND NOT EXISTS ( SELECT 1
FROM hz_cust_accounts hca
WHERE hca.cust_account_id = orsl.sold_from_cust_account_id)
UNION ALL
SELECT orsl.resale_line_int_id
, 'OZF_CLAIM_CUST_NOT_IN_DB'
, 'SHIP_FROM_CUST_ACCOUNT_ID'
, to_char(orsl.ship_from_cust_account_id)
FROM ozf_resale_lines_int_all orsl
WHERE orsl.status_code = 'OPEN'
AND orsl.direct_customer_flag = 'T'
AND orsl.resale_batch_id = p_resale_batch_id
AND orsl.ship_from_cust_account_id IS NOT NULL
AND NOT EXISTS ( SELECT 1
FROM hz_cust_accounts hca
WHERE hca.cust_account_id = orsl.ship_from_cust_account_id)
UNION ALL
SELECT orsl.resale_line_int_id
, 'OZF_RESALE_UOM_NOT_IN_DB'
, 'UOM_CODE'
, orsl.uom_code
FROM ozf_resale_lines_int_all orsl
WHERE orsl.direct_customer_flag ='T'
AND orsl.status_code = 'OPEN'
AND orsl.resale_batch_id = p_resale_batch_id
AND orsl.uom_code IS NOT NULL
AND NOT EXISTS ( SELECT 1
FROM mtl_units_of_measure mum
WHERE mum.uom_code = orsl.uom_code )
UNION ALL
SELECT orsl.resale_line_int_id
, 'OZF_RESALE_ORDTYPE_NOT_IN_DB'
, 'ORDER_TYPE_ID'
, to_char(orsl.order_type_id)
FROM ozf_resale_lines_int_all orsl
WHERE orsl.status_code = 'OPEN'
AND orsl.direct_customer_flag = 'T'
AND orsl.order_type_id IS NOT NULL
AND orsl.resale_batch_id = p_resale_batch_id
AND NOT EXISTS ( SELECT 1
FROM oe_transaction_types_all ottv
WHERE ottv.transaction_type_id = orsl.order_type_id)
UNION ALL
/*
SELECT orsl.resale_line_int_id
, 'OZF_RESALE_PRICE_NOT_IN_DB'
, 'AGREEMENT_ID'
, to_char(orsl.agreement_id)
FROM ozf_resale_lines_int_all orsl
WHERE orsl.status_code = 'OPEN'
AND orsl.direct_customer_flag = 'T'
AND orsl.resale_batch_id = p_resale_batch_id
AND orsl.agreement_id IS NOT NULL
AND NOT EXISTS ( SELECT 1
FROM qp_list_headers_b qlhv
WHERE qlhv.list_header_id = orsl.agreement_id
AND qlhv.list_type_code = 'PRL')
UNION ALL
*/
SELECT orsl.resale_line_int_id
, 'OZF_RESALE_PRODUCT_NOT_IN_DB'
, 'INVENTORY_ITEM_ID'
, to_char(orsl.inventory_item_id)
FROM ozf_resale_lines_int_all orsl
WHERE orsl.status_code = 'OPEN'
AND orsl.direct_customer_flag = 'T'
AND orsl.resale_batch_id = p_resale_batch_id
AND orsl.inventory_item_id IS NOT NULL
AND NOT EXISTS ( SELECT 1
FROM mtl_system_items_b msi
WHERE msi.inventory_item_id = orsl.inventory_item_id
AND msi.organization_id = G_ITEM_ORG_ID);
Bulk_Insert_Resale_Log (
p_id_value => l_id_tbl,
p_id_type => G_ID_TYPE_IFACE,
p_error_code => l_err_tbl,
p_column_name => l_col_tbl,
p_column_value => l_val_tbl,
p_batch_id => p_batch_id, --bug # 5997978 fixed
x_return_status => l_return_status
);
UPDATE ozf_resale_lines_int_all orli
SET orli.dispute_code = (SELECT orl.error_code
FROM ozf_resale_logs_all orl
WHERE orl.resale_id = orli.resale_line_int_id
AND resale_id_type = 'IFACE'
AND rownum = 1)
, orli.status_code = G_BATCH_ADJ_DISPUTED
, followup_action_code = 'C'
, response_type = 'CA'
, response_code = 'N'
WHERE orli.resale_batch_id = p_batch_id
AND orli.status_code = p_line_status
AND EXISTS( SELECT 1
FROM ozf_resale_logs_all c
WHERE c.resale_id = orli.resale_line_int_id
AND c.resale_id_type = 'IFACE');
PROCEDURE Update_Batch_Calculations (
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_resale_batch_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Batch_Calculations';
SELECT count(1)
FROM ozf_resale_lines_int
WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
AND tolerance_flag = 'T'
AND resale_batch_id = p_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 header_tolerance_operand, header_tolerance_calc_code
FROM ozf_resale_batches
WHERE resale_batch_id = p_id;
SELECT NVL(COUNT(resale_line_int_id), 0)
, NVL(SUM(total_claimed_amount), 0)
FROM ozf_resale_lines_int_all
WHERE resale_batch_id = p_resale_batch_id
AND status_code = 'DUPLICATED';
SAVEPOINT Update_Batch_Calculations;
Insert_Resale_Log (
p_id_value => p_resale_batch_id,
p_id_type => 'BATCH',
p_error_code => 'OZF_BATCH_AMT_OUT_TOLERANCE',
p_column_name => 'ALLOWED_AMOUNT',
p_column_value => l_total_allowed_amount,
x_return_status => l_return_status
);
UPDATE ozf_resale_batches_all
SET status_code = l_status_code,
allowed_amount =l_total_allowed_amount,
accepted_amount = l_total_accepted_amount,
disputed_amount = ABS(l_total_disputed_amount) + ABS(l_total_duplicated_amount),
lines_w_tolerance = l_lines_w_tolerance,
lines_disputed = l_lines_disputed + l_lines_duplicated,
lines_invalid = l_lines_invalid
WHERE resale_batch_id = p_resale_batch_id;
ROLLBACK TO Update_Batch_Calculations;
ROLLBACK TO Update_Batch_Calculations;
ROLLBACK TO Update_Batch_Calculations;
END Update_Batch_Calculations;
PROCEDURE Update_Line_Calculations(
p_resale_line_int_rec IN OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE,
p_unit_price IN NUMBER,
p_line_quantity IN NUMBER,
p_allowed_amount IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Line_Calculations';
SELECT line_tolerance_operand, line_tolerance_calc_code
FROM ozf_resale_batches
WHERE resale_batch_id = p_id;
SELECT ship_debit_calc_type
from ozf_sys_parameters;
SELECT batch_type
FROM ozf_resale_batches_all
WHERE resale_batch_id = p_batch_id;
SAVEPOINT Update_Line_Calculations;
Insert_Resale_Log (
p_id_value => p_resale_line_int_rec.resale_line_int_id,
p_id_type => 'IFACE',
p_error_code => 'OZF_IFACE_AMT_OUT_TOLERANCE',
p_column_name => 'LINE_TOLERANCE_AMOUNT',
p_column_value => l_line_tolerance_amount,
x_return_status => l_return_status
);
UPDATE ozf_resale_lines_int_all
SET accepted_amount = l_accepted_amount,
-- total_accepted_amount = l_accepted_amount * ABS(p_line_quantity),
-- bug 5969118 Ship and Debit return order generates positive claim amount
total_accepted_amount = l_total_accepted_amount,
-- bug 5969118 end
allowed_amount = p_allowed_amount,
-- total_allowed_amount = p_allowed_amount * ABS(p_line_quantity),
-- bug 5969118 Ship and Debit return order generates positive claim amount
total_allowed_amount = l_total_allowed_amount,
-- bug 5969118 end
net_adjusted_amount = l_net_adjusted_amount,
calculated_price = p_unit_price,
acctd_calculated_price = p_resale_line_int_rec.acctd_calculated_price,
calculated_amount = p_unit_price * p_line_quantity,
acctd_selling_price = p_resale_line_int_rec.acctd_selling_price,
exchange_rate = p_resale_line_int_rec.exchange_rate,
exchange_rate_date = p_resale_line_int_rec.exchange_rate_date,
exchange_rate_type = p_resale_line_int_rec.exchange_rate_type,
status_code = l_status_code,
dispute_code = l_dispute_code,
line_tolerance_amount = l_line_tolerance_amount,
tolerance_flag = l_tolerance_flag,
followup_action_code = l_followup_action_code,
response_type = l_response_type,
response_code = decode(l_status_code, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED, 'N',
OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED, 'Y')
WHERE resale_line_int_id = p_resale_line_int_rec.resale_line_int_id;
ROLLBACK TO Update_Line_Calculations;
ROLLBACK TO Update_Line_Calculations;
ROLLBACK TO Update_Line_Calculations;
END Update_Line_Calculations;
SELECT orl.resale_line_id
-- Bug 4670154 (+)
FROM ozf_resale_lines_all orl,
ozf_resale_lines_int_all orli
/*
FROM ozf_resale_lines orl,
ozf_resale_lines_int orli
*/
-- Bug 4670154 (-)
WHERE orl.order_number = orli.order_number
AND orl.date_ordered = orli.date_ordered
-- 6704619 (+)
AND (orl.date_shipped = orli.date_shipped
OR (orl.date_shipped IS NULL AND orli.date_shipped IS NULL))
-- 6704619 (-)
--AND orl.invoice_number = orli.invoice_number
--AND orl.date_invoiced = orli.date_invoiced
AND orl.inventory_item_id = orli.inventory_item_id
AND orl.quantity = orli.quantity
AND orl.uom_code = orli.uom_code
AND NVL(orl.sold_from_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.sold_from_cust_account_id,FND_API.G_MISS_NUM)
--AND orl.ship_from_cust_account_id = orli.sold_from_cust_account_id
AND NVL(orl.ship_from_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.ship_from_cust_account_id,FND_API.G_MISS_NUM)
AND orl.direct_customer_flag = 'T'
AND NVL(orl.bill_to_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.bill_to_cust_account_id,FND_API.G_MISS_NUM)
AND orli.resale_line_int_id = p_resale_line_int_id
AND rownum = 1;
SELECT orl.resale_line_id
-- Bug 4670154 (+)
FROM ozf_resale_lines_all orl,
ozf_resale_lines_int_all orli
/*
FROM ozf_resale_lines orl,
ozf_resale_lines_int orli
*/
-- Bug 4670154 (-)
WHERE orl.order_number = orli.order_number
AND nvl (orl.order_line_number, 0) = nvl (orli.order_line_number, 0) -- For Bug#9447673 SSD IDSM ER
AND orl.date_ordered = orli.date_ordered
-- 6704619 (+)
AND (orl.date_shipped = orli.date_shipped
OR (orl.date_shipped IS NULL AND orli.date_shipped IS NULL))
-- 6704619 (-)
--AND orl.invoice_number = orli.invoice_number
--AND orl.date_invoiced = orli.date_invoiced
AND orl.inventory_item_id = orli.inventory_item_id
AND orl.quantity = orli.quantity
AND orl.uom_code = orli.uom_code
AND NVL(orl.sold_from_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.sold_from_cust_account_id,FND_API.G_MISS_NUM)
--AND orl.ship_from_cust_account_id = orli.sold_from_cust_account_id
AND NVL(orl.ship_from_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.ship_from_cust_account_id,FND_API.G_MISS_NUM)
AND orl.direct_customer_flag = 'F'
AND NVL(orl.bill_to_party_name,FND_API.G_MISS_NUM) = NVL(orli.bill_to_party_name,FND_API.G_MISS_NUM)
AND orli.resale_line_int_id = p_resale_line_int_id
AND rownum = 1;
SELECT orsa.resale_adjustment_id
, orsa.claimed_amount
-- Bug 4670154 (+)
FROM ozf_resale_adjustments_all orsa
, ozf_resale_batches_all orsb
/*
FROM ozf_resale_adjustments orsa
, ozf_resale_batches orsb
*/
-- Bug 4670154 (-)
WHERE orsa.resale_line_id = p_line_id
AND orsa.resale_batch_id = orsb.resale_batch_id
AND orsb.batch_type = p_batch_type
-- Bug 4670154 (+)
AND orsa.list_header_id IS NULL
AND orsa.list_line_id IS NULL;
SELECT orlo.resale_line_int_id
-- Bug 4670154 (+)
FROM ozf_resale_lines_int_all orlo,
ozf_resale_lines_int_all orli
/*
FROM ozf_resale_lines_int orlo,
ozf_resale_lines_int orli
*/
-- Bug 4670154 (-)
WHERE orlo.order_number = orli.order_number
AND nvl (orlo.order_line_number, 0) = nvl (orli.order_line_number, 0) -- For Bug#9447673 SSD IDSM ER
AND orlo.date_ordered = orli.date_ordered
-- 6704619 (+)
AND (orlo.date_shipped = orli.date_shipped
OR (orlo.date_shipped IS NULL AND orli.date_shipped IS NULL))
-- 6704619 (-)
AND orlo.inventory_item_id = orli.inventory_item_id
AND orlo.quantity = orli.quantity
AND orlo.uom_code = orli.uom_code
AND NVL(orlo.sold_from_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.sold_from_cust_account_id,FND_API.G_MISS_NUM)
--AND orlo.ship_from_cust_account_id = orli.sold_from_cust_account_id
AND NVL(orlo.ship_from_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.ship_from_cust_account_id,FND_API.G_MISS_NUM)
AND orlo.claimed_amount = orli.claimed_amount
AND NVL(orlo.bill_to_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.bill_to_cust_account_id,FND_API.G_MISS_NUM)
AND orlo.status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED)
AND orlo.resale_line_int_id <> p_resale_line_int_id
AND orlo.resale_batch_id <> orli.resale_batch_id
AND orli.resale_line_int_id = p_resale_line_int_id
AND orlo.duplicated_line_id IS NULL
AND orlo.duplicated_adjustment_id IS NULL
AND orlo.direct_customer_flag = 'T'
AND orlo.creation_date <= orli.creation_date
AND rownum = 1;
SELECT orlo.resale_line_int_id
-- Bug 4670154 (+)
FROM ozf_resale_lines_int_all orlo,
ozf_resale_lines_int_all orli
/*
FROM ozf_resale_lines_int orlo,
ozf_resale_lines_int orli
*/
-- Bug 4670154 (-)
WHERE orlo.order_number = orli.order_number
AND orlo.date_ordered = orli.date_ordered
-- 6704619 (+)
AND (orlo.date_shipped = orli.date_shipped
OR (orlo.date_shipped IS NULL AND orli.date_shipped IS NULL))
-- 6704619 (-)
--AND orl.invoice_number = orli.invoice_number
--AND orl.date_invoiced = orli.date_invoiced
AND orlo.inventory_item_id = orli.inventory_item_id
AND orlo.quantity = orli.quantity
AND orlo.uom_code = orli.uom_code
AND NVL(orlo.sold_from_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.sold_from_cust_account_id,FND_API.G_MISS_NUM)
--AND orlo.ship_from_cust_account_id = orli.sold_from_cust_account_id
AND NVL(orlo.ship_from_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.ship_from_cust_account_id,FND_API.G_MISS_NUM)
AND orlo.claimed_amount = orli.claimed_amount
AND NVL(orlo.bill_to_party_name,FND_API.G_MISS_NUM) = NVL(orli.bill_to_party_name,FND_API.G_MISS_NUM)
AND orlo.status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED)
AND orlo.resale_line_int_id <> p_resale_line_int_id
AND orlo.resale_batch_id <> orli.resale_batch_id
AND orli.resale_line_int_id = p_resale_line_int_id
AND orlo.duplicated_line_id IS NULL
AND orlo.duplicated_adjustment_id IS NULL
AND orlo.direct_customer_flag = 'F'
AND orlo.creation_date <= orli.creation_date
AND rownum = 1;
SELECT orl.resale_line_id
FROM ozf_resale_lines_all orl,
ozf_resale_lines_int_all orli
-- Bug 4670154 (+)
/*
FROM ozf_resale_lines orl,
ozf_resale_lines_int orli
*/
-- Bug 4670154 (-)
WHERE orl.order_number = orli.order_number
AND nvl (orl.order_line_number, 0) = nvl (orli.order_line_number, 0) -- For Bug#9447673 SSD IDSM ER
AND orl.date_ordered = orli.date_ordered
-- 6704619 (+)
AND (orl.date_shipped = orli.date_shipped
OR (orl.date_shipped IS NULL AND orli.date_shipped IS NULL))
-- 6704619 (-)
--AND orl.invoice_number = orli.invoice_number
--AND orl.date_invoiced = orli.date_invoiced
AND orl.inventory_item_id = orli.inventory_item_id
AND orl.quantity = orli.quantity
AND orl.uom_code = orli.uom_code
AND NVL(orl.sold_from_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.sold_from_cust_account_id,FND_API.G_MISS_NUM)
--AND orl.ship_from_cust_account_id = orli.sold_from_cust_account_id
AND NVL(orl.ship_from_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.ship_from_cust_account_id,FND_API.G_MISS_NUM)
AND orl.bill_to_cust_account_id is null
AND orl.bill_to_party_name is null
AND orl.direct_customer_flag is null
AND orli.resale_line_int_id = p_resale_line_int_id
AND rownum = 1;
SELECT orlo.resale_line_int_id
-- Bug 4670154 (+)
FROM ozf_resale_lines_int_all orlo,
ozf_resale_lines_int_all orli
/*
FROM ozf_resale_lines_int orlo,
ozf_resale_lines_int orli
*/
-- Bug 4670154 (-)
WHERE orlo.order_number = orli.order_number
AND nvl (orlo.order_line_number, 0) = nvl (orli.order_line_number, 0) -- For Bug#9447673 SSD IDSM ER
AND orlo.date_ordered = orli.date_ordered
-- 6704619 (+)
AND (orlo.date_shipped = orli.date_shipped
OR (orlo.date_shipped IS NULL AND orli.date_shipped IS NULL))
-- 6704619 (-)
AND orlo.inventory_item_id = orli.inventory_item_id
AND orlo.quantity = orli.quantity
AND orlo.uom_code = orli.uom_code
AND NVL(orlo.sold_from_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.sold_from_cust_account_id,FND_API.G_MISS_NUM)
--AND orlo.ship_from_cust_account_id = orli.sold_from_cust_account_id
AND NVL(orlo.ship_from_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.ship_from_cust_account_id,FND_API.G_MISS_NUM)
AND orlo.claimed_amount = orli.claimed_amount
AND orlo.bill_to_cust_account_id is null
AND orlo.bill_to_party_name is null
AND orlo.direct_customer_flag is null
AND orlo.status_code in (OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN, OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED)
AND orlo.resale_line_int_id <> p_resale_line_int_id
AND orlo.resale_batch_id <> orli.resale_batch_id
AND orli.resale_line_int_id = p_resale_line_int_id
AND orlo.duplicated_line_id IS NULL
AND orlo.duplicated_adjustment_id IS NULL
AND orlo.creation_date <= orli.creation_date
AND rownum = 1;
SELECT orlo.resale_line_int_id
FROM ozf_resale_lines_int_all orlo,
ozf_resale_lines_int_all orli
WHERE orlo.order_number = orli.order_number
AND nvl (orlo.order_line_number, 0) = nvl (orli.order_line_number, 0) -- For Bug#9447673 SSD IDSM ER
AND orlo.date_ordered = orli.date_ordered
-- 6704619 (+)
AND (orlo.date_shipped = orli.date_shipped
OR (orlo.date_shipped IS NULL AND orli.date_shipped IS NULL))
-- 6704619 (-)
AND orlo.inventory_item_id = orli.inventory_item_id
AND orlo.quantity = orli.quantity
AND orlo.uom_code = orli.uom_code
AND NVL(orlo.sold_from_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.sold_from_cust_account_id,FND_API.G_MISS_NUM)
AND NVL(orlo.ship_from_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.ship_from_cust_account_id,FND_API.G_MISS_NUM)
--AND orlo.claimed_amount = orli.claimed_amount
AND NVL(orlo.bill_to_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.bill_to_cust_account_id,FND_API.G_MISS_NUM)
AND orlo.status_code IN ('OPEN', 'PROCESSED')
AND orlo.resale_line_int_id <> p_resale_line_int_id
AND orlo.resale_batch_id = orli.resale_batch_id
AND orli.resale_line_int_id = p_resale_line_int_id
AND orlo.duplicated_line_id IS NULL
AND orlo.duplicated_adjustment_id IS NULL
AND orlo.direct_customer_flag = 'T'
AND orlo.creation_date <= orli.creation_date
AND rownum = 1;
SELECT orlo.resale_line_int_id
FROM ozf_resale_lines_int_all orlo,
ozf_resale_lines_int_all orli
WHERE orlo.order_number = orli.order_number
AND orlo.date_ordered = orli.date_ordered
AND nvl (orlo.order_line_number, 0) = nvl (orli.order_line_number, 0) -- For Bug#9447673 SSD IDSM ER
-- 6704619 (+)
AND (orlo.date_shipped = orli.date_shipped
OR (orlo.date_shipped IS NULL AND orli.date_shipped IS NULL))
-- 6704619 (-)
AND orlo.inventory_item_id = orli.inventory_item_id
AND orlo.quantity = orli.quantity
AND orlo.uom_code = orli.uom_code
AND NVL(orlo.sold_from_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.sold_from_cust_account_id,FND_API.G_MISS_NUM)
AND NVL(orlo.ship_from_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.ship_from_cust_account_id,FND_API.G_MISS_NUM)
--AND orlo.claimed_amount = orli.claimed_amount
AND orlo.bill_to_party_name = orli.bill_to_party_name
AND orlo.status_code IN ('OPEN', 'PROCESSED')
AND orlo.resale_line_int_id <> p_resale_line_int_id
AND orlo.resale_batch_id = orli.resale_batch_id
AND orli.resale_line_int_id = p_resale_line_int_id
AND orlo.duplicated_line_id IS NULL
AND orlo.duplicated_adjustment_id IS NULL
AND orlo.direct_customer_flag = 'F'
AND orlo.creation_date <= orli.creation_date
AND rownum = 1;
SELECT orlo.resale_line_int_id
FROM ozf_resale_lines_int_all orlo,
ozf_resale_lines_int_all orli
WHERE orlo.order_number = orli.order_number
AND nvl (orlo.order_line_number, 0) = nvl (orli.order_line_number, 0) -- For Bug#9447673 SSD IDSM ER
AND orlo.date_ordered = orli.date_ordered
-- 6704619 (+)
AND (orlo.date_shipped = orli.date_shipped
OR (orlo.date_shipped IS NULL AND orli.date_shipped IS NULL))
-- 6704619 (-)
AND orlo.inventory_item_id = orli.inventory_item_id
AND orlo.quantity = orli.quantity
AND orlo.uom_code = orli.uom_code
AND NVL(orlo.sold_from_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.sold_from_cust_account_id,FND_API.G_MISS_NUM)
AND NVL(orlo.ship_from_cust_account_id,FND_API.G_MISS_NUM) = NVL(orli.ship_from_cust_account_id,FND_API.G_MISS_NUM)
--AND orlo.claimed_amount = orli.claimed_amount
AND orlo.bill_to_cust_account_id is null
AND orlo.bill_to_party_name is null
AND orlo.direct_customer_flag is null
AND orlo.status_code IN ('OPEN', 'PROCESSED')
AND orlo.resale_line_int_id <> p_resale_line_int_id
AND orlo.resale_batch_id = orli.resale_batch_id
AND orli.resale_line_int_id = p_resale_line_int_id
AND orlo.duplicated_line_id IS NULL
AND orlo.duplicated_adjustment_id IS NULL
AND orlo.creation_date <= orli.creation_date
AND rownum = 1;
l_dup_resl_csr_stmt VARCHAR2(4000) := ' SELECT orl.resale_line_id
FROM ozf_resale_lines_all orl,
ozf_resale_lines_int_all orli ';
l_dup_iface_csr_stmt VARCHAR2(4000) :=' SELECT orlo.resale_line_int_id
FROM ozf_resale_lines_int_all orlo,
ozf_resale_lines_int_all orli ';
PROCEDURE Update_Duplicates (
p_api_version IN NUMBER
,p_init_msg_LIST IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_resale_batch_id IN NUMBER
,p_resale_batch_type IN VARCHAR2
,p_batch_status IN VARCHAR2
,x_batch_status OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Duplicates';
SELECT resale_line_int_id, direct_customer_flag, claimed_amount
FROM ozf_resale_lines_int
WHERE resale_batch_id = p_id
--AMITAMKU, Fix for bug 14685303 - Added condition to avoid processing disputed line for duplicate check
AND status_code <> OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DISPUTED;
SELECT NVL(batch_count,0), batch_number
FROM ozf_resale_batches
WHERE resale_batch_id = pc_batch_id;
SELECT count(1)
FROM ozf_resale_lines_int
WHERE status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED
AND resale_batch_id = p_id;
SAVEPOINT Update_Duplicates;
UPDATE ozf_resale_lines_int_all
SET duplicated_line_id = NULL
, duplicated_adjustment_id = NULL
WHERE resale_line_int_id = l_open_lines_tbl(i);
UPDATE ozf_resale_lines_int
SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED
, duplicated_line_id = l_dup_line_id
, duplicated_adjustment_id = l_dup_adjustment_id
, dispute_code = 'OZF_RESALE_DUP'
WHERE resale_line_int_id = l_open_lines_tbl(i);
Insert_Resale_Log (
p_id_value => l_open_lines_tbl(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_RESALE_DUP',
p_column_name => 'DUPLICATED_ADJUSTMENT_ID',
p_column_value => l_dup_adjustment_id,
x_return_status => l_return_status );
UPDATE ozf_resale_lines_int
SET duplicated_line_id = l_dup_line_id
, duplicated_adjustment_id = l_dup_adjustment_id
WHERE resale_line_int_id = l_open_lines_tbl(i);
UPDATE ozf_resale_batches_all
SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_REJECTED
WHERE resale_batch_id = p_resale_batch_id;
UPDATE ozf_resale_batches_all
SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
WHERE resale_batch_id = p_resale_batch_id;
ROLLBACK TO Update_Duplicates;
ROLLBACK TO Update_Duplicates;
ROLLBACK TO Update_Duplicates;
END Update_Duplicates;
SELECT batch_count
, status_code
, report_date
, report_start_date
, report_end_date
, partner_cust_account_id
FROM ozf_resale_batches
WHERE resale_batch_id = p_resale_batch_id;
SELECT count(1)
FROM ozf_resale_lines_int
WHERE resale_batch_id = p_resale_batch_id;
SELECT count(1)
FROM ozf_resale_batch_line_maps
WHERE resale_batch_id = p_resale_batch_id;
SELECT 1
FROM dual
WHERE EXISTS (SELECT hca.cust_account_id
FROM hz_cust_accounts hca
WHERE hca.cust_account_id = p_id);
Insert_Resale_Log (
p_id_value => p_resale_batch_id,
p_id_type => G_ID_TYPE_BATCH,
p_error_code => 'OZF_BATCH_STATUS_WNG',
p_column_name => 'STATUS_CODE',
p_column_value => l_status_code,
x_return_status => l_return_status );
Insert_Resale_Log (
p_id_value => p_resale_batch_id,
p_id_type => G_ID_TYPE_BATCH,
p_error_code => 'OZF_BATCH_REPORT_DATE_NULL',
p_column_name => 'REPORT_date',
p_column_value => NULL,
x_return_status => l_return_status );
Insert_Resale_Log (
p_id_value => p_resale_batch_id,
p_id_type => G_ID_TYPE_BATCH,
p_error_code => 'OZF_BATCH_REPORT_ST_DATE_NULL',
p_column_name => 'REPORT_START_date',
p_column_value => NULL,
x_return_status => l_return_status );
Insert_Resale_Log (
p_id_value => p_resale_batch_id,
p_id_type => G_ID_TYPE_BATCH,
p_error_code => 'OZF_BATCH_REPORT_END_DATE_NULL',
p_column_name => 'REPORT_END_date',
p_column_value => NULL,
x_return_status => l_return_status );
Insert_Resale_Log (
p_id_value => p_resale_batch_id,
p_id_type => G_ID_TYPE_BATCH,
p_error_code => 'OZF_RESALE_WNG_DATE_RANGE',
p_column_name => 'REPORT_START_DATE',
p_column_value => NULL,
x_return_status => l_return_status );
Insert_Resale_Log (
p_id_value => p_resale_batch_id,
p_id_type => G_ID_TYPE_BATCH,
p_error_code => 'OZF_BATCH_PARTNER_NULL',
p_column_name => 'PARTNER_CUST_ACCOUNT_ID',
p_column_value => NULL,
x_return_status => l_return_status );
Insert_Resale_Log (
p_id_value => p_resale_batch_id,
p_id_type => G_ID_TYPE_BATCH,
p_error_code => 'OZF_BATCH_PARTNER_ERR',
p_column_name => 'PARTNER_CUST_ACCOUNT_ID',
p_column_value => l_partner_cust_account_id,
x_return_status => l_return_status );
Insert_Resale_Log (
p_id_value => p_resale_batch_id,
p_id_type => G_ID_TYPE_BATCH,
p_error_code => 'OZF_BATCH_COUNT_NULL',
p_column_name => 'BATCH_COUNT',
p_column_value => NULL,
x_return_status => l_return_status );
Insert_Resale_Log (
p_id_value => p_resale_batch_id,
p_id_type => G_ID_TYPE_BATCH,
p_error_code => 'OZF_BATCH_LINE_COUNT_ERR',
p_column_name => NULL,
p_column_value => NULL,
x_return_status => l_return_status );
Insert_Resale_Log (
p_id_value => p_resale_batch_id,
p_id_type => G_ID_TYPE_BATCH,
p_error_code => 'OZF_BATCH_COUNT_ERR',
p_column_name => 'BATCH_COUNT',
p_column_value => l_batch_count,
x_return_status => l_return_status );
UPDATE ozf_resale_batches
SET status_code= G_BATCH_DISPUTED
WHERE resale_batch_id = p_resale_batch_id;
UPDATE ozf_resale_lines_int_all
SET duplicated_line_id = p_line_id
, duplicated_adjustment_id =l_adjustment_id
WHERE duplicated_line_id = p_line_int_rec.resale_line_int_id
AND duplicated_adjustment_id = -1;
OZF_RESALE_ADJUSTMENTS_PKG.Insert_Row(
px_resale_adjustment_id => l_adjustment_id,
px_object_version_number => l_obj_ver_num,
p_last_update_date => sysdate,
p_last_updated_by => NVL(FND_GLOBAL.user_id,-1),
p_creation_date => sysdate,
p_request_id => FND_GLOBAL.CONC_REQUEST_ID,
p_created_by => NVL(FND_GLOBAL.user_id,-1),
p_created_from => p_price_adj_rec.created_from,
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_resale_line_id => p_price_adj_rec.resale_line_id,
p_resale_batch_id => p_price_adj_rec.resale_batch_id,
p_orig_system_agreement_uom => p_price_adj_rec.orig_system_agreement_uom,
p_orig_system_agreement_name => p_price_adj_rec.orig_system_agreement_name,
p_orig_system_agreement_type => p_price_adj_rec.orig_system_agreement_type,
p_orig_system_agreement_status=> p_price_adj_rec.orig_system_agreement_status,
p_orig_system_agreement_curr => p_price_adj_rec.orig_system_agreement_curr,
p_orig_system_agreement_price => p_price_adj_rec.orig_system_agreement_price,
p_orig_system_agreement_quant => p_price_adj_rec.orig_system_agreement_quantity,
p_agreement_id => p_price_adj_rec.agreement_id ,
p_agreement_type => p_price_adj_rec.agreement_type ,
p_agreement_name => p_price_adj_rec.agreement_name ,
p_agreement_price => p_price_adj_rec.agreement_price ,
p_agreement_uom_code => p_price_adj_rec.agreement_uom_code,
p_corrected_agreement_id => p_price_adj_rec.corrected_agreement_id ,
p_corrected_agreement_name => p_price_adj_rec.corrected_agreement_name ,
p_credit_code => p_price_adj_rec.credit_code,
p_credit_advice_date => p_price_adj_rec.credit_advice_date,
p_total_allowed_amount => p_price_adj_rec.total_allowed_amount,
p_allowed_amount => p_price_adj_rec.allowed_amount,
p_total_accepted_amount => p_price_adj_rec.total_accepted_amount,
p_accepted_amount => p_price_adj_rec.accepted_amount,
p_total_claimed_amount => p_price_adj_rec.total_claimed_amount,
p_claimed_amount => p_price_adj_rec.claimed_amount,
p_calculated_price => p_price_adj_rec.calculated_price,
p_acctd_calculated_price => p_price_adj_rec.acctd_calculated_price,
p_calculated_amount => p_price_adj_rec.calculated_amount,
p_line_agreement_flag => p_price_adj_rec.line_agreement_flag,
p_tolerance_flag => p_price_adj_rec.tolerance_flag,
p_line_tolerance_amount => p_price_adj_rec.line_tolerance_amount,
p_operand => p_price_adj_rec.operand,
p_operand_calculation_code => p_price_adj_rec.operand_calculation_code,
p_priced_quantity => p_price_adj_rec.priced_quantity,
p_priced_uom_code => p_price_adj_rec.priced_uom_code,
p_priced_unit_price => p_price_adj_rec.priced_unit_price,
p_list_header_id => p_price_adj_rec.list_header_id,
p_list_line_id => p_price_adj_rec.list_line_id,
p_status_code => 'CLOSED',
px_org_id => l_org_id
);
SELECT a.resale_adjustment_id,
a.total_accepted_amount
FROM ozf_resale_adjustments a,
ozf_resale_batches b,
ozf_resale_lines c
WHERE a.resale_line_id = p_line_id
AND a.resale_batch_id = b.resale_batch_id
AND b.batch_type = p_batch_type
AND b.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED
AND c.resale_line_id = a.resale_line_id
AND c.status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
-- BUG 4670154 (+)
AND a.list_header_id IS NULL
AND a.list_line_id IS NULL;
SELECT *
FROM ozf_resale_adjustments
WHERE resale_adjustment_id = p_adj_id;
SELECT
orha.ship_from_stock_flag,
orha.offer_type,
qlha.list_header_id
FROM
ozf_request_headers_all_vl orha,
ozf_resale_batches_all orba,
qp_list_headers_all qlha
WHERE
orha.agreement_number = p_agreement_num
AND orha.status_code = 'APPROVED'
AND orha.request_class = 'SPECIAL_PRICE'
AND orha.partner_id = orba.partner_id
AND orba.resale_batch_id = p_resale_batch_id
AND orha.authorization_code = qlha.name;
UPDATE ozf_resale_lines_int_all
SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_DUPLICATED,
duplicated_line_id = p_line_id,
duplicated_adjustment_id = l_dup_adjustment_id
WHERE resale_line_int_id = l_line_int_rec.resale_line_int_id;
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 NVL(direct_order_flag,'N')
FROM ozf_resale_batches_all
WHERE resale_batch_id = p_id;
PROCEDURE Insert_Resale_Header(
p_api_version IN NUMBER
,p_init_msg_LIST IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_line_int_rec IN g_interface_rec_csr%rowtype
,x_header_id OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_resale_header';
SAVEPOINT Insert_Resale_Header;
OZF_UTILITY_PVT.debug_message('before INSERT: header_id' || l_header_id);
OZF_RESALE_HEADERS_PKG.Insert_Row(
px_resale_header_id => l_header_id,
px_object_version_number => l_object_version_number,
p_last_update_date => SYSdate,
p_last_updated_by => NVL(FND_GLOBAL.user_id,-1),
p_creation_date => SYSdate,
p_request_id => FND_GLOBAL.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 => p_line_int_rec.created_from,
p_date_shipped => p_line_int_rec.date_shipped,
p_date_ordered => p_line_int_rec.date_ordered,
p_order_type_id => p_line_int_rec.order_type_id,
p_order_type => p_line_int_rec.order_type,
p_order_category => p_line_int_rec.order_category,
p_status_code => G_BATCH_PROCESSED,
p_direct_customer_flag => p_line_int_rec.direct_customer_flag,
p_order_number => p_line_int_rec.order_number,
p_price_LIST_id => p_line_int_rec.price_LIST_id,
p_bill_to_cust_account_id => p_line_int_rec.bill_to_cust_account_id,
p_bill_to_site_use_id => p_line_int_rec.bill_to_site_use_id,
p_bill_to_party_name => p_line_int_rec.bill_to_party_name,
p_bill_to_party_id =>p_line_int_rec.bill_to_party_id ,
p_bill_to_party_site_id =>p_line_int_rec.bill_to_party_site_id ,
p_bill_to_location => p_line_int_rec.bill_to_location ,
p_bill_to_duns_number => p_line_int_rec.bill_to_duns_number,
p_bill_to_address => p_line_int_rec.bill_to_address,
p_bill_to_city => p_line_int_rec.bill_to_city ,
p_bill_to_state => p_line_int_rec.bill_to_state,
p_bill_to_postal_code => p_line_int_rec.bill_to_postal_code,
p_bill_to_country => p_line_int_rec.bill_to_country,
p_bill_to_contact_party_id => p_line_int_rec.bill_to_contact_party_id,
p_bill_to_contact_name => p_line_int_rec.bill_to_contact_name,
p_bill_to_email => p_line_int_rec.bill_to_email,
p_bill_to_phone => p_line_int_rec.bill_to_phone,
p_bill_to_fax => p_line_int_rec.bill_to_fax,
p_ship_to_cust_account_id => p_line_int_rec.ship_to_cust_account_id,
p_ship_to_site_use_id => p_line_int_rec.ship_to_site_use_id,
p_ship_to_party_name => p_line_int_rec.ship_to_party_name,
p_ship_to_party_id =>p_line_int_rec.ship_to_party_id ,
p_ship_to_party_site_id =>p_line_int_rec.ship_to_party_site_id ,
p_ship_to_location => p_line_int_rec.ship_to_location,
p_ship_to_duns_number => p_line_int_rec.ship_to_duns_number,
p_ship_to_address => p_line_int_rec.ship_to_address,
p_ship_to_city => p_line_int_rec.ship_to_city,
p_ship_to_state => p_line_int_rec.ship_to_state,
p_ship_to_postal_code => p_line_int_rec.ship_to_postal_code,
p_ship_to_country => p_line_int_rec.ship_to_country,
p_ship_to_contact_party_id => p_line_int_rec.ship_to_contact_party_id,
p_ship_to_contact_name => p_line_int_rec.ship_to_contact_name,
p_ship_to_email => p_line_int_rec.ship_to_email,
p_ship_to_phone => p_line_int_rec.ship_to_phone,
p_ship_to_fax => p_line_int_rec.ship_to_fax,
p_sold_from_cust_account_id=> p_line_int_rec.sold_from_cust_account_id,
p_ship_from_cust_account_id=> p_line_int_rec.ship_from_cust_account_id,
p_header_attribute_category=> p_line_int_rec.header_attribute_category,
p_header_attribute1 => p_line_int_rec.header_attribute1,
p_header_attribute2 => p_line_int_rec.header_attribute2,
p_header_attribute3 => p_line_int_rec.header_attribute3,
p_header_attribute4 => p_line_int_rec.header_attribute4,
p_header_attribute5 => p_line_int_rec.header_attribute5,
p_header_attribute6 => p_line_int_rec.header_attribute6,
p_header_attribute7 => p_line_int_rec.header_attribute7,
p_header_attribute8 => p_line_int_rec.header_attribute8,
p_header_attribute9 => p_line_int_rec.header_attribute9,
p_header_attribute10 => p_line_int_rec.header_attribute10,
p_header_attribute11 => p_line_int_rec.header_attribute11,
p_header_attribute12 => p_line_int_rec.header_attribute12,
p_header_attribute13 => p_line_int_rec.header_attribute13,
p_header_attribute14 => p_line_int_rec.header_attribute14,
p_header_attribute15 => p_line_int_rec.header_attribute15,
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);
ROLLBACK TO Insert_Resale_Header;
ROLLBACK TO Insert_Resale_Header;
ROLLBACK TO Insert_Resale_Header;
END Insert_Resale_Header;
PROCEDURE Insert_Resale_Line(
p_api_version IN NUMBER
,p_init_msg_LIST IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_line_int_rec IN g_interface_rec_csr%rowtype
,p_header_id IN NUMBER
,x_line_id OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_resale_line';
SAVEPOINT Insert_Resale_Line;
OZF_UTILITY_PVT.debug_message('before line INSERT: header id' || p_header_id);
OZF_UTILITY_PVT.debug_message('before line INSERT:' || l_line_id);
OZF_RESALE_LINES_PKG.Insert_Row(
p_resale_line_id => l_line_id ,
p_resale_header_id => p_header_id ,
p_resale_transfer_type => p_line_int_rec.resale_transfer_type ,
px_object_version_number => l_obj_ver_num,
p_last_update_date => SYSdate,
p_last_updated_by => NVL(FND_GLOBAL.user_id,-1),
p_creation_date => SYSdate,
p_request_id => FND_GLOBAL.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 => p_line_int_rec.created_from,
p_status_code => G_BATCH_ADJ_PROCESSED ,
p_product_trans_movement_type => p_line_int_rec.product_transfer_movement_type ,
p_product_transfer_date => p_line_int_rec.product_transfer_date,
p_end_cust_party_id => p_line_int_rec.end_cust_party_id,
p_end_cust_site_use_id => p_line_int_rec.end_cust_site_use_id,
p_end_cust_site_use_code => p_line_int_rec.end_cust_site_use_code,
p_end_cust_party_site_id => p_line_int_rec.end_cust_party_site_id ,
p_end_cust_party_name => p_line_int_rec.end_cust_party_name ,
p_end_cust_location => p_line_int_rec.end_cust_location ,
p_end_cust_address => p_line_int_rec.end_cust_address ,
p_end_cust_city => p_line_int_rec.end_cust_city ,
p_end_cust_state => p_line_int_rec.end_cust_state ,
p_end_cust_postal_code => p_line_int_rec.end_cust_postal_code ,
p_end_cust_country => p_line_int_rec.end_cust_country ,
p_end_cust_contact_party_id => p_line_int_rec.end_cust_contact_party_id ,
p_end_cust_contact_name => p_line_int_rec.end_cust_contact_name ,
p_end_cust_email => p_line_int_rec.end_cust_email ,
p_end_cust_phone => p_line_int_rec.end_cust_phone ,
p_end_cust_fax => p_line_int_rec.end_cust_fax ,
p_bill_to_cust_account_id => p_line_int_rec.bill_to_cust_account_id,
p_bill_to_site_use_id => p_line_int_rec.bill_to_site_use_id ,
p_bill_to_party_name => p_line_int_rec.bill_to_party_name ,
p_bill_to_party_id => p_line_int_rec.bill_to_party_id ,
p_bill_to_party_site_id => p_line_int_rec.bill_to_party_site_id ,
p_bill_to_duns_number => p_line_int_rec.bill_to_duns_number ,
p_bill_to_location => p_line_int_rec.bill_to_location ,
p_bill_to_address => p_line_int_rec.bill_to_address ,
p_bill_to_city => p_line_int_rec.bill_to_city ,
p_bill_to_state => p_line_int_rec.bill_to_state ,
p_bill_to_postal_code => p_line_int_rec.bill_to_postal_code ,
p_bill_to_country => p_line_int_rec.bill_to_country ,
p_bill_to_contact_party_id => p_line_int_rec.bill_to_contact_party_id ,
p_bill_to_contact_name => p_line_int_rec.bill_to_contact_name ,
p_bill_to_email => p_line_int_rec.bill_to_email ,
p_bill_to_phone => p_line_int_rec.bill_to_phone ,
p_bill_to_fax => p_line_int_rec.bill_to_fax ,
p_ship_to_cust_account_id => p_line_int_rec.ship_to_cust_account_id ,
p_ship_to_site_use_id => p_line_int_rec.ship_to_site_use_id ,
p_ship_to_party_name => p_line_int_rec.ship_to_party_name ,
p_ship_to_party_id => p_line_int_rec.ship_to_party_id ,
p_ship_to_party_site_id => p_line_int_rec.ship_to_party_site_id ,
p_ship_to_duns_number => p_line_int_rec.ship_to_duns_number ,
p_ship_to_location => p_line_int_rec.ship_to_location ,
p_ship_to_address => p_line_int_rec.ship_to_address,
p_ship_to_city => p_line_int_rec.ship_to_city ,
p_ship_to_state => p_line_int_rec.ship_to_state ,
p_ship_to_postal_code => p_line_int_rec.ship_to_postal_code ,
p_ship_to_country => p_line_int_rec.ship_to_country ,
p_ship_to_contact_party_id => p_line_int_rec.ship_to_contact_party_id ,
p_ship_to_contact_name => p_line_int_rec.ship_to_contact_name ,
p_ship_to_email => p_line_int_rec.ship_to_email ,
p_ship_to_phone => p_line_int_rec.ship_to_phone ,
p_ship_to_fax => p_line_int_rec.ship_to_fax ,
p_ship_from_cust_account_id => p_line_int_rec.ship_from_cust_account_id ,
p_ship_from_site_id => p_line_int_rec.ship_from_site_id,
p_ship_from_party_name => p_line_int_rec.ship_from_party_name,
p_ship_from_location => p_line_int_rec.ship_from_location ,
p_ship_from_address => p_line_int_rec.ship_from_address ,
p_ship_from_city => p_line_int_rec.ship_from_city ,
p_ship_from_state => p_line_int_rec.ship_from_state ,
p_ship_from_postal_code => p_line_int_rec.ship_from_postal_code ,
p_ship_from_country => p_line_int_rec.ship_from_country,
p_ship_from_contact_party_id => p_line_int_rec.ship_from_contact_party_id ,
p_ship_from_contact_name => p_line_int_rec.ship_from_contact_name ,
p_ship_from_email => p_line_int_rec.ship_from_email ,
p_ship_from_fax => p_line_int_rec.ship_from_fax ,
p_ship_from_phone => p_line_int_rec.ship_from_phone ,
p_sold_from_cust_account_id => p_line_int_rec.sold_from_cust_account_id ,
p_sold_from_site_id => p_line_int_rec.sold_from_site_id ,
p_sold_from_party_name => p_line_int_rec.sold_from_party_name,
p_sold_from_location => p_line_int_rec.sold_from_location ,
p_sold_from_address => p_line_int_rec.sold_from_address ,
p_sold_from_city => p_line_int_rec.sold_from_city ,
p_sold_from_state => p_line_int_rec.sold_from_state ,
p_sold_from_postal_code => p_line_int_rec.sold_from_postal_code ,
p_sold_from_country => p_line_int_rec.sold_from_country,
p_sold_from_contact_party_id => p_line_int_rec.sold_from_contact_party_id ,
p_sold_from_contact_name => p_line_int_rec.sold_from_contact_name ,
p_sold_from_email => p_line_int_rec.sold_from_email,
p_sold_from_phone => p_line_int_rec.sold_from_phone,
p_sold_from_fax => p_line_int_rec.sold_from_fax,
p_price_LIST_id => p_line_int_rec.price_LIST_id ,
p_price_LIST_name => p_line_int_rec.price_LIST_name ,
p_invoice_number => p_line_int_rec.invoice_number ,
p_date_invoiced => p_line_int_rec.date_invoiced,
p_po_number => p_line_int_rec.po_number ,
p_po_release_number => p_line_int_rec.po_release_number ,
p_po_type => p_line_int_rec.po_type ,
p_order_number => p_line_int_rec.order_number ,
p_date_ordered => p_line_int_rec.date_ordered,
p_date_shipped => p_line_int_rec.date_shipped,
p_purchase_uom_code => p_line_int_rec.purchase_uom_code ,
p_quantity => p_line_int_rec.quantity ,
p_uom_code => p_line_int_rec.uom_code ,
p_currency_code => p_line_int_rec.currency_code ,
p_exchange_rate => p_line_int_rec.exchange_rate ,
p_exchange_rate_type => p_line_int_rec.exchange_rate_type,
p_exchange_rate_date => p_line_int_rec.exchange_rate_date,
p_selling_price => p_line_int_rec.selling_price ,
p_acctd_selling_price => p_line_int_rec.acctd_selling_price ,
p_purchase_price => p_line_int_rec.purchase_price ,
p_acctd_purchase_price => p_line_int_rec.acctd_purchase_price ,
p_tracing_flag => p_line_int_rec.tracing_flag ,
p_orig_system_quantity => p_line_int_rec. orig_system_quantity,
p_orig_system_uom => p_line_int_rec.orig_system_uom ,
p_orig_system_currency_code => p_line_int_rec.orig_system_currency_code,
p_orig_system_selling_price => p_line_int_rec.orig_system_selling_price ,
p_orig_system_line_reference => p_line_int_rec.orig_system_line_reference ,
p_orig_system_reference => p_line_int_rec.orig_system_reference ,
p_orig_system_purchase_uom => p_line_int_rec.orig_system_purchase_uom,
p_orig_system_purchase_curr => p_line_int_rec.orig_system_purchase_curr,
p_orig_system_purchase_price => p_line_int_rec.orig_system_purchase_price,
p_orig_system_purchase_quant => p_line_int_rec.orig_system_purchase_quantity,
p_orig_system_item_number => p_line_int_rec.orig_system_item_number,
p_product_category_id => p_line_int_rec.product_category_id ,
p_category_name => p_line_int_rec.category_name ,
p_inventory_item_segment1 => p_line_int_rec.inventory_item_segment1 ,
p_inventory_item_segment2 => p_line_int_rec.inventory_item_segment2 ,
p_inventory_item_segment3 => p_line_int_rec.inventory_item_segment3 ,
p_inventory_item_segment4 => p_line_int_rec.inventory_item_segment4 ,
p_inventory_item_segment5 => p_line_int_rec.inventory_item_segment5 ,
p_inventory_item_segment6 => p_line_int_rec.inventory_item_segment6 ,
p_inventory_item_segment7 => p_line_int_rec.inventory_item_segment7 ,
p_inventory_item_segment8 => p_line_int_rec.inventory_item_segment8 ,
p_inventory_item_segment9 => p_line_int_rec.inventory_item_segment9,
p_inventory_item_segment10 => p_line_int_rec.inventory_item_segment10,
p_inventory_item_segment11 => p_line_int_rec.inventory_item_segment11,
p_inventory_item_segment12 => p_line_int_rec.inventory_item_segment12,
p_inventory_item_segment13 => p_line_int_rec.inventory_item_segment13,
p_inventory_item_segment14 => p_line_int_rec.inventory_item_segment14,
p_inventory_item_segment15 => p_line_int_rec.inventory_item_segment15,
p_inventory_item_segment16 => p_line_int_rec.inventory_item_segment16,
p_inventory_item_segment17 => p_line_int_rec.inventory_item_segment17,
p_inventory_item_segment18 => p_line_int_rec.inventory_item_segment18,
p_inventory_item_segment19 => p_line_int_rec.inventory_item_segment19,
p_inventory_item_segment20 => p_line_int_rec.inventory_item_segment20 ,
p_inventory_item_id => p_line_int_rec.inventory_item_id ,
p_item_description => p_line_int_rec.item_description ,
p_upc_code => p_line_int_rec.upc_code ,
p_item_number => p_line_int_rec.item_number ,
p_direct_customer_flag => p_line_int_rec.direct_customer_flag ,
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,
p_line_attribute_category => p_line_int_rec.line_attribute_category,
p_line_attribute1 => p_line_int_rec.line_attribute1 ,
p_line_attribute2 => p_line_int_rec.line_attribute2 ,
p_line_attribute3 => p_line_int_rec.line_attribute3,
p_line_attribute4 => p_line_int_rec.line_attribute4 ,
p_line_attribute5 => p_line_int_rec.line_attribute5 ,
p_line_attribute6 => p_line_int_rec.line_attribute6 ,
p_line_attribute7 => p_line_int_rec.line_attribute7,
p_line_attribute8 => p_line_int_rec.line_attribute8,
p_line_attribute9 => p_line_int_rec.line_attribute9,
p_line_attribute10 => p_line_int_rec.line_attribute10,
p_line_attribute11 => p_line_int_rec.line_attribute11,
p_line_attribute12 => p_line_int_rec.line_attribute12,
p_line_attribute13 => p_line_int_rec.line_attribute13,
p_line_attribute14 => p_line_int_rec.line_attribute14,
p_line_attribute15 => p_line_int_rec.line_attribute15 ,
px_org_id => l_org_id,
-- For Bug#9447673 SSD IDSM ER(+)
p_invoice_line_number => p_line_int_rec.invoice_line_number,
p_order_line_number => p_line_int_rec.order_line_number,
p_supplier_item_cost => p_line_int_rec.supplier_item_cost,
p_supplier_item_uom => p_line_int_rec.supplier_item_uom
-- For Bug#9447673 SSD IDSM ER(-)
);
OZF_UTILITY_PVT.debug_message('line INSERT successful id:' || l_line_id);
ROLLBACK TO Insert_Resale_Line;
ROLLBACK TO Insert_Resale_Line;
ROLLBACK TO Insert_Resale_Line;
END Insert_Resale_Line;
PROCEDURE Insert_Resale_Line_Mapping(
p_api_version IN NUMBER
,p_init_msg_LIST IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_resale_batch_id IN NUMBER
,p_line_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Resale_Line_Mapping';
SAVEPOINT Insert_Resale_Line_Mapping;
OZF_RESALE_BATCH_LINE_MAPS_PKG.Insert_Row(
px_resale_batch_line_map_id => l_map_id,
p_resale_batch_id => p_resale_batch_id,
p_resale_line_id => p_line_id,
px_object_version_number => l_obj_ver_num,
p_last_update_date => SYSdate,
p_last_updated_by => NVL(FND_GLOBAL.user_id,-1),
p_creation_date => SYSdate,
p_request_id => FND_GLOBAL.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 => 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);
ROLLBACK TO Insert_Resale_Line_Mapping;
ROLLBACK TO Insert_Resale_Line_Mapping;
ROLLBACK TO Insert_Resale_Line_Mapping;
END Insert_Resale_Line_Mapping;
PROCEDURE Delete_Log(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
,p_resale_batch_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Log';
SAVEPOINT Delete_Log;
DELETE FROM OZF_RESALE_LOGS
WHERE resale_id = p_resale_batch_id
AND resale_id_type = G_ID_TYPE_BATCH;
DELETE FROM OZF_RESALE_LOGS a
WHERE exists (
SELECT 1
FROM OZF_RESALE_LINES_INT b
WHERE b.resale_batch_id = p_resale_batch_id
AND a.resale_id = b.resale_line_int_id
AND a.resale_id_type = G_ID_TYPE_IFACE
);
ROLLBACK TO Delete_Log;
ROLLBACK TO Delete_Log;
ROLLBACK TO Delete_Log;
END Delete_Log;
SELECT COUNT(DISTINCT bill_to_party_name)
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 bill_to_party_id
FROM ozf_resale_lines_int_all
WHERE resale_batch_id = cv_resale_batch_id
AND bill_to_party_name = cv_billto_party_name
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 NOT NULL
GROUP BY bill_to_party_id
ORDER BY bill_to_party_id;
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;
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,
bill_to_site_use_id = l_new_party_rec.party_site_use_id
WHERE bill_to_party_id IS NULL
AND bill_to_cust_account_id IS NULL
AND resale_batch_id = p_resale_batch_id
AND bill_to_party_name = l_orig_billto_cust_tbl(i).bill_to_party_name
AND NVL(bill_to_address,1) = NVL(l_orig_billto_cust_tbl(i).bill_to_address,1)
AND NVL(bill_to_city,1) = NVL(l_orig_billto_cust_tbl(i).bill_to_city,1)
AND NVL(bill_to_state,1) = NVL(l_orig_billto_cust_tbl(i).bill_to_state,1)
AND NVL(bill_to_postal_code,1) = NVL(l_orig_billto_cust_tbl(i).bill_to_postal_code,1)
AND NVL(bill_to_country,1) = NVL(l_orig_billto_cust_tbl(i).bill_to_country,1);
SELECT COUNT(DISTINCT ship_to_party_name)
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 ship_to_party_id IS NULL
AND ship_to_cust_account_id IS NULL
AND ship_to_party_name IS NOT NULL;
SELECT ship_to_party_id
FROM ozf_resale_lines_int_all
WHERE resale_batch_id = cv_resale_batch_id
AND ship_to_party_name = cv_shipto_party_name
AND status_code = 'PROCESSED'
AND ( duplicated_line_id IS NULL
OR ( duplicated_line_id IS NOT NULL AND duplicated_adjustment_id = -1)
)
AND ship_to_party_id IS NOT NULL
GROUP BY ship_to_party_id
ORDER BY ship_to_party_id;
SELECT bill_to_party_id
FROM ozf_resale_lines_int_all
WHERE resale_batch_id = cv_resale_batch_id
AND bill_to_party_name = cv_billto_party_name
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 NOT NULL
GROUP BY bill_to_party_id
ORDER BY bill_to_party_id;
SELECT bill_to_party_site_id
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 NOT NULL
AND bill_to_party_site_id IS NOT NULL
AND bill_to_party_name = cv_billto_party_name
AND NVL(bill_to_address,1) = NVL(cv_bill_to_address,1)
AND NVL(bill_to_city,1) = NVL(cv_bill_to_city,1)
AND NVL(bill_to_state,1) = NVL(cv_bill_to_state,1)
AND NVL(bill_to_postal_code,1) = NVL(cv_bill_to_postal_code,1)
AND NVL(bill_to_country,1) = NVL(cv_bill_to_country,1)
GROUP BY bill_to_party_site_id
ORDER BY bill_to_party_site_id;
SELECT DISTINCT ship_to_party_name
, ship_to_address
, ship_to_city
, ship_to_state
, ship_to_postal_code
, ship_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 ship_to_party_id IS NULL
AND ship_to_cust_account_id IS NULL
AND ship_to_party_name IS NOT NULL;
UPDATE ozf_resale_lines_int_all
SET ship_to_party_id = l_new_party_rec.party_id,
ship_to_party_site_id = l_new_party_rec.party_site_id,
ship_to_site_use_id = l_new_party_rec.party_site_use_id
WHERE ship_to_party_id IS NULL
AND ship_to_cust_account_id IS NULL
AND resale_batch_id = p_resale_batch_id
AND ship_to_party_name = l_orig_shipto_cust_tbl(i).ship_to_party_name
AND NVL(ship_to_address,1) = NVL(l_orig_shipto_cust_tbl(i).ship_to_address,1)
AND NVL(ship_to_city,1) = NVL(l_orig_shipto_cust_tbl(i).ship_to_city,1)
AND NVL(ship_to_state,1) = NVL(l_orig_shipto_cust_tbl(i).ship_to_state,1)
AND NVL(ship_to_postal_code,1) = NVL(l_orig_shipto_cust_tbl(i).ship_to_postal_code,1)
AND NVL(ship_to_country,1) = NVL(l_orig_shipto_cust_tbl(i).ship_to_country,1);
SELECT COUNT(DISTINCT end_cust_party_name)
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;
SELECT end_cust_party_id
FROM ozf_resale_lines_int_all
WHERE resale_batch_id = cv_resale_batch_id
AND end_cust_party_name = cv_end_cust_party_name
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 NOT NULL
GROUP BY end_cust_party_id
ORDER BY end_cust_party_id;
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;
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,
end_cust_site_use_id = l_new_party_rec.party_site_use_id
WHERE end_cust_party_id IS NULL
AND resale_batch_id = p_resale_batch_id
AND end_cust_party_name = l_orig_end_cust_tbl(i).end_cust_party_name
AND NVL(end_cust_address,1) = NVL(l_orig_end_cust_tbl(i).end_cust_address,1)
AND NVL(end_cust_city,1) = NVL(l_orig_end_cust_tbl(i).end_cust_city,1)
AND NVL(end_cust_state,1) = NVL(l_orig_end_cust_tbl(i).end_cust_state,1)
AND NVL(end_cust_postal_code,1) = NVL(l_orig_end_cust_tbl(i).end_cust_postal_code,1)
AND NVL(end_cust_country,1) = NVL(l_orig_end_cust_tbl(i).end_cust_country,1)
AND NVL(end_cust_site_use_code,1) = NVL(l_orig_end_cust_tbl(i).end_cust_site_use_code,1);
PROCEDURE update_main_tables (x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY NUMBER,
p_batch_type IN VARCHAR2,
p_batch_id IN NUMBER)
IS
l_activity_budget_id NUMBER;
l_api_name VARCHAR2(30) := 'update_main_tables';
SELECT DISTINCT RESALE_BATCH_ID
FROM OZF_FUNDS_ALL_B_INT
WHERE BATCH_TYPE = p_batch_type;
SELECT
FUND_ID,
SUM(BUDGET_AMOUNT_TC) BUDGET_AMOUNT_TC,
SUM(BUDGET_AMOUNT_FC) BUDGET_AMOUNT_FC,
SUM(AVAILABLE_AMOUNT) AVAILABLE_AMOUNT,
SUM(TRANSFERED_IN_AMT) TRANSFERED_IN_AMT,
SUM(TRANSFERED_OUT_AMT) TRANSFERED_OUT_AMT,
SUM(PLANNED_AMT) PLANNED_AMT,
SUM(COMMITTED_AMT) COMMITTED_AMT,
SUM(EARNED_AMT) EARNED_AMT,
SUM(PAID_AMT) PAID_AMT,
SUM(RECAL_COMMITTED) RECAL_COMMITTED,
SUM(ROLLUP_ORIGINAL_BUDGET) ROLLUP_ORIGINAL_BUDGET,
SUM(ROLLUP_TRANSFERED_IN_AMT) ROLLUP_TRANSFERED_IN_AMT,
SUM(ROLLUP_TRANSFERED_OUT_AMT) ROLLUP_TRANSFERED_OUT_AMT,
SUM(ROLLUP_HOLDBACK_AMT) ROLLUP_HOLDBACK_AMT,
SUM(ROLLUP_PLANNED_AMT) ROLLUP_PLANNED_AMT,
SUM(ROLLUP_COMMITTED_AMT) ROLLUP_COMMITTED_AMT,
SUM(ROLLUP_RECAL_COMMITTED) ROLLUP_RECAL_COMMITTED,
SUM(ROLLUP_EARNED_AMT) ROLLUP_EARNED_AMT,
SUM(ROLLUP_PAID_AMT) ROLLUP_PAID_AMT,
SUM(UTILIZED_AMT) UTILIZED_AMT,
SUM(ROLLUP_UTILIZED_AMT) ROLLUP_UTILIZED_AMT
FROM OZF_FUNDS_ALL_B_INT
WHERE BATCH_TYPE = p_batch_type
GROUP BY RESALE_BATCH_ID, FUND_ID;
SELECT
FUND_ID,
SUM(BUDGET_AMOUNT_TC) BUDGET_AMOUNT_TC,
SUM(BUDGET_AMOUNT_FC) BUDGET_AMOUNT_FC,
SUM(AVAILABLE_AMOUNT) AVAILABLE_AMOUNT,
SUM(TRANSFERED_IN_AMT) TRANSFERED_IN_AMT,
SUM(TRANSFERED_OUT_AMT) TRANSFERED_OUT_AMT,
SUM(PLANNED_AMT) PLANNED_AMT,
SUM(COMMITTED_AMT) COMMITTED_AMT,
SUM(EARNED_AMT) EARNED_AMT,
SUM(PAID_AMT) PAID_AMT,
SUM(RECAL_COMMITTED) RECAL_COMMITTED,
SUM(ROLLUP_ORIGINAL_BUDGET) ROLLUP_ORIGINAL_BUDGET,
SUM(ROLLUP_TRANSFERED_IN_AMT) ROLLUP_TRANSFERED_IN_AMT,
SUM(ROLLUP_TRANSFERED_OUT_AMT) ROLLUP_TRANSFERED_OUT_AMT,
SUM(ROLLUP_HOLDBACK_AMT) ROLLUP_HOLDBACK_AMT,
SUM(ROLLUP_PLANNED_AMT) ROLLUP_PLANNED_AMT,
SUM(ROLLUP_COMMITTED_AMT) ROLLUP_COMMITTED_AMT,
SUM(ROLLUP_RECAL_COMMITTED) ROLLUP_RECAL_COMMITTED,
SUM(ROLLUP_EARNED_AMT) ROLLUP_EARNED_AMT,
SUM(ROLLUP_PAID_AMT) ROLLUP_PAID_AMT,
SUM(UTILIZED_AMT) UTILIZED_AMT,
SUM(ROLLUP_UTILIZED_AMT) ROLLUP_UTILIZED_AMT
FROM OZF_FUNDS_ALL_B_INT
WHERE RESALE_BATCH_ID = p_batch_id
AND BATCH_TYPE = p_batch_type
GROUP BY RESALE_BATCH_ID, FUND_ID;
SELECT
ACTIVITY_BUDGET_ID,
SUM(REQUEST_AMOUNT) REQUEST_AMOUNT,
SUM( APPROVED_AMOUNT) APPROVED_AMOUNT,
SUM(APPROVED_ORIGINAL_AMOUNT) APPROVED_ORIGINAL_AMOUNT,
SUM(APPROVED_AMOUNT_FC) APPROVED_AMOUNT_FC,
SUM(PARENT_SRC_APPRVD_AMT) PARENT_SRC_APPRVD_AMT,
SUM(SRC_CURR_REQUEST_AMT) SRC_CURR_REQUEST_AMT
FROM OZF_ACT_BUDGETS_INT
WHERE BATCH_TYPE = p_batch_type
group by RESALE_BATCH_ID, ACTIVITY_BUDGET_ID;
SELECT DISTINCT
RESALE_BATCH_ID
FROM OZF_FUNDS_ALL_B_INT -- nepanda : changed the table name, as OZF_ACT_BUDGETS_INT will not have any data when its the 1st time accrual is created for the offer.
WHERE BATCH_TYPE = p_batch_type;
SELECT
ACTIVITY_BUDGET_ID,
SUM(REQUEST_AMOUNT) REQUEST_AMOUNT,
SUM( APPROVED_AMOUNT) APPROVED_AMOUNT,
SUM(APPROVED_ORIGINAL_AMOUNT) APPROVED_ORIGINAL_AMOUNT,
SUM(APPROVED_AMOUNT_FC) APPROVED_AMOUNT_FC,
SUM(PARENT_SRC_APPRVD_AMT) PARENT_SRC_APPRVD_AMT,
SUM(SRC_CURR_REQUEST_AMT) SRC_CURR_REQUEST_AMT
FROM OZF_ACT_BUDGETS_INT
WHERE RESALE_BATCH_ID = p_batch_id
AND BATCH_TYPE = p_batch_type
group by RESALE_BATCH_ID, ACTIVITY_BUDGET_ID;
SELECT mc_record_id, object_version_number
FROM ozf_mc_transactions_all
WHERE source_object_id = source_id
AND source_object_name = 'FUND';
SELECT
ORIGINAL_BUDGET,
OBJECT_VERSION_NUMBER,
CURRENCY_CODE_TC,
ORG_ID,
BUDGET_AMOUNT_TC,
BUDGET_AMOUNT_FC,
AVAILABLE_AMOUNT,
TRANSFERED_IN_AMT,
TRANSFERED_OUT_AMT,
PLANNED_AMT,
COMMITTED_AMT,
EARNED_AMT,
PAID_AMT,
RECAL_COMMITTED,
ROLLUP_ORIGINAL_BUDGET,
ROLLUP_TRANSFERED_IN_AMT,
ROLLUP_TRANSFERED_OUT_AMT,
ROLLUP_HOLDBACK_AMT,
ROLLUP_PLANNED_AMT,
ROLLUP_COMMITTED_AMT,
ROLLUP_RECAL_COMMITTED,
ROLLUP_EARNED_AMT,
ROLLUP_PAID_AMT,
UTILIZED_AMT,
ROLLUP_UTILIZED_AMT
FROM OZF_FUNDS_ALL_B
WHERE FUND_ID = p_fund_id;
SELECT objfundsum_id
, object_version_number
, planned_amt
, committed_amt
, recal_committed_amt
, plan_curr_planned_amt
, plan_curr_committed_amt
, plan_curr_recal_committed_amt
, univ_curr_planned_amt
, univ_curr_committed_amt
, univ_curr_recal_committed_amt
, utilized_amt
, utilized_amt
, plan_curr_utilized_amt
, univ_curr_utilized_amt
, earned_amt
, plan_curr_earned_amt
, univ_curr_earned_amt
, paid_amt
, plan_curr_paid_amt
, univ_curr_paid_amt
FROM ozf_object_fund_summary
WHERE object_type = p_object_type
AND object_id = p_object_id
AND fund_id = p_fund_id;
SELECT FUND_TYPE
FROM OZF_FUNDS_ALL_B
WHERE FUND_ID = p_fund_id;
select SUM(amount) amount,
SUM(fund_request_amount) fund_request_amount,
SUM(amount_remaining) amount_remaining,
SUM(univ_curr_amount) univ_curr_amount,
SUM(plan_curr_amount) plan_curr_amount,
gl_posted_flag,
utilization_type,
plan_type,
component_type,
component_id,
fund_id
from ozf_funds_utilized_all_b fut
WHERE
object_type = 'TP_ORDER' AND
utilization_type = 'ACCRUAL' -- nepanda : Fix for 12747322 and 12660466 adjustments were getting added to obj_fundsum table twice
AND object_id IN
(select orl.resale_line_id
from ozf_resale_lines_all orl,
OZF_RESALE_BATCH_LINE_MAPS_ALL orblm
where orl.resale_line_id = orblm.resale_line_id
and orblm.resale_batch_id = p_batch_id)
AND EXISTS (SELECT 1 FROM ozf_act_budgets_int where request_id = fut.request_id)
GROUP BY
gl_posted_flag,
utilization_type,
plan_type,
component_type,
component_id,
fund_id;
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM qp_list_lines line, qp_pricing_attributes attr
WHERE attr.list_header_id = p_qp_list_header_id
AND line.list_line_id = attr.list_line_id
AND NVL(line.accrual_flag, 'N') = 'Y'
AND attr.product_attribute = DECODE(NVL(p_product_type, 'OTHER')
, 'PRODUCT', 'ITEM', 'FAMILY', 'CATEGORY', attr.product_attribute)
AND attr.product_attr_value = NVL(p_product_id, attr.product_attr_value)
AND attr.product_attribute_context = 'ITEM'
);
SELECT offer_type
FROM ozf_offers
WHERE qp_list_header_id = p_offer_id;
SELECT NVL(sob.gl_acct_for_offinv_flag, 'F')
FROM ozf_sys_parameters_all sob
WHERE sob.org_id = p_org_id;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT 'Y' FROM
OZF_VOLUME_SUMMARY_INT
WHERE BATCH_TYPE='TP_ACCRUAL');
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT 'Y' FROM OZF_VOLUME_SUMMARY_INT
WHERE BATCH_TYPE='TP_ACCRUAL'
AND RESALE_BATCH_ID=p_batch_id );
SAVEPOINT update_main_tables;
UPDATE OZF_FUNDS_ALL_B
SET
BUDGET_AMOUNT_TC = NVL(BUDGET_AMOUNT_TC,0) + NVL(funds_int_rec.BUDGET_AMOUNT_TC,0),
BUDGET_AMOUNT_FC = NVL(BUDGET_AMOUNT_FC,0) + NVL(funds_int_rec.BUDGET_AMOUNT_FC,0),
AVAILABLE_AMOUNT = NVL(AVAILABLE_AMOUNT,0) + NVL(funds_int_rec.AVAILABLE_AMOUNT,0),
TRANSFERED_IN_AMT = NVL(TRANSFERED_IN_AMT,0) + NVL(funds_int_rec.TRANSFERED_IN_AMT,0),
TRANSFERED_OUT_AMT = NVL(TRANSFERED_OUT_AMT,0) + NVL(funds_int_rec.TRANSFERED_OUT_AMT,0),
PLANNED_AMT = NVL(PLANNED_AMT,0) + NVL(funds_int_rec.PLANNED_AMT,0),
COMMITTED_AMT = NVL(COMMITTED_AMT,0) + NVL(funds_int_rec.COMMITTED_AMT,0),
EARNED_AMT = NVL(EARNED_AMT,0) + NVL(funds_int_rec.EARNED_AMT,0),
PAID_AMT = NVL(PAID_AMT,0) + NVL(funds_int_rec.PAID_AMT,0),
RECAL_COMMITTED = NVL(RECAL_COMMITTED,0) + NVL(funds_int_rec.RECAL_COMMITTED,0),
ROLLUP_ORIGINAL_BUDGET = NVL(ROLLUP_ORIGINAL_BUDGET,0) + NVL(funds_int_rec.ROLLUP_ORIGINAL_BUDGET,0),
ROLLUP_TRANSFERED_IN_AMT = NVL(ROLLUP_TRANSFERED_IN_AMT,0) + NVL(funds_int_rec.ROLLUP_TRANSFERED_IN_AMT,0),
ROLLUP_TRANSFERED_OUT_AMT = NVL(ROLLUP_TRANSFERED_OUT_AMT,0) + NVL(funds_int_rec.ROLLUP_TRANSFERED_OUT_AMT,0),
ROLLUP_HOLDBACK_AMT = NVL(ROLLUP_HOLDBACK_AMT,0) + NVL(funds_int_rec.ROLLUP_HOLDBACK_AMT,0),
ROLLUP_PLANNED_AMT = NVL(ROLLUP_PLANNED_AMT,0) + NVL(funds_int_rec.ROLLUP_PLANNED_AMT,0),
ROLLUP_COMMITTED_AMT = NVL(ROLLUP_COMMITTED_AMT,0) + NVL(funds_int_rec.ROLLUP_COMMITTED_AMT,0),
ROLLUP_RECAL_COMMITTED = NVL(ROLLUP_RECAL_COMMITTED,0) + NVL(funds_int_rec.ROLLUP_RECAL_COMMITTED,0),
ROLLUP_EARNED_AMT = NVL(ROLLUP_EARNED_AMT,0) + NVL(funds_int_rec.ROLLUP_EARNED_AMT,0),
ROLLUP_PAID_AMT = NVL(ROLLUP_PAID_AMT,0) + NVL(funds_int_rec.ROLLUP_PAID_AMT,0),
UTILIZED_AMT = NVL(UTILIZED_AMT,0) + NVL(funds_int_rec.UTILIZED_AMT,0),
ROLLUP_UTILIZED_AMT = NVL(ROLLUP_UTILIZED_AMT,0) + NVL(funds_int_rec.ROLLUP_UTILIZED_AMT,0),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = NVL (fnd_global.user_id, -1),
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHERE FUND_ID = funds_int_rec.fund_id;
UPDATE ozf_funds_all_tl
SET last_update_date = SYSDATE
,last_updated_by = NVL(fnd_global.user_id, -1)
,last_update_login = NVL(fnd_global.conc_login_id, -1)
,request_id = fnd_global.conc_request_id
,program_application_id = fnd_global.prog_appl_id
,program_id = fnd_global.conc_program_id
,program_update_date = SYSDATE
WHERE fund_id = funds_int_rec.fund_id
AND USERENV('LANG') IN(language, source_lang);
ozf_funds_pvt.update_rollup_amount(
p_api_version => l_api_version
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_fund_rec => l_fund_rec
);
UPDATE ozf_act_budgets
SET request_amount = NVL(request_amount, 0) + NVL(budgets_int_rec.REQUEST_AMOUNT,0)
,src_curr_request_amt = NVL(src_curr_request_amt, 0) + NVL(budgets_int_rec.SRC_CURR_REQUEST_AMT,0)
,approved_amount = NVL(approved_amount, 0) + NVL(budgets_int_rec.APPROVED_AMOUNT,0)
,approved_original_amount = NVL(approved_original_amount, 0) + NVL(budgets_int_rec.APPROVED_ORIGINAL_AMOUNT,0)
,approved_amount_fc = NVL(approved_amount_fc, 0) + NVL(budgets_int_rec.APPROVED_AMOUNT_FC,0)
,parent_src_apprvd_amt = NVL(parent_src_apprvd_amt, 0) + NVL(budgets_int_rec.parent_src_apprvd_amt,0)
,last_update_date = sysdate
,last_updated_by = NVL (fnd_global.user_id, -1)
,last_update_login = NVL (fnd_global.conc_login_id, -1)
,object_version_number = NVL (object_version_number,0) + 1
WHERE activity_budget_id = budgets_int_rec.ACTIVITY_BUDGET_ID;
OZF_OBJFUNDSUM_PVT.update_objfundsum(
p_api_version => 1.0,
p_init_msg_list => Fnd_Api.G_FALSE,
p_validation_level => Fnd_Api.G_VALID_LEVEL_NONE,
p_objfundsum_rec => l_objfundsum_rec,
p_conv_date => l_exchange_rate_date,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
USING (SELECT SUM (GROUP_VOLUME) GROUP_VOLUME,
OFFER_ID,
GROUP_NO,
PBH_LINE_ID
FROM OZF_VOLUME_SUMMARY_INT
WHERE GROUP_NO IS NOT NULL
GROUP BY OFFER_ID, GROUP_NO, PBH_LINE_ID) ovolgrpint
ON (ovolsum.OFFER_ID= ovolgrpint.OFFER_ID
AND ovolsum.GROUP_NO = ovolgrpint.GROUP_NO
AND ovolsum.PBH_LINE_ID= ovolgrpint.PBH_LINE_ID)
WHEN MATCHED THEN
UPDATE
SET ovolsum.GROUP_VOLUME = ovolsum.GROUP_VOLUME + nvl(ovolgrpint.GROUP_VOLUME,0),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
WHEN NOT MATCHED THEN
INSERT
(
ovolsum.volume_summary_id
,ovolsum.creation_date
,ovolsum.created_by
,ovolsum.last_update_date
,ovolsum.last_updated_by
,ovolsum.last_update_login
,ovolsum.offer_id
,ovolsum.group_no
,ovolsum.group_volume
,ovolsum.pbh_line_id
)
VALUES
(
ozf_volume_summary_s.NEXTVAL
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,ovolgrpint.OFFER_ID
,ovolgrpint.GROUP_NO
,ovolgrpint.GROUP_VOLUME
,ovolgrpint.PBH_LINE_ID
);
USING (SELECT SUM (INDIVIDUAL_VOLUME) INDIVIDUAL_VOLUME ,
OFFER_ID,
INDIVIDUAL_TYPE,
PBH_LINE_ID
FROM OZF_VOLUME_SUMMARY_INT
WHERE INDIVIDUAL_TYPE IS NOT NULL AND INDIVIDUAL_ID IS NULL
GROUP BY OFFER_ID, INDIVIDUAL_TYPE, PBH_LINE_ID) ovolindint
ON (ovolsum.OFFER_ID= ovolindint.OFFER_ID
AND ovolsum.INDIVIDUAL_TYPE = ovolindint.INDIVIDUAL_TYPE
AND ovolsum.INDIVIDUAL_ID IS NULL
AND ovolsum.PBH_LINE_ID= ovolindint.PBH_LINE_ID)
WHEN MATCHED THEN
UPDATE
SET ovolsum.INDIVIDUAL_VOLUME = ovolsum.INDIVIDUAL_VOLUME + nvl(ovolindint.INDIVIDUAL_VOLUME,0),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
WHEN NOT MATCHED THEN
INSERT
(
ovolsum.volume_summary_id
,ovolsum.creation_date
,ovolsum.created_by
,ovolsum.last_update_date
,ovolsum.last_updated_by
,ovolsum.last_update_login
,ovolsum.offer_id
,ovolsum.individual_type
,ovolsum.individual_volume
,ovolsum.pbh_line_id
)
VALUES
(
ozf_volume_summary_s.NEXTVAL
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,ovolindint.OFFER_ID
,ovolindint.INDIVIDUAL_TYPE
,ovolindint.INDIVIDUAL_VOLUME
,ovolindint.PBH_LINE_ID
);
USING (SELECT SUM (INDIVIDUAL_VOLUME) INDIVIDUAL_VOLUME ,
OFFER_ID,
INDIVIDUAL_TYPE,
INDIVIDUAL_ID,
PBH_LINE_ID
FROM OZF_VOLUME_SUMMARY_INT
WHERE INDIVIDUAL_TYPE IS NOT NULL AND INDIVIDUAL_ID IS NOT NULL
GROUP BY OFFER_ID, INDIVIDUAL_TYPE,INDIVIDUAL_ID, PBH_LINE_ID) ovolindint
ON (ovolsum.OFFER_ID= ovolindint.OFFER_ID
AND ovolsum.INDIVIDUAL_TYPE = ovolindint.INDIVIDUAL_TYPE
AND ovolsum.INDIVIDUAL_ID = ovolindint.INDIVIDUAL_ID
AND ovolsum.PBH_LINE_ID= ovolindint.PBH_LINE_ID)
WHEN MATCHED THEN
UPDATE
SET ovolsum.INDIVIDUAL_VOLUME = ovolsum.INDIVIDUAL_VOLUME+ nvl(ovolindint.INDIVIDUAL_VOLUME,0),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
WHEN NOT MATCHED THEN
INSERT
(
ovolsum.volume_summary_id
,ovolsum.creation_date
,ovolsum.created_by
,ovolsum.last_update_date
,ovolsum.last_updated_by
,ovolsum.last_update_login
,ovolsum.offer_id
,ovolsum.individual_type
,ovolsum.individual_id
,ovolsum.individual_volume
,ovolsum.pbh_line_id
)
VALUES
(
ozf_volume_summary_s.NEXTVAL
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,ovolindint.OFFER_ID
,ovolindint.INDIVIDUAL_TYPE
,ovolindint.INDIVIDUAL_ID
,ovolindint.INDIVIDUAL_VOLUME
,ovolindint.PBH_LINE_ID
);
UPDATE ozf_resale_batches_all
SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED,
last_updated_by = NVL(fnd_global.user_id, -1),
last_update_date = SYSDATE
WHERE resale_batch_id = batch_id_int_rec.RESALE_BATCH_ID
AND status_code in ('PENDING_CLOSE','PENDING_ACCRUALS');
DELETE FROM OZF_FUNDS_ALL_B_INT WHERE BATCH_TYPE = p_batch_type;
DELETE FROM OZF_ACT_BUDGETS_INT WHERE BATCH_TYPE = p_batch_type;
DELETE FROM OZF_VOLUME_SUMMARY_INT WHERE BATCH_TYPE = 'TP_ACCRUAL';
ozf_utility_pvt.write_conc_log('deleted records from staging table OZF_VOLUME_SUMMARY_INT for Batch Type '||p_batch_type);
ozf_utility_pvt.write_conc_log('deleted records from staging tables OZF_FUNDS_ALL_B_INT and OZF_ACT_BUDGETS_INT ');
UPDATE OZF_FUNDS_ALL_B
SET
BUDGET_AMOUNT_TC = NVL(BUDGET_AMOUNT_TC,0) + NVL(funds_int_rec2.BUDGET_AMOUNT_TC,0),
BUDGET_AMOUNT_FC = NVL(BUDGET_AMOUNT_FC,0) + NVL(funds_int_rec2.BUDGET_AMOUNT_FC,0),
AVAILABLE_AMOUNT = NVL(AVAILABLE_AMOUNT,0) + NVL(funds_int_rec2.AVAILABLE_AMOUNT,0),
TRANSFERED_IN_AMT = NVL(TRANSFERED_IN_AMT,0) + NVL(funds_int_rec2.TRANSFERED_IN_AMT,0),
TRANSFERED_OUT_AMT = NVL(TRANSFERED_OUT_AMT,0) + NVL(funds_int_rec2.TRANSFERED_OUT_AMT,0),
PLANNED_AMT = NVL(PLANNED_AMT,0) + NVL(funds_int_rec2.PLANNED_AMT,0),
COMMITTED_AMT = NVL(COMMITTED_AMT,0) + NVL(funds_int_rec2.COMMITTED_AMT,0),
EARNED_AMT = NVL(EARNED_AMT,0) + NVL(funds_int_rec2.EARNED_AMT,0),
PAID_AMT = NVL(PAID_AMT,0) + NVL(funds_int_rec2.PAID_AMT,0),
RECAL_COMMITTED = NVL(RECAL_COMMITTED,0) + NVL(funds_int_rec2.RECAL_COMMITTED,0),
ROLLUP_ORIGINAL_BUDGET = NVL(ROLLUP_ORIGINAL_BUDGET,0) + NVL(funds_int_rec2.ROLLUP_ORIGINAL_BUDGET,0),
ROLLUP_TRANSFERED_IN_AMT = NVL(ROLLUP_TRANSFERED_IN_AMT,0) + NVL(funds_int_rec2.ROLLUP_TRANSFERED_IN_AMT,0),
ROLLUP_TRANSFERED_OUT_AMT = NVL(ROLLUP_TRANSFERED_OUT_AMT,0) + NVL(funds_int_rec2.ROLLUP_TRANSFERED_OUT_AMT,0),
ROLLUP_HOLDBACK_AMT = NVL(ROLLUP_HOLDBACK_AMT,0) + NVL(funds_int_rec2.ROLLUP_HOLDBACK_AMT,0),
ROLLUP_PLANNED_AMT = NVL(ROLLUP_PLANNED_AMT,0) + NVL(funds_int_rec2.ROLLUP_PLANNED_AMT,0),
ROLLUP_COMMITTED_AMT = NVL(ROLLUP_COMMITTED_AMT,0) + NVL(funds_int_rec2.ROLLUP_COMMITTED_AMT,0),
ROLLUP_RECAL_COMMITTED = NVL(ROLLUP_RECAL_COMMITTED,0) + NVL(funds_int_rec2.ROLLUP_RECAL_COMMITTED,0),
ROLLUP_EARNED_AMT = NVL(ROLLUP_EARNED_AMT,0) + NVL(funds_int_rec2.ROLLUP_EARNED_AMT,0),
ROLLUP_PAID_AMT = NVL(ROLLUP_PAID_AMT,0) + NVL(funds_int_rec2.ROLLUP_PAID_AMT,0),
UTILIZED_AMT = NVL(UTILIZED_AMT,0) + NVL(funds_int_rec2.UTILIZED_AMT,0),
ROLLUP_UTILIZED_AMT = NVL(ROLLUP_UTILIZED_AMT,0) + NVL(funds_int_rec2.ROLLUP_UTILIZED_AMT,0),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = NVL (fnd_global.user_id, -1),
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHERE FUND_ID = funds_int_rec2.FUND_ID;
UPDATE ozf_funds_all_tl
SET last_update_date = SYSDATE
,last_updated_by = NVL(fnd_global.user_id, -1)
,last_update_login = NVL(fnd_global.conc_login_id, -1)
,request_id = fnd_global.conc_request_id
,program_application_id = fnd_global.prog_appl_id
,program_id = fnd_global.conc_program_id
,program_update_date = SYSDATE
WHERE fund_id = funds_int_rec2.FUND_ID
AND USERENV('LANG') IN(language, source_lang);
OZF_UTILITY_PVT.write_conc_log('Updating update_rollup_amount from UPDATE_OZF_FUNDS_ALL_B for batch_id='||p_batch_id);
ozf_funds_pvt.update_rollup_amount(
p_api_version => l_api_version
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_fund_rec => l_fund_rec
);
UPDATE ozf_act_budgets
SET request_amount = NVL(request_amount, 0) + NVL(budgets_int_rec2.REQUEST_AMOUNT,0)
,src_curr_request_amt = NVL(src_curr_request_amt, 0) + NVL(budgets_int_rec2.SRC_CURR_REQUEST_AMT,0)
,approved_amount = NVL(approved_amount, 0) + NVL(budgets_int_rec2.APPROVED_AMOUNT,0)
,approved_original_amount = NVL(approved_original_amount, 0) + NVL(budgets_int_rec2.APPROVED_ORIGINAL_AMOUNT,0)
,approved_amount_fc = NVL(approved_amount_fc, 0) + NVL(budgets_int_rec2.APPROVED_AMOUNT_FC,0)
,parent_src_apprvd_amt = NVL(parent_src_apprvd_amt, 0) + NVL(budgets_int_rec2.parent_src_apprvd_amt,0)
,last_update_date = sysdate
,last_updated_by = NVL (fnd_global.user_id, -1)
,last_update_login = NVL (fnd_global.conc_login_id, -1)
,object_version_number = NVL (object_version_number,0) + 1
WHERE activity_budget_id = l_activity_budget_id;
OZF_OBJFUNDSUM_PVT.update_objfundsum(
p_api_version => 1.0,
p_init_msg_list => Fnd_Api.G_FALSE,
p_validation_level => Fnd_Api.G_VALID_LEVEL_NONE,
p_objfundsum_rec => l_objfundsum_rec,
p_conv_date => l_exchange_rate_date,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
USING (SELECT SUM (GROUP_VOLUME) GROUP_VOLUME,
OFFER_ID,
GROUP_NO,
PBH_LINE_ID ,
RESALE_BATCH_ID
FROM OZF_VOLUME_SUMMARY_INT
WHERE RESALE_BATCH_ID=p_batch_id
AND GROUP_NO IS NOT NULL
GROUP BY RESALE_BATCH_ID, OFFER_ID, GROUP_NO, PBH_LINE_ID) ovolgrpint
ON (ovolsum.OFFER_ID= ovolgrpint.OFFER_ID
AND ovolsum.GROUP_NO = ovolgrpint.GROUP_NO
AND ovolsum.PBH_LINE_ID= ovolgrpint.PBH_LINE_ID)
WHEN MATCHED THEN
UPDATE
SET ovolsum.GROUP_VOLUME = ovolsum.GROUP_VOLUME + nvl(ovolgrpint.GROUP_VOLUME,0),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
WHEN NOT MATCHED THEN
INSERT
(
ovolsum.volume_summary_id
,ovolsum.creation_date
,ovolsum.created_by
,ovolsum.last_update_date
,ovolsum.last_updated_by
,ovolsum.last_update_login
,ovolsum.offer_id
,ovolsum.group_no
,ovolsum.group_volume
,ovolsum.pbh_line_id
)
VALUES
(
ozf_volume_summary_s.NEXTVAL
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,ovolgrpint.OFFER_ID
,ovolgrpint.GROUP_NO
,ovolgrpint.GROUP_VOLUME
,ovolgrpint.PBH_LINE_ID
);
USING (SELECT SUM (INDIVIDUAL_VOLUME) INDIVIDUAL_VOLUME ,
OFFER_ID,
INDIVIDUAL_TYPE,
PBH_LINE_ID ,
RESALE_BATCH_ID
FROM OZF_VOLUME_SUMMARY_INT
WHERE RESALE_BATCH_ID=p_batch_id
AND INDIVIDUAL_TYPE IS NOT NULL AND INDIVIDUAL_ID IS NULL
GROUP BY RESALE_BATCH_ID, OFFER_ID, INDIVIDUAL_TYPE, PBH_LINE_ID) ovolindint
ON (ovolsum.OFFER_ID= ovolindint.OFFER_ID
AND ovolsum.INDIVIDUAL_TYPE = ovolindint.INDIVIDUAL_TYPE
AND ovolsum.INDIVIDUAL_ID IS NULL
AND ovolsum.PBH_LINE_ID= ovolindint.PBH_LINE_ID)
WHEN MATCHED THEN
UPDATE
SET ovolsum.INDIVIDUAL_VOLUME = ovolsum.INDIVIDUAL_VOLUME + nvl(ovolindint.INDIVIDUAL_VOLUME,0),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
WHEN NOT MATCHED THEN
INSERT
(
ovolsum.volume_summary_id
,ovolsum.creation_date
,ovolsum.created_by
,ovolsum.last_update_date
,ovolsum.last_updated_by
,ovolsum.last_update_login
,ovolsum.offer_id
,ovolsum.individual_type
,ovolsum.individual_volume
,ovolsum.pbh_line_id
)
VALUES
(
ozf_volume_summary_s.NEXTVAL
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,ovolindint.OFFER_ID
,ovolindint.INDIVIDUAL_TYPE
,ovolindint.INDIVIDUAL_VOLUME
,ovolindint.PBH_LINE_ID
);
USING (SELECT SUM (INDIVIDUAL_VOLUME) INDIVIDUAL_VOLUME ,
OFFER_ID,
INDIVIDUAL_TYPE,
INDIVIDUAL_ID,
PBH_LINE_ID ,
RESALE_BATCH_ID
FROM OZF_VOLUME_SUMMARY_INT
WHERE RESALE_BATCH_ID=p_batch_id
AND INDIVIDUAL_TYPE IS NOT NULL AND INDIVIDUAL_ID IS NOT NULL
GROUP BY RESALE_BATCH_ID, OFFER_ID, INDIVIDUAL_TYPE,INDIVIDUAL_ID, PBH_LINE_ID) ovolindint
ON (ovolsum.OFFER_ID= ovolindint.OFFER_ID
AND ovolsum.INDIVIDUAL_TYPE = ovolindint.INDIVIDUAL_TYPE
AND ovolsum.INDIVIDUAL_ID = ovolindint.INDIVIDUAL_ID
AND ovolsum.PBH_LINE_ID= ovolindint.PBH_LINE_ID)
WHEN MATCHED THEN
UPDATE
SET ovolsum.INDIVIDUAL_VOLUME = ovolsum.INDIVIDUAL_VOLUME+ nvl(ovolindint.INDIVIDUAL_VOLUME,0),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id
WHEN NOT MATCHED THEN
INSERT
(
ovolsum.volume_summary_id
,ovolsum.creation_date
,ovolsum.created_by
,ovolsum.last_update_date
,ovolsum.last_updated_by
,ovolsum.last_update_login
,ovolsum.offer_id
,ovolsum.individual_type
,ovolsum.individual_id
,ovolsum.individual_volume
,ovolsum.pbh_line_id
)
VALUES
(
ozf_volume_summary_s.NEXTVAL
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,ovolindint.OFFER_ID
,ovolindint.INDIVIDUAL_TYPE
,ovolindint.INDIVIDUAL_ID
,ovolindint.INDIVIDUAL_VOLUME
,ovolindint.PBH_LINE_ID
);
UPDATE ozf_resale_batches_all
SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED,
last_updated_by = NVL(fnd_global.user_id, -1),
last_update_date = SYSDATE
WHERE resale_batch_id = p_batch_id;
DELETE FROM ozf_act_budgets_int WHERE RESALE_BATCH_ID = p_batch_id AND BATCH_TYPE = p_batch_type;
DELETE FROM ozf_funds_all_b_int WHERE RESALE_BATCH_ID = p_batch_id AND BATCH_TYPE = p_batch_type;
ozf_utility_pvt.write_conc_log('deleted records from staging tables OZF_FUNDS_ALL_B_INT and OZF_ACT_BUDGETS_INT for the Batch Id='||p_batch_id);
DELETE FROM OZF_VOLUME_SUMMARY_INT WHERE RESALE_BATCH_ID = p_batch_id AND BATCH_TYPE = 'TP_ACCRUAL' ;
ozf_utility_pvt.write_conc_log('deleted records from staging table OZF_VOLUME_SUMMARY_INT for p_batch_type='||p_batch_type ||' and for batch id '|| p_batch_id );
ROLLBACK TO update_main_tables;
ROLLBACK TO update_main_tables;
ROLLBACK TO update_main_tables;
END update_main_tables;
SELECT gs.currency_code
,org.exchange_rate_type
FROM gl_sets_of_books gs
, ozf_sys_parameters org
WHERE org.set_of_books_id = gs.set_of_books_id
AND org.org_id = p_org_id;
SELECT OZF_FUNDS_UTILIZED_DRAFT_ALL_S.NEXTVAL FROM DUAL;
px_draft_accrual_tbl(line_count).last_update_date := SYSDATE ;
px_draft_accrual_tbl(line_count).last_updated_by := NVL (fnd_global.user_id, -1) ;
px_draft_accrual_tbl(line_count).last_update_login := NVL (fnd_global.conc_login_id, -1) ;
px_draft_accrual_tbl(line_count).program_update_date := SYSDATE ;