[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select resale_batch_id
from ozf_resale_batches
where batch_number = pc_batch_no;
SELECT COUNT(DECODE(b.status_code, 'DISPUTED', 1, NULL)),
COUNT(b.status_code)
FROM ozf_resale_batches_all a
, ozf_resale_lines_int_all b
WHERE a.resale_batch_id = b.resale_batch_id
AND a.resale_batch_id = pc_batch_id;
SELECT org_id
FROM ozf_resale_batches_all
WHERE resale_batch_id = cv_resale_batch_id;
OZF_RESALE_COMMON_PVT.Delete_Log(
p_api_version => 1.0
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,p_resale_batch_id => p_batch_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
Batch_Update
( p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_batch_id => p_batch_id,
x_resale_batch_rec => l_resale_batch_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
Lines_Update
(
p_batch_id => p_batch_id,
px_batch_record => l_resale_batch_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
Update_interface_batch
( p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_int_batch_rec => l_resale_batch_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
Update ozf_resale_batches
SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
WHERE resale_batch_id = p_batch_id;
Update ozf_resale_batches
SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
WHERE resale_batch_id = p_batch_id;
Update ozf_resale_batches
SET status_code = OZF_RESALE_COMMON_PVT.G_BATCH_DISPUTED
WHERE resale_batch_id = p_batch_id;
PROCEDURE Batch_Update (
p_api_version_number 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_batch_id IN NUMBER,
x_resale_batch_rec OUT NOCOPY ozf_resale_batches_all%rowtype,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Batch_Update';
END Batch_Update;
SELECT *
FROM ozf_resale_batches_all
WHERE resale_batch_id = cv_batch_id;
SELECT party_id
, party_site_id
, company_admin_email
FROM ecx_tp_headers_v
WHERE party_site_id = pc_party_site
UNION
SELECT party_id
, party_site_id
, company_admin_email
FROM ecx_tp_headers_v hdr
, ecx_tp_details_v dtl
WHERE hdr.tp_header_id = dtl.tp_header_id
AND source_tp_location_code = to_char(pc_party_site);
insert_resale_log(
p_id_value => p_batch_id,
p_id_type => 'BATCH',
p_error_code => 'OZF_BATCH_RECORD_EMPTY',
p_column_name => NULL,
p_column_value => NULL,
x_return_status => x_return_status
);
ozf_utility_pvt.debug_message('x_return_status from insert resale log' ||x_return_status );
SELECT 'X'
FROM hz_cust_accounts
WHERE cust_account_id = pc_account_id;
insert_resale_log
(p_id_value => p_resale_batch_rec.resale_batch_id,
p_id_type => 'BATCH',
p_error_code => 'OZF_BATCH_PARTNER_NULL',
p_column_name => NULL,
p_column_value => NULL,
x_return_status => x_return_status);
insert_resale_log
( p_id_value => p_resale_batch_rec.resale_batch_id,
p_id_type => 'BATCH',
p_error_code => 'OZF_REPORT_START_DATE_NULL',
p_column_name => NULL,
p_column_value => NULL,
x_return_status => x_return_status
);
insert_resale_log
(p_id_value => p_resale_batch_rec.resale_batch_id,
p_id_type => 'BATCH',
p_error_code => 'OZF_REPORT_END_DATE_NULL',
p_column_name => NULL,
p_column_value => NULL,
x_return_status => x_return_status
);
insert_resale_log
(p_id_value => p_resale_batch_rec.resale_batch_id,
p_id_type => 'BATCH',
p_error_code => 'OZF_RESALE_WNG_DATE_RANGE',
p_column_name => 'REPORT_END_DATE',
p_column_value => p_resale_batch_rec.report_end_date,
x_return_status => x_return_status
);
insert_resale_log
(p_id_value => p_resale_batch_rec.resale_batch_id,
p_id_type => 'BATCH',
p_error_code => 'OZF_BATCH_PARTNER_ERR',
p_column_name => 'PARTNER_CUST_ACCOUNT_ID',
p_column_value => p_resale_batch_rec.partner_cust_account_id,
x_return_status => x_return_status
);
insert_resale_log
(p_id_value => p_resale_batch_rec.resale_batch_id,
p_id_type => 'BATCH',
p_error_code => 'OZF_BATCH_TYPE_NULL',
p_column_name => 'BATCH_TYPE',
p_column_value => p_resale_batch_rec.batch_type,
x_return_status => x_return_status
);
SELECT pvpp.partner_id
FROM pv_partner_profiles pvpp
WHERE pvpp.partner_party_id = pc_party_id;
SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = pc_account_id;
insert_resale_log
(p_id_value => px_resale_batch_rec.resale_batch_id,
p_id_type => 'BATCH',
p_error_code => 'OZF_DQM_PROCESS_ERROR',
p_column_name => 'P_PARTNER_CONTACT_NAME',
p_column_value => px_resale_batch_rec.batch_type,
x_return_status => x_return_status
);
PROCEDURE Lines_Update
(
p_batch_id IN NUMBER,
px_batch_record IN OUT NOCOPY ozf_resale_batches_all%rowtype,
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) := 'Lines_Update';
SELECT resale_line_int_id
, object_version_number
, resale_batch_id
, status_code
, resale_transfer_type
, product_transfer_movement_type
, tracing_flag
, ship_from_cust_account_id
, ship_from_site_id
, ship_from_party_name
, ship_from_location
, ship_from_address
, ship_from_city
, ship_from_state
, ship_from_postal_code
, ship_from_country
, ship_from_contact_party_id
, ship_from_contact_name
, ship_from_email
, ship_from_fax
, ship_from_phone
, sold_from_cust_account_id
, sold_from_site_id
, sold_from_party_name
, sold_from_location
, sold_from_address
, sold_from_city
, sold_from_state
, sold_from_postal_code
, sold_from_country
, sold_from_contact_party_id
, sold_from_contact_name
, sold_from_email
, sold_from_phone
, sold_from_fax
, bill_to_cust_account_id
, bill_to_site_use_id
, bill_to_party_id
, bill_to_party_site_id
, bill_to_party_name
, bill_to_duns_number
, bill_to_location
, bill_to_address
, bill_to_city
, bill_to_state
, bill_to_postal_code
, bill_to_country
, bill_to_contact_party_id
, bill_to_contact_name
, bill_to_email
, bill_to_phone
, bill_to_fax
, ship_to_cust_account_id
, ship_to_site_use_id
, ship_to_party_id
, ship_to_party_site_id
, ship_to_party_name
, ship_to_duns_number
, ship_to_location
, ship_to_address
, ship_to_city
, ship_to_country
, ship_to_postal_code
, ship_to_state
, ship_to_contact_party_id
, ship_to_contact_name
, ship_to_email
, ship_to_phone
, ship_to_fax
, end_cust_party_id
, end_cust_site_use_id
, end_cust_site_use_code
, end_cust_party_site_id
, end_cust_party_name
, end_cust_location
, end_cust_address
, end_cust_city
, end_cust_state
, end_cust_postal_code
, end_cust_country
, end_cust_contact_party_id
, end_cust_contact_name
, end_cust_email
, end_cust_phone
, end_cust_fax
, direct_customer_flag
, order_type_id
, order_type
, order_category
, agreement_type
, agreement_id
, agreement_name
, agreement_price
, agreement_uom_code
, corrected_agreement_id
, corrected_agreement_name
, price_list_id
, orig_system_currency_code
, orig_system_selling_price
, orig_system_quantity
, orig_system_uom
, orig_system_purchase_uom
, orig_system_purchase_curr
, orig_system_purchase_price
, orig_system_purchase_quantity
, orig_system_agreement_uom
, orig_system_agreement_name
, orig_system_agreement_type
, orig_system_agreement_curr
, orig_system_agreement_price
, orig_system_agreement_quantity
, orig_system_item_number
, currency_code
, exchange_rate_type
, exchange_rate_date
, exchange_rate
, order_number
, date_ordered
, claimed_amount
, total_claimed_amount
, purchase_price
, acctd_purchase_price
, purchase_uom_code
, selling_price
, acctd_selling_price
, uom_code
, quantity
, inventory_item_id
, item_number
, dispute_code
, data_source_code
, org_id
, response_code
FROM ozf_resale_lines_int
WHERE resale_batch_id = cv_batch_id
-- AND status_code IN ('NEW', 'OPEN', 'DUPLICATED', 'DISPUTED', 'PROCESSED')
--------------------------------------------------------------
-- We're going to process all the lines regardless of the status
-- Since there is no closed line, we don't need status code clause
--------------------------------------------------------------
ORDER BY resale_line_int_id;
Lines_Bulk_Update
(
p_batch_id => p_batch_id,
p_line_record => l_line_record,
x_return_status => x_return_status
);
END Lines_Update;
PROCEDURE Lines_Bulk_Update
(
p_batch_id IN NUMBER,
p_line_record IN resale_line_int_rec_type,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Lines_Bulk_Update';
ozf_utility_pvt.debug_message('Line Count in line bulk update ' || p_line_record.resale_line_int_id.COUNT);
UPDATE ozf_resale_lines_int_all
SET object_version_number = p_line_record.object_version_number(i)
, resale_batch_id = p_line_record.resale_batch_id(i)
, status_code = p_line_record.status_code(i)
, resale_transfer_type = p_line_record.resale_transfer_type(i)
, product_transfer_movement_type= p_line_record.product_transfer_movement_type(i)
, tracing_flag = p_line_record.tracing_flag(i)
, ship_from_cust_account_id = p_line_record.ship_from_cust_account_id(i)
, ship_from_site_id = p_line_record.ship_from_site_id(i)
, ship_from_party_name = p_line_record.ship_from_party_name(i)
, ship_from_location = p_line_record.ship_from_location(i)
, ship_from_address = p_line_record.ship_from_address(i)
, ship_from_city = p_line_record.ship_from_city(i)
, ship_from_state = p_line_record.ship_from_state(i)
, ship_from_postal_code = p_line_record.ship_from_postal_code(i)
, ship_from_country = p_line_record.ship_from_country(i)
, ship_from_contact_party_id = p_line_record.ship_from_contact_party_id(i)
, ship_from_contact_name = p_line_record.ship_from_contact_name(i)
, ship_from_email = p_line_record.ship_from_email(i)
, ship_from_fax = p_line_record.ship_from_fax(i)
, ship_from_phone = p_line_record.ship_from_phone(i)
, sold_from_cust_account_id = p_line_record.sold_from_cust_account_id(i)
, sold_from_site_id = p_line_record.sold_from_site_id(i)
, sold_from_party_name = p_line_record.sold_from_party_name(i)
, sold_from_location = p_line_record.sold_from_location(i)
, sold_from_address = p_line_record.sold_from_address(i)
, sold_from_city = p_line_record.sold_from_city(i)
, sold_from_state = p_line_record.sold_from_state(i)
, sold_from_postal_code = p_line_record.sold_from_postal_code(i)
, sold_from_country = p_line_record.sold_from_country(i)
, sold_from_contact_party_id = p_line_record.sold_from_contact_party_id(i)
, sold_from_contact_name = p_line_record.sold_from_contact_name(i)
, sold_from_email = p_line_record.sold_from_email(i)
, sold_from_phone = p_line_record.sold_from_phone(i)
, sold_from_fax = p_line_record.sold_from_fax(i)
, bill_to_cust_account_id = p_line_record.bill_to_cust_account_id(i)
, bill_to_site_use_id = p_line_record.bill_to_site_use_id(i)
, bill_to_party_id = p_line_record.bill_to_party_id(i)
, bill_to_party_site_id = p_line_record.bill_to_party_site_id(i)
, bill_to_party_name = p_line_record.bill_to_party_name(i)
, bill_to_duns_number = p_line_record.bill_to_duns_number(i)
, bill_to_location = p_line_record.bill_to_location(i)
, bill_to_address = p_line_record.bill_to_address(i)
, bill_to_city = p_line_record.bill_to_city(i)
, bill_to_state = p_line_record.bill_to_state(i)
, bill_to_postal_code = p_line_record.bill_to_postal_code(i)
, bill_to_country = p_line_record.bill_to_country(i)
, bill_to_contact_party_id = p_line_record.bill_to_contact_party_id(i)
, bill_to_contact_name = p_line_record.bill_to_contact_name(i)
, bill_to_email = p_line_record.bill_to_email(i)
, bill_to_phone = p_line_record.bill_to_phone(i)
, bill_to_fax = p_line_record.bill_to_fax(i)
, ship_to_cust_account_id = p_line_record.ship_to_cust_account_id(i)
, ship_to_site_use_id = p_line_record.ship_to_site_use_id(i)
, ship_to_party_id = p_line_record.ship_to_party_id(i)
, ship_to_party_site_id = p_line_record.ship_to_party_site_id(i)
, ship_to_party_name = p_line_record.ship_to_party_name(i)
, ship_to_duns_number = p_line_record.ship_to_duns_number(i)
, ship_to_location = p_line_record.ship_to_location(i)
, ship_to_address = p_line_record.ship_to_address(i)
, ship_to_city = p_line_record.ship_to_city(i)
, ship_to_country = p_line_record.ship_to_country(i)
, ship_to_postal_code = p_line_record.ship_to_postal_code(i)
, ship_to_state = p_line_record.ship_to_state(i)
, ship_to_contact_party_id = p_line_record.ship_to_contact_party_id(i)
, ship_to_contact_name = p_line_record.ship_to_contact_name(i)
, ship_to_email = p_line_record.ship_to_email(i)
, ship_to_phone = p_line_record.ship_to_phone(i)
, ship_to_fax = p_line_record.ship_to_fax(i)
, end_cust_party_id = p_line_record.end_cust_party_id(i)
, end_cust_site_use_id = p_line_record.end_cust_site_use_id(i)
, end_cust_site_use_code = p_line_record.end_cust_site_use_code(i)
, end_cust_party_site_id = p_line_record.end_cust_party_site_id(i)
, end_cust_party_name = p_line_record.end_cust_party_name(i)
, end_cust_location = p_line_record.end_cust_location(i)
, end_cust_address = p_line_record.end_cust_address(i)
, end_cust_city = p_line_record.end_cust_city(i)
, end_cust_state = p_line_record.end_cust_state(i)
, end_cust_postal_code = p_line_record.end_cust_postal_code(i)
, end_cust_country = p_line_record.end_cust_country(i)
, end_cust_contact_party_id = p_line_record.end_cust_contact_party_id(i)
, end_cust_contact_name = p_line_record.end_cust_contact_name(i)
, end_cust_email = p_line_record.end_cust_email(i)
, end_cust_phone = p_line_record.end_cust_phone(i)
, end_cust_fax = p_line_record.end_cust_fax(i)
, direct_customer_flag = p_line_record.direct_customer_flag(i)
, order_type_id = p_line_record.order_type_id(i)
, order_type = p_line_record.order_type(i)
, order_category = p_line_record.order_category(i)
, agreement_type = p_line_record.agreement_type(i)
, agreement_id = p_line_record.agreement_id(i)
, agreement_name = p_line_record.agreement_name(i)
, agreement_price = p_line_record.agreement_price(i)
, agreement_uom_code = p_line_record.agreement_uom_code(i)
, corrected_agreement_id = p_line_record.corrected_agreement_id(i)
, corrected_agreement_name = p_line_record.corrected_agreement_name(i)
, price_list_id = p_line_record.price_list_id(i)
, orig_system_currency_code = p_line_record.orig_system_currency_code(i)
, orig_system_selling_price = p_line_record.orig_system_selling_price(i)
, orig_system_quantity = p_line_record.orig_system_quantity(i)
, orig_system_uom = p_line_record.orig_system_uom(i)
, orig_system_purchase_uom = p_line_record.orig_system_purchase_uom(i)
, orig_system_purchase_curr = p_line_record.orig_system_purchase_curr(i)
, orig_system_purchase_price = p_line_record.orig_system_purchase_price(i)
, orig_system_purchase_quantity = p_line_record.orig_system_purchase_quantity(i)
, orig_system_agreement_uom = p_line_record.orig_system_agreement_uom(i)
, orig_system_agreement_name = p_line_record.orig_system_agreement_name(i)
, orig_system_agreement_type = p_line_record.orig_system_agreement_type(i)
, orig_system_agreement_curr = p_line_record.orig_system_agreement_curr(i)
, orig_system_agreement_price = p_line_record.orig_system_agreement_price(i)
, orig_system_agreement_quantity= p_line_record.orig_system_agreement_quantity(i)
, orig_system_item_number = p_line_record.orig_system_item_number(i)
, currency_code = p_line_record.currency_code(i)
, exchange_rate_type = p_line_record.exchange_rate_type(i)
, exchange_rate = p_line_record.exchange_rate(i)
, order_number = p_line_record.order_number(i)
, date_ordered = p_line_record.date_ordered(i)
, claimed_amount = p_line_record.claimed_amount(i)
, total_claimed_amount = p_line_record.total_claimed_amount(i)
, purchase_price = p_line_record.purchase_price(i)
, acctd_purchase_price = p_line_record.acctd_purchase_price(i)
, purchase_uom_code = p_line_record.purchase_uom_code(i)
, selling_price = p_line_record.selling_price(i)
, acctd_selling_price = p_line_record.acctd_selling_price(i)
, uom_code = p_line_record.uom_code(i)
, quantity = p_line_record.quantity(i)
, inventory_item_id = p_line_record.inventory_item_id(i)
, item_number = p_line_record.item_number(i)
, dispute_code = p_line_record.dispute_code(i)
, data_source_code = p_line_record.data_source_code(i)
, org_id = p_line_record.org_id(i)
, response_code = p_line_record.response_code(i)
WHERE resale_batch_id = p_batch_id
AND resale_line_int_id = p_line_record.resale_line_int_id(i);
END Lines_Bulk_Update;
SELECT transaction_type_id
FROM oe_transaction_types_vl
WHERE transaction_type_code = 'ORDER'
AND TO_DATE(TO_CHAR(pc_date_ordered, 'MM/DD/YYYY'), 'MM/DD/YYYY')
BETWEEN start_date_active AND NVL(end_date_active, SYSDATE)
AND name = pc_order_type;
SELECT 'X'
FROM oe_transaction_types_vl
WHERE transaction_type_id = pc_order_type_id
UNION ALL
SELECT 'X'
FROM oe_transaction_types_vl
WHERE name = pc_order_type ;
SELECT gs.currency_code
FROM gl_sets_of_books gs,
ozf_sys_parameters osp
WHERE gs.set_of_books_id = osp.set_of_books_id
AND osp.org_id = MO_GLOBAL.get_current_org_id(); -- BUG 5058027
SELECT concatenated_segments
FROM mtl_system_items_vl
WHERE inventory_item_id = pc_item_id
AND organization_id = pc_org_id;
SELECT b.orig_org_id
FROM qp_list_headers_b b
, qp_list_headers_tl tl
WHERE b.list_header_id = tl.list_header_id
ANd b.list_header_id = cv_price_list_id;
SELECT org_id
FROM ozf_request_headers_all_b
WHERE request_header_id = cv_reqeust_header_id;
Insert_Resale_Log(
p_id_value => px_line_record.resale_line_int_id(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_RESALE_SOLD_FROM_MISS',
p_column_name => 'SOLD_FROM_CUST_ACCOUNT_ID',
p_column_value => NULL,
x_return_status => x_return_status
);
Insert_Resale_Log(
p_id_value => px_line_record.resale_line_int_id(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_RESALE_SHIP_FROM_MISS',
p_column_name => 'SHIP_FROM_CUST_ACCOUNT_ID',
p_column_value => NULL,
x_return_status => x_return_status
);
Insert_Resale_Log(
p_id_value => px_line_record.resale_line_int_id(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_BILL_TO_ACCT_NULL',
p_column_name => 'BILL_TO_CUST_ACCOUNT_ID',
p_column_value => NULL,
x_return_status => x_return_status
);
Insert_Resale_Log (
p_id_value => px_line_record.resale_line_int_id(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_BILL_TO_PARTY_NAME_NULL',
p_column_name => 'BILL_TO_PARTY_NAME',
p_column_value => NULL,
x_return_status => x_return_status
);
Insert_Resale_Log (
p_id_value => px_line_record.resale_line_int_id(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_MOVEMENT_TYPE_NULL',
p_column_name => 'PRODUCT_TRANSFER_MOVEMENT_TYPE',
p_column_value => NULL,
x_return_status => x_return_status
);
Insert_Resale_Log (
p_id_value => px_line_record.resale_line_int_id(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_RESALE_ORD_NUM_MISS',
p_column_name => 'ORDER_NUMBER',
p_column_value => NULL,
x_return_status => x_return_status
);
Insert_Resale_Log(
p_id_value => px_line_record.resale_line_int_id(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_RESALE_WRNG_TRANSFER_TYPE',
p_column_name => 'RESALE_TRANSFER_TYPE',
p_column_value => px_line_record.resale_transfer_type(i),
x_return_status => x_return_status
);
Insert_Resale_Log (
p_id_value => px_line_record.resale_line_int_id(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_RESALE_WRNG_ORD_CGRY',
p_column_name => 'ORDER_CATEGORY',
p_column_value => px_line_record.order_category(i),
x_return_status => x_return_status
);
Insert_Resale_Log(
p_id_value => px_line_record.resale_line_int_id(i),
p_id_type => 'IFACE',
p_error_code => l_dispute_code,
p_column_name => l_column_name,
p_column_value => l_column_value,
x_return_status => x_return_status
);
Insert_Resale_Log(
p_id_value => px_line_record.resale_line_int_id(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_IDSM_INV_ITEM_REQ',
p_column_name => 'INVENTORY_ITEM_ID',
p_column_value => NULL,
x_return_status => x_return_status
);
Insert_Resale_Log(
p_id_value => px_line_record.resale_line_int_id(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_IDSM_INV_ORDER_DATE_REQ',
p_column_name => 'DATE_ORDERED',
p_column_value => NULL,
x_return_status => x_return_status
);
Insert_Resale_Log(
p_id_value => px_line_record.resale_line_int_id(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_IDSM_INV_UOM_REQ',
p_column_name => 'UOM_CODE',
p_column_value => NULL,
x_return_status => x_return_status
);
Insert_Resale_Log(
p_id_value => px_line_record.resale_line_int_id(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_IDSM_INV_QTY_REQ',
p_column_name => 'QUANTITY',
p_column_value => NULL,
x_return_status => x_return_status
);
Insert_Resale_Log(
p_id_value => px_line_record.resale_line_int_id(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_IDSM_AGRM_MISMATCH_OU',
p_column_name => 'AGREEMENT_NAME',
p_column_value => NULL,
x_return_status => x_return_status
);
SELECT cust.cust_account_id
FROM hz_cust_accounts cust
WHERE cust.party_id = pc_party_Id;
SELECT hcsu.site_use_id
FROM hz_cust_acct_sites hcs --hz_cust_acct_sites_all hcs
, hz_cust_site_uses hcsu --, hz_cust_site_uses_all hcsu
WHERE hcsu.cust_acct_site_id = hcs.cust_acct_site_id
AND hcs.cust_account_id = pc_cust_account_id
AND hcsu.site_use_code = pc_site_use
AND hcsu.primary_flag= 'Y'
AND hcsu.status = 'A';
SELECT hp.party_id , hc.cust_account_id
FROM hz_party_sites hps
, hz_parties hp
, hz_cust_accounts hc
WHERE hps.party_id = hp.party_id
AND hp.party_id = hc.party_id (+)
AND hps.party_site_id = cv_party_site_id;
l_party_site_id.delete;
l_party_id.delete;
l_party_site_id.delete;
l_party_id.delete;
SELECT gs.currency_code
FROM gl_sets_of_books gs,
ozf_sys_parameters osp
WHERE gs.set_of_books_id = osp.set_of_books_id
AND osp.org_id = MO_GLOBAL.get_current_org_id();
SELECT currency_code
FROM fnd_currencies_vl
WHERE name = p_name;
insert_resale_log (
p_id_value => p_int_line_id_tbl(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_CURR_CONV_ERROR',
p_column_name => 'CURRENCY_CODE',
p_column_value => p_ext_currency_tbl(i),
x_return_status => x_return_status
);
insert_resale_log (
p_id_value => p_int_line_id_tbl(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_CURR_CONV_ERROR',
p_column_name => 'CURRENCY_CODE',
p_column_value => px_currency_tbl(i),
x_return_status => x_return_status
);
SELECT name
FROM qp_list_headers_vl
WHERE list_header_id = pc_agreement_id
AND list_type_code = 'PRL';
SELECT agreement_number
FROM ozf_request_headers_all_b
WHERE request_header_id = pc_agreement_id;
SELECT request_header_id
FROM ozf_request_headers_all_b
WHERE agreement_number = pc_agreement_name;
SELECT 'X'
FROM qp_list_headers_vl
WHERE list_header_id = pc_agreement_id
AND list_type_code = 'PRL';
SELECT 'X'
FROM ozf_request_headers_all_b
WHERE request_header_id = pc_agreement_id;
SELECT list_header_id
FROM qp_list_headers_vl
WHERE name = pc_agreement_name
AND list_type_code = 'PRL';
SELECT a.request_header_id
FROM ozf_request_headers_all_b a
, qp_list_headers_vl b
WHERE a.offer_id = b.list_header_id
AND a.status_code = 'APPROVED'
AND b.name = cv_agreement_name;
Insert_Resale_Log(
p_id_value => p_interface_line_id_tbl(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_INVALID_AGREEMENT_TYPE',
p_column_name => 'AGREEMENT_TYPE',
p_column_value => px_int_agreement_type(i),
x_return_status => x_return_status
);
Insert_Resale_Log(
p_id_value => p_interface_line_id_tbl(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_AGREEMENT_MISS',
p_column_name => 'AGREEMENT_NAME',
p_column_value => NULL,
x_return_status => x_return_status
);
SELECT inventory_item_id
FROM mtl_system_items_vl
WHERE concatenated_segments = pc_item_number
AND organization_id = pc_org_id;
SELECT 'X'
FROM mtl_system_items
WHERE inventory_item_id = pc_item_id;
Insert_Resale_Log(
p_id_value => p_interface_line_id_tbl(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_RESALE_PRODUCT_NOT_IN_DB',
p_column_name => 'INVENTORY_ITEM_ID',
p_column_value => p_ext_item_number_tbl(i),
x_return_status => x_return_status
);
Insert_Resale_Log(
p_id_value => p_interface_line_id_tbl(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_PRODUCT_CODE_MAP_MISS',
p_column_name => 'INVENTORY_ITEM_ID',
p_column_value => p_ext_item_number_tbl(i),
x_return_status => x_return_status
);
Insert_Resale_Log(
p_id_value => p_interface_line_id_tbl(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_RESALE_PRODUCT_ID_MISS',
p_column_name => 'INVENTORY_ITEM_ID',
p_column_value => p_item_number_tbl(i),
x_return_status => x_return_status
);
Insert_Resale_Log(
p_id_value => p_interface_line_id_tbl(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_UOM_CODE_MAP_MISS',
p_column_name => 'PURCHASE_UOM',
p_column_value => p_ext_purchase_uom(i),
x_return_status => x_return_status
);
l_temp_code_tbl.DELETE;
Insert_Resale_Log(
p_id_value => p_interface_line_id_tbl(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_UOM_CODE_MAP_MISS',
p_column_name => 'UOM',
p_column_value => p_ext_uom(i),
x_return_status => x_return_status
);
l_temp_code_tbl.DELETE;
Insert_Resale_Log(
p_id_value => p_interface_line_id_tbl(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_UOM_CODE_MAP_MISS',
p_column_name => 'AGREEMENT_UOM',
p_column_value => p_ext_agreement_uom(i),
x_return_status => x_return_status
);
SELECT cust.cust_account_id
FROM hz_cust_accounts cust
WHERE cust.party_id = pc_party_Id;
SELECT hcsu.site_use_id
FROM hz_cust_acct_sites hcs --hz_cust_acct_sites_all hcs
, hz_cust_site_uses hcsu --, hz_cust_site_uses_all hcsu
WHERE hcsu.cust_acct_site_id = hcs.cust_acct_site_id
AND hcs.cust_account_id = pc_cust_account_id
AND hcsu.site_use_code = pc_site_use
AND hcsu.primary_flag= 'Y'
AND hcsu.status = 'A';
SELECT hcsu.site_use_id , hp.party_id , hc.cust_account_id
FROM hz_cust_acct_sites hcs --hz_cust_acct_sites_all hcs
, hz_cust_site_uses hcsu --, hz_cust_site_uses_all hcsu
, hz_party_sites hps
, hz_parties hp
, hz_cust_accounts hc
WHERE hcsu.cust_acct_site_id = hcs.cust_acct_site_id
AND hcs.party_site_id = hps.party_site_id
AND hps.party_id = hp.party_id
AND hcs.cust_account_id = hc.cust_account_id
AND hc.party_id = hp.party_id
AND hcsu.site_use_code = pc_site_use
AND hps.party_site_id = pc_party_site_id;
SELECT hp.party_id , hc.cust_account_id
FROM hz_party_sites hps
, hz_parties hp
, hz_cust_accounts hc
WHERE hps.party_id = hp.party_id
AND hp.party_id = hc.party_id (+)
AND hps.party_site_id = cv_party_site_id;
SELECT cust.party_id
, pt.party_name
FROM hz_cust_accounts cust, hz_parties pt
WHERE cust_account_id = pc_account_Id
AND cust.party_id = pt.party_id ;
SELECT hcsu.site_use_id
, hcas.party_site_id
, hp.party_id
FROM hz_cust_acct_sites hcas, --hz_cust_acct_sites_all hcas,
hz_cust_site_uses hcsu, -- hz_cust_site_uses_all hcsu,
hz_party_sites hps,
hz_parties hp
WHERE hcas.party_site_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hps.party_id = hp.party_id
AND hcas.cust_account_id = pc_account_id
AND hcsu.location = pc_location
AND hcsu.site_use_code = pc_site_use;
SELECT hcas.cust_acct_site_id
FROM hz_cust_acct_sites hcas, --hz_cust_acct_sites_all hcas,
hz_cust_site_uses hcsu, --hz_cust_site_uses_all hcsu,
hz_party_sites hps
WHERE hcas.party_site_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = pc_account_id
AND hcsu.location = pc_location
AND hcsu.site_use_code = pc_site_use;
SELECT hcas.cust_account_id
, hps.party_site_id
, hp.party_id
, hp.party_name
FROM hz_cust_acct_sites hcas, --hz_cust_acct_sites_all hcas,
hz_cust_site_uses hcsu, --hz_cust_site_uses_all hcsu,
hz_party_sites hps,
hz_parties hp
WHERE hcas.party_site_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hps.party_id = hp.party_id
AND hcsu.site_use_id = pc_site_use_id;
SELECT hcas.cust_account_id
, hp.party_id
, hp.party_name
FROM hz_cust_acct_sites_all hcas,
hz_party_sites hps,
hz_parties hp
WHERE hcas.party_site_id = hps.party_site_id
AND hps.party_id = hp.party_id
AND hcas.cust_acct_site_id = pc_site_id;
SELECT hps.party_site_id
FROM hz_cust_acct_sites hcas, --hz_cust_acct_sites_all hcas,
hz_cust_site_uses hcsu, --hz_cust_site_uses_all hcsu,
hz_party_sites hps
WHERE hcas.party_site_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsu.cust_acct_site_id = pc_site_use_id
UNION
SELECT hps.party_site_id
FROM hz_cust_acct_sites hcas, --hz_cust_acct_sites_all hcas,
hz_cust_site_uses hcsu, --hz_cust_site_uses_all hcsu,
hz_party_sites hps
WHERE hcas.party_site_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsu.site_use_id = pc_site_use_id;
SELECT hca.cust_account_id,
hp.party_id,
hcas.cust_acct_site_id,
hp.party_name
FROM hz_cust_accounts hca,
hz_parties hp,
hz_cust_acct_sites_all hcas,
hz_party_sites hps
WHERE hcas.party_site_id = hps.party_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND hp.party_id = hca.party_id
AND hps.party_site_id = pc_address_id;
SELECT hca.cust_account_id,
hcsu.site_use_id,
hp.party_id,
hcas.party_site_id,
hp.party_name
FROM hz_cust_accounts hca,
hz_parties hp,
hz_cust_site_uses hcsu, --hz_cust_site_uses_all hcsu,
hz_cust_acct_sites hcas, --hz_cust_acct_sites_all hcas,
hz_party_sites hps,
hz_locations hl
WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hcsu.status = 'A'
AND hps.location_id = hl.location_id
AND hcas.cust_account_id = hca.cust_account_id
AND hp.party_id = hca.party_id
AND hps.party_site_id = pc_address_id
AND hcsu.site_use_code = pc_site_use;
SELECT hca.cust_account_id,
decode(p_party_type, 'SHIP_FROM',hcas.cust_acct_site_id,
'SOLD_FROM',hcas.cust_acct_site_id,
'BILL_TO', hcsu.site_use_id,
'SHIP_TO', hcsu.site_use_id,
'END_CUST', hcsu.site_use_id ) site_id,
decode(p_party_type,'SHIP_FROM', NULL,
'SOLD_FROM', NULL,
hps.party_site_id) party_site_id,
hp.party_id
FROM hz_cust_accounts hca,
hz_parties hp,
hz_cust_site_uses hcsu, --hz_cust_site_uses_all hcsu,
hz_cust_acct_sites hcas, --hz_cust_acct_sites_all hcas,
hz_party_sites hps,
hz_locations hl
WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hcsu.status = 'A'
AND hps.location_id = hl.location_id
AND hcas.cust_account_id = hca.cust_account_id
AND hp.party_id = hca.party_id
AND hcsu.location = pc_location
AND hcsu.site_use_code = pc_site_use
AND hp.party_name = pc_party_name;
SELECT hca.cust_account_id,
decode('SHIP_FROM', 'SHIP_FROM',hcas.cust_acct_site_id,
'SOLD_FROM',hcas.cust_acct_site_id,
'BILL_TO', hcsu.site_use_id,
'SHIP_TO', hcsu.site_use_id,
'END_CUST', hcsu.site_use_id ) site_id,
decode('SHIP_FROM','SHIP_FROM', NULL,
'SOLD_FROM', NULL,
hps.party_site_id) party_site_id,
hp.party_id,
hp.party_name
FROM hz_cust_accounts hca,
hz_parties hp,
hz_cust_site_uses hcsu, --hz_cust_site_uses_all hcsu,
hz_cust_acct_sites hcas, --hz_cust_acct_sites_all hcas,
hz_party_sites hps
WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hcsu.status = 'A'
AND hps.status = 'A'
AND hp.status = 'A'
AND hcsu.primary_flag = 'Y'
AND hcas.cust_account_id = hca.cust_account_id
AND hp.party_id = hca.party_id
AND hcsu.location = pc_location
AND hcsu.site_use_code = pc_site_use;
SELECT hca.cust_account_id,
decode('SHIP_FROM', 'SHIP_FROM',hcas.cust_acct_site_id,
'SOLD_FROM',hcas.cust_acct_site_id,
'BILL_TO', hcsu.site_use_id,
'SHIP_TO', hcsu.site_use_id,
'END_CUST', hcsu.site_use_id ) site_id,
decode('SHIP_FROM','SHIP_FROM', NULL,
'SOLD_FROM', NULL,
hps.party_site_id) party_site_id,
hp.party_id,
hp.party_name
FROM hz_cust_accounts hca,
hz_parties hp,
hz_cust_site_uses hcsu, --hz_cust_site_uses_all hcsu,
hz_cust_acct_sites hcas, --hz_cust_acct_sites_all hcas,
hz_party_sites hps
WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hcsu.status = 'A'
AND hps.status = 'A'
AND hp.status = 'A'
AND hcas.cust_account_id = hca.cust_account_id
AND hp.party_id = hca.party_id
AND hcsu.location = pc_location
AND hcsu.site_use_code = pc_site_use;
SELECT hca.cust_account_id,
decode(p_party_type, 'SHIP_FROM',hcas.cust_acct_site_id,
'SOLD_FROM',hcas.cust_acct_site_id,
hcsu.site_use_id) site_id,
decode(p_party_type,'SHIP_FROM', NULL,
'SOLD_FROM', NULL,
hps.party_site_id) party_site_id,
hp.party_id
FROM hz_cust_accounts hca,
hz_parties hp,
hz_cust_site_uses hcsu, --hz_cust_site_uses_all hcsu,
hz_cust_acct_sites hcas, --hz_cust_acct_sites_all hcas,
hz_party_sites hps,
hz_locations hl
WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hcsu.status = 'A'
AND hps.location_id = hl.location_id
AND hcas.cust_account_id = hca.cust_account_id
AND hp.party_id = hca.party_id
AND hp.party_id = pc_party_id
AND hps.party_site_id = pc_party_site_id
AND hcsu.site_use_code = pc_site_use_type;
SELECT party_name
FROM hz_parties
WHERE party_id = cv_party_id;
SELECT NULL
, use.party_site_use_id
, site.party_site_id
, party.party_id
, party.party_name
FROM hz_parties party
, hz_party_sites site
, hz_locations loc
, hz_party_site_uses use
WHERE site.location_id = loc.location_id
AND party.party_id = site.party_id
AND party.status = 'A'
AND party.party_type = 'ORGANIZATION'
AND site.party_site_id = use.party_site_id
AND use.site_use_type = cv_site_use_type
AND site.party_site_number = cv_site_number;
insert_resale_log
(p_id_value => p_resale_line_int_id(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_CLAIM_CUST_NOT_IN_DB',
p_column_name => p_party_type||'_CUST_ACCOUNT_ID',
p_column_value => px_cust_account_id(i),
x_return_status => x_return_status);
insert_resale_log
(p_id_value => p_resale_line_int_id(i),
p_id_type => 'IFACE',
p_error_code => 'OZF_CLAIM_'||p_party_type||'_ST_WRNG',
p_column_name => p_party_type||'_SITE_USE_ID',
p_column_value => px_site_use_id(i),
x_return_status => x_return_status);
insert_resale_log
(p_id_value => p_resale_line_int_id(i),
p_id_type => 'IFACE',
p_error_code => l_dispute_code,
p_column_name => NULL,
p_column_value => NULL,
x_return_status => x_return_status);
SELECT party_id
FROM ( SELECT hzmp.party_id,
score
FROM hz_matched_parties_gt hzmp,
hz_parties hzp
WHERE search_context_id = pc_search_id
AND hzp.party_id = hzmp.party_id
ORDER BY score desc, hzp.creation_date desc )
WHERE rownum = 1;
SELECT hzmps.party_id,
hzmps.party_site_id,
score ,
hzps.creation_date
FROM hz_matched_party_sites_gt hzmps,
hz_party_sites hzps
WHERE search_context_id = pc_search_id
AND hzps.party_site_id = hzmps.party_site_id
AND hzps.party_id = hzmps.party_id
ORDER BY score desc, hzps.creation_date desc;
SELECT hzmc.party_id,
hzmc.org_contact_id,
score
FROM hz_matched_contacts_gt hzmc
WHERE search_context_id = pc_search_id;
SELECT match_rule_id
FROM hz_match_rules_vl
WHERE active_flag = 'Y'
AND compilation_flag = 'C'
AND rule_name = pc_rule_name;
SELECT dbms_utility.get_hash_value( p_external_code_tbl(i),1, 2048)
INTO idx
FROM DUAL;
SELECT s.cust_account_id
FROM hz_cust_acct_sites s
WHERE s.party_site_id = pc_party_site_id
AND status = 'A';
SELECT a.cust_account_id
FROM hz_cust_accounts a
WHERE a.party_id = pc_party_id
AND status = 'A';
SELECT hcas.cust_account_id
, hps.party_site_id
, hp.party_id
, hp.party_name
FROM hz_cust_acct_sites hcas,
hz_cust_site_uses hcsu,
hz_party_sites hps,
hz_parties hp
WHERE hp.party_id = hcas.cust_account_id
AND hp.party_id = hps.party_id
AND hps.party_site_id = hcas.party_site_id
AND hcas.cust_account_id = cv_cust_account_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcsu.primary_flag = 'Y'
AND hcsu.site_use_code = 'BILL_TO';
SELECT hp.party_id
, hp.party_name
FROM hz_cust_accounts hca,
hz_parties hp
WHERE hp.party_id = hca.party_id
AND hca.cust_account_id = cv_cust_account_id;
SELECT hcas.cust_account_id
, hps.party_site_id
, hp.party_id
, hp.party_name
FROM hz_cust_acct_sites hcas,
hz_cust_site_uses hcsu,
hz_party_sites hps,
hz_parties hp
WHERE hp.party_id = hcas.cust_account_id
AND hp.party_id = hps.party_id
AND hps.party_site_id = hcas.party_site_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcas.party_site_id = cv_party_site_id
AND hcsu.site_use_code = 'BILL_TO';
SELECT hcas.cust_account_id
, hcsu.site_use_id
, hps.party_site_id
, hp.party_id
, hp.party_name
FROM hz_cust_acct_sites hcas,
hz_cust_site_uses hcsu,
hz_party_sites hps,
hz_parties hp
WHERE hp.party_id = hcas.cust_account_id
AND hp.party_id = hps.party_id
AND hps.party_site_id = hcas.party_site_id
AND hcas.cust_account_id = cv_cust_account_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcsu.primary_flag = 'Y'
AND hcsu.site_use_code = 'BILL_TO';
SELECT hcas.cust_account_id
, hcsu.site_use_id
, hps.party_site_id
, hp.party_id
, hp.party_name
FROM hz_cust_acct_sites hcas,
hz_cust_site_uses hcsu,
hz_party_sites hps,
hz_parties hp,
hz_cust_site_uses shcsu
WHERE hp.party_id = hcas.cust_account_id
AND hp.party_id = hps.party_id
AND hps.party_site_id = hcas.party_site_id
AND hcas.cust_account_id = cv_cust_account_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcsu.site_use_code = 'BILL_TO'
AND shcsu.bill_to_site_use_id = hcsu.site_use_id
AND shcsu.site_use_code = 'SHIP_TO'
AND shcsu.site_use_id = cv_site_use_id;
SELECT hcas.cust_account_id
, hcsu.site_use_id
, hps.party_site_id
, hp.party_id
, hp.party_name
FROM hz_cust_acct_sites hcas,
hz_cust_site_uses hcsu,
hz_party_sites hps,
hz_parties hp,
hz_cust_acct_sites shcas,
hz_cust_site_uses shcsu
WHERE hp.party_id = hcas.cust_account_id
AND hp.party_id = hps.party_id
AND hps.party_site_id = hcas.party_site_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcsu.site_use_code = 'BILL_TO'
AND hcas.cust_account_id = shcas.cust_account_id
AND shcas.cust_acct_site_id = shcsu.cust_acct_site_id
AND shcsu.bill_to_site_use_id = hcsu.site_use_id
AND shcsu.site_use_code = 'SHIP_TO'
AND shcsu.site_use_id = cv_site_use_id;
PROCEDURE Update_interface_line
(
p_api_version_number 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_int_line_tbl IN resale_line_int_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_interface_line';
OZF_RESALE_LINES_INT_PKG.Update_Row(
p_resale_line_int_id => p_int_line_tbl(l_count).resale_line_int_id,
p_object_version_number => p_int_line_tbl(l_count).object_version_number,
p_last_update_date => p_int_line_tbl(l_count).last_update_date,
p_last_updated_by => p_int_line_tbl(l_count).last_updated_by,
p_request_id => p_int_line_tbl(l_count).request_id,
p_created_from => p_int_line_tbl(l_count).created_from,
p_last_update_login => p_int_line_tbl(l_count).last_update_login,
p_program_application_id => p_int_line_tbl(l_count).program_application_id,
p_program_update_date => p_int_line_tbl(l_count).program_update_date,
p_program_id => p_int_line_tbl(l_count).program_id,
p_response_type => p_int_line_tbl(l_count).response_type,
p_response_code => p_int_line_tbl(l_count).response_code,
p_reject_reason_code => p_int_line_tbl(l_count).reject_reason_code,
p_followup_action_code => p_int_line_tbl(l_count).followup_action_code,
p_resale_transfer_type => p_int_line_tbl(l_count).resale_transfer_type,
p_product_trans_movement_type => p_int_line_tbl(l_count).product_transfer_movement_type,
p_product_transfer_date => p_int_line_tbl(l_count).product_transfer_date,
p_resale_batch_id => p_int_line_tbl(l_count).resale_batch_id,
p_status_code => p_int_line_tbl(l_count).status_code,
p_end_cust_party_id => p_int_line_tbl(l_count).end_cust_party_id,
p_end_cust_site_use_id => p_int_line_tbl(l_count).end_cust_site_use_id,
p_end_cust_site_use_code => p_int_line_tbl(l_count).end_cust_site_use_code,
p_end_cust_party_site_id => p_int_line_tbl(l_count).end_cust_party_site_id,
p_end_cust_party_name => p_int_line_tbl(l_count).end_cust_party_name,
p_end_cust_location => p_int_line_tbl(l_count).end_cust_location,
p_end_cust_address => p_int_line_tbl(l_count).end_cust_address,
p_end_cust_city => p_int_line_tbl(l_count).end_cust_city,
p_end_cust_state => p_int_line_tbl(l_count).end_cust_state,
p_end_cust_postal_code => p_int_line_tbl(l_count).end_cust_postal_code,
p_end_cust_country => p_int_line_tbl(l_count).end_cust_country,
p_end_cust_contact_party_id => p_int_line_tbl(l_count).end_cust_contact_party_id,
p_end_cust_contact_name => p_int_line_tbl(l_count).end_cust_contact_name,
p_end_cust_email => p_int_line_tbl(l_count).end_cust_email,
p_end_cust_phone => p_int_line_tbl(l_count).end_cust_phone,
p_end_cust_fax => p_int_line_tbl(l_count).end_cust_fax,
p_bill_to_cust_account_id => p_int_line_tbl(l_count).bill_to_cust_account_id,
p_bill_to_site_use_id => p_int_line_tbl(l_count).bill_to_site_use_id,
p_bill_to_PARTY_NAME => p_int_line_tbl(l_count).bill_to_party_name,
p_bill_to_PARTY_ID => p_int_line_tbl(l_count).bill_to_PARTY_ID,
p_bill_to_PARTY_site_id => p_int_line_tbl(l_count).bill_to_PARTY_site_id,
p_bill_to_location => p_int_line_tbl(l_count).bill_to_location,
p_bill_to_duns_number => p_int_line_tbl(l_count).bill_to_duns_number,
p_bill_to_address => p_int_line_tbl(l_count).bill_to_address,
p_bill_to_city => p_int_line_tbl(l_count).bill_to_city,
p_bill_to_state => p_int_line_tbl(l_count).bill_to_state,
p_bill_to_postal_code => p_int_line_tbl(l_count).bill_to_postal_code,
p_bill_to_country => p_int_line_tbl(l_count).bill_to_country,
p_bill_to_contact_party_id => p_int_line_tbl(l_count).bill_to_contact_party_id,
p_bill_to_contact_name => p_int_line_tbl(l_count).bill_to_contact_name,
p_bill_to_email => p_int_line_tbl(l_count).bill_to_email,
p_bill_to_phone => p_int_line_tbl(l_count).bill_to_phone,
p_bill_to_fax => p_int_line_tbl(l_count).bill_to_fax,
p_ship_to_cust_account_id => p_int_line_tbl(l_count).ship_to_cust_account_id,
p_ship_to_site_use_id => p_int_line_tbl(l_count).ship_to_site_use_id,
p_ship_to_party_name => p_int_line_tbl(l_count).ship_to_party_name,
p_ship_to_party_id => p_int_line_tbl(l_count).ship_to_party_id,
p_ship_to_party_site_id => p_int_line_tbl(l_count).ship_to_party_site_id,
p_ship_to_duns_number => p_int_line_tbl(l_count).ship_to_duns_number,
p_ship_to_location => p_int_line_tbl(l_count).ship_to_location,
p_ship_to_address => p_int_line_tbl(l_count).ship_to_address,
p_ship_to_city => p_int_line_tbl(l_count).ship_to_city,
p_ship_to_state => p_int_line_tbl(l_count).ship_to_state,
p_ship_to_postal_code => p_int_line_tbl(l_count).ship_to_postal_code,
p_ship_to_country => p_int_line_tbl(l_count).ship_to_country,
p_ship_to_contact_party_id => p_int_line_tbl(l_count).ship_to_contact_party_id,
p_ship_to_contact_name => p_int_line_tbl(l_count).ship_to_contact_name,
p_ship_to_email => p_int_line_tbl(l_count).ship_to_email,
p_ship_to_phone => p_int_line_tbl(l_count).ship_to_phone,
p_ship_to_fax => p_int_line_tbl(l_count).ship_to_fax,
p_ship_from_cust_account_id => p_int_line_tbl(l_count).ship_from_cust_account_id,
p_ship_from_site_id => p_int_line_tbl(l_count).ship_from_site_id,
p_ship_from_party_name => p_int_line_tbl(l_count).ship_from_party_name,
p_ship_from_location => p_int_line_tbl(l_count).ship_from_location,
p_ship_from_address => p_int_line_tbl(l_count).ship_from_address,
p_ship_from_city => p_int_line_tbl(l_count).ship_from_city,
p_ship_from_state => p_int_line_tbl(l_count).ship_from_state,
p_ship_from_postal_code => p_int_line_tbl(l_count).ship_from_postal_code,
p_ship_from_country => p_int_line_tbl(l_count).ship_from_country,
p_ship_from_contact_party_id => p_int_line_tbl(l_count).ship_from_contact_party_id,
p_ship_from_contact_name => p_int_line_tbl(l_count).ship_from_contact_name,
p_ship_from_email => p_int_line_tbl(l_count).ship_from_email,
p_ship_from_phone => p_int_line_tbl(l_count).ship_from_phone,
p_ship_from_fax => p_int_line_tbl(l_count).ship_from_fax,
p_sold_from_cust_account_id => p_int_line_tbl(l_count).sold_from_cust_account_id,
p_sold_from_site_id => p_int_line_tbl(l_count).sold_from_site_id,
p_sold_from_party_name => p_int_line_tbl(l_count).sold_from_party_name,
p_sold_from_location => p_int_line_tbl(l_count).sold_from_location,
p_sold_from_address => p_int_line_tbl(l_count).sold_from_address,
p_sold_from_city => p_int_line_tbl(l_count).sold_from_city,
p_sold_from_state => p_int_line_tbl(l_count).sold_from_state,
p_sold_from_postal_code => p_int_line_tbl(l_count).sold_from_postal_code,
p_sold_from_country => p_int_line_tbl(l_count).sold_from_country,
p_sold_from_contact_party_id => p_int_line_tbl(l_count).sold_from_contact_party_id,
p_sold_from_contact_name => p_int_line_tbl(l_count).sold_from_contact_name,
p_sold_from_email => p_int_line_tbl(l_count).sold_from_email,
p_sold_from_phone => p_int_line_tbl(l_count).sold_from_phone,
p_sold_from_fax => p_int_line_tbl(l_count).sold_from_fax,
p_order_number => p_int_line_tbl(l_count).order_number,
p_date_ordered => p_int_line_tbl(l_count).date_ordered,
p_po_number => p_int_line_tbl(l_count).po_number,
p_po_release_number => p_int_line_tbl(l_count).po_release_number,
p_po_type => p_int_line_tbl(l_count).po_type,
p_agreement_id => p_int_line_tbl(l_count).agreement_id,
p_agreement_name => p_int_line_tbl(l_count).agreement_name,
p_agreement_type => p_int_line_tbl(l_count).agreement_type,
p_agreement_price => p_int_line_tbl(l_count).agreement_price,
p_agreement_uom_code => p_int_line_tbl(l_count).agreement_uom_code,
p_corrected_agreement_id => p_int_line_tbl(l_count).corrected_agreement_id,
p_corrected_agreement_name => p_int_line_tbl(l_count).corrected_agreement_name,
p_price_list_id => p_int_line_tbl(l_count).price_list_id,
p_price_list_name => p_int_line_tbl(l_count).price_list_name,
p_orig_system_quantity => p_int_line_tbl(l_count).orig_system_quantity,
p_orig_system_uom => p_int_line_tbl(l_count).orig_system_uom,
p_orig_system_currency_code => p_int_line_tbl(l_count).orig_system_currency_code,
p_orig_system_selling_price => p_int_line_tbl(l_count).orig_system_selling_price,
p_orig_system_reference => p_int_line_tbl(l_count).orig_system_reference,
p_orig_system_line_reference => p_int_line_tbl(l_count).orig_system_line_reference,
p_orig_system_purchase_uom => p_int_line_tbl(l_count).orig_system_purchase_uom,
p_orig_system_purchase_curr => p_int_line_tbl(l_count).orig_system_purchase_curr,
p_orig_system_purchase_price => p_int_line_tbl(l_count).orig_system_purchase_price,
p_orig_system_purchase_quant => p_int_line_tbl(l_count).orig_system_purchase_quantity,
p_orig_system_agreement_uom => p_int_line_tbl(l_count).orig_system_agreement_uom,
p_orig_system_agreement_name => p_int_line_tbl(l_count).orig_system_agreement_name,
p_orig_system_agreement_type => p_int_line_tbl(l_count).orig_system_agreement_type,
p_orig_system_agreement_status => p_int_line_tbl(l_count).orig_system_agreement_status,
p_orig_system_agreement_curr => p_int_line_tbl(l_count).orig_system_agreement_curr,
p_orig_system_agreement_price => p_int_line_tbl(l_count).orig_system_agreement_price,
p_orig_system_agreement_quant => p_int_line_tbl(l_count).orig_system_agreement_quantity,
p_orig_system_item_number => p_int_line_tbl(l_count).orig_system_item_number,
p_quantity => p_int_line_tbl(l_count).quantity,
p_uom_code => p_int_line_tbl(l_count).uom_code,
p_currency_code => p_int_line_tbl(l_count).currency_code,
p_exchange_rate => p_int_line_tbl(l_count).exchange_rate,
p_exchange_rate_type => p_int_line_tbl(l_count).exchange_rate_type,
p_exchange_rate_date => p_int_line_tbl(l_count).exchange_rate_date,
p_selling_price => p_int_line_tbl(l_count).selling_price,
p_purchase_uom_code => p_int_line_tbl(l_count).purchase_uom_code,
p_invoice_number => p_int_line_tbl(l_count).invoice_number,
p_date_invoiced => p_int_line_tbl(l_count).date_invoiced,
p_date_shipped => p_int_line_tbl(l_count).date_shipped,
p_credit_advice_date => p_int_line_tbl(l_count).credit_advice_date,
p_product_category_id => p_int_line_tbl(l_count).product_category_id,
p_category_name => p_int_line_tbl(l_count).category_name,
p_inventory_item_segment1 => p_int_line_tbl(l_count).inventory_item_segment1,
p_inventory_item_segment2 => p_int_line_tbl(l_count).inventory_item_segment2,
p_inventory_item_segment3 => p_int_line_tbl(l_count).inventory_item_segment3,
p_inventory_item_segment4 => p_int_line_tbl(l_count).inventory_item_segment4,
p_inventory_item_segment5 => p_int_line_tbl(l_count).inventory_item_segment5,
p_inventory_item_segment6 => p_int_line_tbl(l_count).inventory_item_segment6,
p_inventory_item_segment7 => p_int_line_tbl(l_count).inventory_item_segment7,
p_inventory_item_segment8 => p_int_line_tbl(l_count).inventory_item_segment8,
p_inventory_item_segment9 => p_int_line_tbl(l_count).inventory_item_segment9,
p_inventory_item_segment10 => p_int_line_tbl(l_count).inventory_item_segment10,
p_inventory_item_segment11 => p_int_line_tbl(l_count).inventory_item_segment11,
p_inventory_item_segment12 => p_int_line_tbl(l_count).inventory_item_segment12,
p_inventory_item_segment13 => p_int_line_tbl(l_count).inventory_item_segment13,
p_inventory_item_segment14 => p_int_line_tbl(l_count).inventory_item_segment14,
p_inventory_item_segment15 => p_int_line_tbl(l_count).inventory_item_segment15,
p_inventory_item_segment16 => p_int_line_tbl(l_count).inventory_item_segment16,
p_inventory_item_segment17 => p_int_line_tbl(l_count).inventory_item_segment17,
p_inventory_item_segment18 => p_int_line_tbl(l_count).inventory_item_segment18,
p_inventory_item_segment19 => p_int_line_tbl(l_count).inventory_item_segment19,
p_inventory_item_segment20 => p_int_line_tbl(l_count).inventory_item_segment20,
p_inventory_item_id => p_int_line_tbl(l_count).inventory_item_id,
p_item_description => p_int_line_tbl(l_count).item_description,
p_upc_code => p_int_line_tbl(l_count).upc_code,
p_item_number => p_int_line_tbl(l_count).item_number,
p_claimed_amount => p_int_line_tbl(l_count).claimed_amount,
p_purchase_price => p_int_line_tbl(l_count).purchase_price,
p_acctd_purchase_price => p_int_line_tbl(l_count).acctd_purchase_price,
p_net_adjusted_amount => p_int_line_tbl(l_count).net_adjusted_amount,
p_accepted_amount => p_int_line_tbl(l_count).accepted_amount,
p_total_accepted_amount => p_int_line_tbl(l_count).total_accepted_amount,
p_allowed_amount => p_int_line_tbl(l_count).allowed_amount,
p_total_allowed_amount => p_int_line_tbl(l_count).total_allowed_amount,
p_calculated_price => p_int_line_tbl(l_count).calculated_price,
p_acctd_calculated_price => p_int_line_tbl(l_count).acctd_calculated_price,
p_calculated_amount => p_int_line_tbl(l_count).calculated_amount,
p_line_tolerance_amount => p_int_line_tbl(l_count).line_tolerance_amount,
p_total_claimed_amount => p_int_line_tbl(l_count).total_claimed_amount,
p_credit_code => p_int_line_tbl(l_count).credit_code,
p_direct_customer_flag => p_int_line_tbl(l_count).direct_customer_flag,
p_duplicated_line_id => p_int_line_tbl(l_count).duplicated_line_id,
p_duplicated_adjustment_id => p_int_line_tbl(l_count).duplicated_adjustment_id,
p_order_type_id => p_int_line_tbl(l_count).order_type_id,
p_order_type => p_int_line_tbl(l_count).order_type,
p_order_category => p_int_line_tbl(l_count).order_category,
p_dispute_code => p_int_line_tbl(l_count).dispute_code,
p_data_source_code => p_int_line_tbl(l_count).data_source_code,
p_tracing_flag => p_int_line_tbl(l_count).tracing_flag,
p_header_attribute_category => p_int_line_tbl(l_count).header_attribute_category,
p_header_attribute1 => p_int_line_tbl(l_count).header_attribute1,
p_header_attribute2 => p_int_line_tbl(l_count).header_attribute2,
p_header_attribute3 => p_int_line_tbl(l_count).header_attribute3,
p_header_attribute4 => p_int_line_tbl(l_count).header_attribute4,
p_header_attribute5 => p_int_line_tbl(l_count).header_attribute5,
p_header_attribute6 => p_int_line_tbl(l_count).header_attribute6,
p_header_attribute7 => p_int_line_tbl(l_count).header_attribute7,
p_header_attribute8 => p_int_line_tbl(l_count).header_attribute8,
p_header_attribute9 => p_int_line_tbl(l_count).header_attribute9,
p_header_attribute10 => p_int_line_tbl(l_count).header_attribute10,
p_header_attribute11 => p_int_line_tbl(l_count).header_attribute11,
p_header_attribute12 => p_int_line_tbl(l_count).header_attribute12,
p_header_attribute13 => p_int_line_tbl(l_count).header_attribute13,
p_header_attribute14 => p_int_line_tbl(l_count).header_attribute14,
p_header_attribute15 => p_int_line_tbl(l_count).header_attribute15,
p_line_attribute_category => p_int_line_tbl(l_count).line_attribute_category,
p_line_attribute1 => p_int_line_tbl(l_count).line_attribute1,
p_line_attribute2 => p_int_line_tbl(l_count).line_attribute2,
p_line_attribute3 => p_int_line_tbl(l_count).line_attribute3,
p_line_attribute4 => p_int_line_tbl(l_count).line_attribute4,
p_line_attribute5 => p_int_line_tbl(l_count).line_attribute5,
p_line_attribute6 => p_int_line_tbl(l_count).line_attribute6,
p_line_attribute7 => p_int_line_tbl(l_count).line_attribute7,
p_line_attribute8 => p_int_line_tbl(l_count).line_attribute8,
p_line_attribute9 => p_int_line_tbl(l_count).line_attribute9,
p_line_attribute10 => p_int_line_tbl(l_count).line_attribute10,
p_line_attribute11 => p_int_line_tbl(l_count).line_attribute11,
p_line_attribute12 => p_int_line_tbl(l_count).line_attribute12,
p_line_attribute13 => p_int_line_tbl(l_count).line_attribute13,
p_line_attribute14 => p_int_line_tbl(l_count).line_attribute14,
p_line_attribute15 => p_int_line_tbl(l_count).line_attribute15,
p_org_id => p_int_line_tbl(l_count).org_id );
END Update_interface_line;
procedure Update_interface_batch (
p_api_version_number 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_int_batch_rec IN ozf_resale_batches_all%rowtype,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_interface_batch';
OZF_RESALE_BATCHES_PKG.Update_Row(
p_resale_batch_id => p_int_batch_rec.resale_batch_id,
p_object_version_number => p_int_batch_rec.object_version_number ,
p_last_update_date => p_int_batch_rec.last_update_date,
p_last_updated_by => p_int_batch_rec.last_updated_by,
p_request_id => p_int_batch_rec.request_id,
p_created_from => p_int_batch_rec.created_from,
p_last_update_login => p_int_batch_rec.last_update_login,
p_program_application_id => p_int_batch_rec.program_application_id,
p_program_update_date => p_int_batch_rec.program_update_date,
p_program_id => p_int_batch_rec.program_id,
p_batch_number => p_int_batch_rec.batch_number,
p_batch_type => p_int_batch_rec.batch_type,
p_batch_count => p_int_batch_rec.batch_count,
p_year => p_int_batch_rec.year,
p_month => p_int_batch_rec.month,
p_report_date => p_int_batch_rec.report_date,
p_report_start_date => p_int_batch_rec.report_start_date,
p_report_end_date => p_int_batch_rec.report_end_date,
p_status_code => p_int_batch_rec.status_code,
-- p_date_source_code => p_int_batch_rec.data_source_code, -- BUG 5077213
p_reference_type => p_int_batch_rec.reference_type,
p_reference_number => p_int_batch_rec.reference_number,
p_comments => p_int_batch_rec.comments,
p_partner_claim_number => p_int_batch_rec.partner_claim_number,
p_transaction_purpose_code => p_int_batch_rec.transaction_purpose_code,
p_transaction_type_code => p_int_batch_rec.transaction_type_code,
p_partner_type => p_int_batch_rec.partner_type,
p_partner_id => p_int_batch_rec.partner_id,
p_partner_party_id => p_int_batch_rec.partner_party_id,
p_partner_cust_account_id => p_int_batch_rec.partner_cust_account_id,
p_partner_site_id => p_int_batch_rec.partner_site_id,
p_partner_contact_party_id => p_int_batch_rec.partner_contact_party_id,
p_partner_contact_name => p_int_batch_rec.partner_contact_name,
p_partner_email => p_int_batch_rec.partner_email,
p_partner_phone => p_int_batch_rec.partner_phone,
p_partner_fax => p_int_batch_rec.partner_fax,
p_header_tolerance_operand => p_int_batch_rec.header_tolerance_operand,
p_header_tolerance_calc_code => p_int_batch_rec.header_tolerance_calc_code,
p_line_tolerance_operand => p_int_batch_rec.line_tolerance_operand,
p_line_tolerance_calc_code => p_int_batch_rec.line_tolerance_calc_code,
p_currency_code => p_int_batch_rec.currency_code,
p_claimed_amount => p_int_batch_rec.claimed_amount,
p_allowed_amount => p_int_batch_rec.allowed_amount,
p_paid_amount => p_int_batch_rec.paid_amount,
p_disputed_amount => p_int_batch_rec.disputed_amount,
p_accepted_amount => p_int_batch_rec.accepted_amount,
p_lines_invalid => p_int_batch_rec.lines_invalid,
p_lines_w_tolerance => p_int_batch_rec.lines_w_tolerance,
p_lines_disputed => p_int_batch_rec.lines_disputed,
p_batch_set_id_code => p_int_batch_rec.batch_set_id_code,
p_credit_code => p_int_batch_rec.credit_code,
p_credit_advice_date => p_int_batch_rec.credit_advice_date,
p_purge_flag => p_int_batch_rec.purge_flag,
p_attribute_category => p_int_batch_rec.attribute_category,
p_attribute1 => p_int_batch_rec.attribute1,
p_attribute2 => p_int_batch_rec.attribute2,
p_attribute3 => p_int_batch_rec.attribute3,
p_attribute4 => p_int_batch_rec.attribute4,
p_attribute5 => p_int_batch_rec.attribute5,
p_attribute6 => p_int_batch_rec.attribute6,
p_attribute7 => p_int_batch_rec.attribute7,
p_attribute8 => p_int_batch_rec.attribute8,
p_attribute9 => p_int_batch_rec.attribute9,
p_attribute10 => p_int_batch_rec.attribute10,
p_attribute11 => p_int_batch_rec.attribute11,
p_attribute12 => p_int_batch_rec.attribute12,
p_attribute13 => p_int_batch_rec.attribute13,
p_attribute14 => p_int_batch_rec.attribute14,
p_attribute15 => p_int_batch_rec.attribute15,
p_org_id => p_int_batch_rec.org_id );
END update_interface_batch;
SELECT etd.confirmation, eth.company_admin_email
FROM ecx_tp_headers_v eth
, ecx_tp_details_v etd
WHERE eth.tp_header_id = etd.tp_header_id
AND eth.party_id = pc_party_id
AND eth.party_site_id = pc_site_id
AND etd.transaction_type = pc_transaction_type
AND etd.transaction_subtype = pc_txn_sub_type;
SELECT partner_party_id
, partner_site_id
FROM ozf_resale_batches
WHERE resale_batch_id = pc_batch_id;
SELECT partner_party_id
, partner_site_id
FROM ozf_resale_batches
WHERE resale_batch_id = pc_batch_id;
PROCEDURE Insert_Resale_Log (
p_id_value IN VARCHAR2,
p_id_type IN VARCHAR2,
p_error_code IN VARCHAR2,
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';
SELECT ozf_resale_logs_all_s.nextval into l_log_id from dual;
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 => 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;