[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_Process_Results';
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 orl.sold_from_cust_account_id = orli.sold_from_cust_account_id
--AND orl.ship_from_cust_account_id = orli.sold_from_cust_account_id
AND orl.ship_from_cust_account_id = orli.ship_from_cust_account_id
AND orl.direct_customer_flag = 'T'
AND orl.bill_to_cust_account_id = orli.bill_to_cust_account_id
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 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 orl.sold_from_cust_account_id = orli.sold_from_cust_account_id
--AND orl.ship_from_cust_account_id = orli.sold_from_cust_account_id
AND orl.ship_from_cust_account_id = orli.ship_from_cust_account_id
AND orl.direct_customer_flag = 'F'
AND orl.bill_to_party_name = orli.bill_to_party_name
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 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 orlo.sold_from_cust_account_id = orli.sold_from_cust_account_id
--AND orlo.ship_from_cust_account_id = orli.sold_from_cust_account_id
AND orlo.ship_from_cust_account_id = orli.ship_from_cust_account_id
AND orlo.claimed_amount = orli.claimed_amount
AND orlo.bill_to_cust_account_id = orli.bill_to_cust_account_id
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 orlo.sold_from_cust_account_id = orli.sold_from_cust_account_id
--AND orlo.ship_from_cust_account_id = orli.sold_from_cust_account_id
AND orlo.ship_from_cust_account_id = orli.ship_from_cust_account_id
AND orlo.claimed_amount = orli.claimed_amount
AND orlo.bill_to_party_name = orli.bill_to_party_name
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 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 orl.sold_from_cust_account_id = orli.sold_from_cust_account_id
--AND orl.ship_from_cust_account_id = orli.sold_from_cust_account_id
AND orl.ship_from_cust_account_id = orli.ship_from_cust_account_id
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 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 orlo.sold_from_cust_account_id = orli.sold_from_cust_account_id
--AND orlo.ship_from_cust_account_id = orli.sold_from_cust_account_id
AND orlo.ship_from_cust_account_id = orli.ship_from_cust_account_id
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 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 orlo.sold_from_cust_account_id = orli.sold_from_cust_account_id
AND orlo.ship_from_cust_account_id = orli.ship_from_cust_account_id
--AND orlo.claimed_amount = orli.claimed_amount
AND orlo.bill_to_cust_account_id = orli.bill_to_cust_account_id
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
-- 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 orlo.sold_from_cust_account_id = orli.sold_from_cust_account_id
AND orlo.ship_from_cust_account_id = orli.ship_from_cust_account_id
--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 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 orlo.sold_from_cust_account_id = orli.sold_from_cust_account_id
AND orlo.ship_from_cust_account_id = orli.ship_from_cust_account_id
--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;
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 status_code =OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
WHERE resale_batch_id = p_id;
SELECT NVL(batch_count,0)
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;
UPDATE ozf_resale_lines_int
SET dispute_code = NULL
WHERE resale_batch_id = p_resale_batch_id
AND status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN;
UPDATE ozf_resale_lines_int
SET status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_PROCESSED
WHERE resale_batch_id = p_resale_batch_id
AND status_code= OZF_RESALE_COMMON_PVT.G_BATCH_ADJ_OPEN
AND tracing_flag = 'T';
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;
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;
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 );
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;