[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, detail_deleted VARCHAR2(30)
, detail_date_from HXC_TIME_BUILDING_BLOCKS.date_from%TYPE
, detail_date_to HXC_TIME_BUILDING_BLOCKS.date_to%TYPE
, resource_id HXC_TIME_BUILDING_BLOCKS.resource_id%TYPE
, po_number PO_HEADERS_ALL.segment1%TYPE
, po_header_id PO_HEADERS_ALL.po_header_id%TYPE
, po_line PO_LINES_ALL.line_num%TYPE
, po_line_id PO_LINES_ALL.po_line_id%TYPE
, po_line_location_id PO_LINE_LOCATIONS_ALL.line_location_id%TYPE
, po_distribution_id PO_DISTRIBUTIONS_ALL.po_distribution_id%TYPE
, project_id PO_DISTRIBUTIONS_ALL.project_id%TYPE
, task_id PO_DISTRIBUTIONS_ALL.task_id%TYPE
, po_price_type PO_TEMP_LABOR_RATES_V.asg_rate_type%TYPE
, po_price_type_display PO_TEMP_LABOR_RATES_V.price_type_dsp%TYPE
, po_billable_amount PO_LINES_ALL.amount%TYPE
, po_receipt_date RCV_TRANSACTIONS.transaction_date%TYPE
, lpn_group_id RCV_TRANSACTIONS.lpn_group_id%TYPE
-- save the transaction type so we know how to check for success
, transaction_type VARCHAR2(240)
-- we need to reference two rti rows for corrections
, receive_rti_id RCV_TRANSACTIONS_INTERFACE.interface_transaction_id%TYPE
, deliver_rti_id RCV_TRANSACTIONS_INTERFACE.interface_transaction_id%TYPE
-- we need to reference four rti rows for delete+insert
, delete_receive_rti_id RCV_TRANSACTIONS_INTERFACE.interface_transaction_id%TYPE
, delete_deliver_rti_id RCV_TRANSACTIONS_INTERFACE.interface_transaction_id%TYPE
-- parent txns exist when we have received against this po line before
, parent_receive_txn_id RCV_TRANSACTIONS.transaction_id%TYPE
, parent_deliver_txn_id RCV_TRANSACTIONS.transaction_id%TYPE
-- org_id of the PO/CWK
, org_id PO_HEADERS_ALL.org_id%TYPE
-- save the purchasing category attribute_id to attach to error messages
, time_attribute_id HXC_TIME_ATTRIBUTES.time_attribute_id%TYPE
-- transient variable to save the validation status
, validation_status VARCHAR2(30)
-- is this the old version of a changed block
, old_block VARCHAR2(1)
);
g_update_start DATE;
g_update_stop DATE;
SELECT po_line_id
, timecard_id
, interface_transaction_id
FROM rcv_transactions
WHERE group_id = v_group_id;
PO_DEBUG.debug_var(l_log_head,l_progress,l_new_old ||'-detail_deleted ' , p_attributes.detail_deleted);
SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL
INTO g_group_id
FROM dual;
SELECT poh.po_header_id
, poh.segment1
, NVL (poh.user_hold_flag, 'N')
, poh.org_id
, poh.vendor_id
, poh.vendor_site_id
INTO g_po_header_cache(p_po_header_id).po_header_id
, g_po_header_cache(p_po_header_id).segment1
, g_po_header_cache(p_po_header_id).user_hold_flag
, g_po_header_cache(p_po_header_id).org_id
, g_po_header_cache(p_po_header_id).vendor_id
, g_po_header_cache(p_po_header_id).vendor_site_id
FROM po_headers_all poh
WHERE poh.po_header_id = p_po_header_id;
SELECT pol.po_line_id
, pol.po_header_id
, pol.line_num
, pol.unit_price
, pol.matching_basis
, pol.purchase_basis
, pol.order_type_lookup_code
, NVL (pol.start_date, HR_GENERAL.start_of_time)
, NVL (pol.expiration_date, HR_GENERAL.end_of_time)
, pol.job_id
, poll.line_location_id
, NVL (poll.approved_flag, 'N')
, NVL (poll.cancel_flag, 'N')
, NVL (poll.closed_code, 'OPEN')
, NVL (poll.qty_rcv_exception_code, 'NONE')
, poll.amount + ( poll.amount * NVL (poll.qty_rcv_tolerance, 0) / 100 )
, 0
, poll.ship_to_organization_id
, poll.ship_to_location_id
INTO g_po_line_cache(p_po_line_id).po_line_id
, g_po_line_cache(p_po_line_id).po_header_id
, g_po_line_cache(p_po_line_id).line_num
, g_po_line_cache(p_po_line_id).unit_price
, g_po_line_cache(p_po_line_id).matching_basis
, g_po_line_cache(p_po_line_id).purchase_basis
, g_po_line_cache(p_po_line_id).order_type_lookup_code
, g_po_line_cache(p_po_line_id).start_date
, g_po_line_cache(p_po_line_id).expiration_date
, g_po_line_cache(p_po_line_id).job_id
, g_po_line_cache(p_po_line_id).line_location_id
, g_po_line_cache(p_po_line_id).approved_flag
, g_po_line_cache(p_po_line_id).cancel_flag
, g_po_line_cache(p_po_line_id).closed_code
, g_po_line_cache(p_po_line_id).qty_rcv_exception_code
, g_po_line_cache(p_po_line_id).tolerable_amount
, g_po_line_cache(p_po_line_id).timecard_amount
, g_po_line_cache(p_po_line_id).ship_to_organization_id
, g_po_line_cache(p_po_line_id).ship_to_location_id
FROM po_lines_all pol
, po_line_locations_all poll
WHERE pol.po_line_id = p_po_line_id
AND poll.po_line_id = pol.po_line_id;
SELECT MIN(pod.po_distribution_id)
INTO g_po_distribution_cache(p_po_line_id).po_distribution_id
FROM po_distributions_all pod
WHERE pod.po_line_id = p_po_line_id
AND pod.project_id = p_project_id
AND pod.task_id = p_task_id;
-- This is used in the case when user select a Project on the Timecard which doesn't matches
-- with the projects in Purchase Order which was selected on Time card.
IF g_po_distribution_cache(p_po_line_id).po_distribution_id IS NULL THEN
SELECT MIN(psp.po_distribution_id)
INTO g_po_distribution_cache(p_po_line_id).po_distribution_id
FROM PO_SP_VAL_V psp
WHERE psp.po_line_id = p_po_line_id
AND psp.project_id IS NOT NULL
AND psp.task_id IS NOT NULL
AND psp.VALIDATE_PROJECT_FLAG = 'Y';
SELECT p_price_type
, meaning
INTO g_price_type_lookup_cache(l_cache_index).lookup_code
, g_price_type_lookup_cache(l_cache_index).meaning
FROM fnd_lookups
WHERE lookup_type = 'PRICE DIFFERENTIALS'
AND lookup_code = p_price_type;
g_price_type_lookup_cache.DELETE(l_cache_index);
SELECT entity_id
, price_type
, enabled_flag
, multiplier
INTO g_price_differentials_cache(l_cache_index).entity_id
, g_price_differentials_cache(l_cache_index).price_type
, g_price_differentials_cache(l_cache_index).enabled_flag
, g_price_differentials_cache(l_cache_index).multiplier
FROM po_price_differentials
WHERE entity_type = 'PO LINE'
AND entity_id = p_po_line_id
AND price_type = p_price_type;
g_price_differentials_cache.DELETE(l_cache_index);
l_sql :=' SELECT effective_start_date , effective_end_date
FROM per_all_assignments_f paaf
WHERE paaf.po_line_id = :po_line_id
AND paaf.person_id = :person_id
AND Trunc(:effective_date)
BETWEEN Trunc(paaf.effective_start_date)
AND Trunc(paaf.effective_end_date)
UNION
SELECT effective_start_date , effective_end_date
FROM per_all_assignments_f paaf
, po_cwk_associations pca
, po_headers_all ph
, po_lines_all pl
WHERE pca.po_line_id = :po_line_id
AND pca.cwk_person_id = :person_id
AND pca.po_line_id = pl.po_line_id
AND pca.po_header_id = ph.po_header_id
AND pca.cwk_person_id = paaf.person_id
AND paaf.job_id = pl.job_id
AND ph.vendor_id = paaf.vendor_id
AND ph.vendor_site_id = paaf.vendor_site_id
AND Trunc(:effective_date)
BETWEEN Trunc(paaf.effective_start_date)
AND Trunc(paaf.effective_end_date) ';
SELECT receive.transaction_id
INTO g_rcv_transactions_cache(p_timecard_bb_id).receive_transaction_id
FROM rcv_transactions receive
WHERE receive.timecard_id = p_timecard_bb_id
AND receive.po_line_id = p_po_line_id
AND receive.transaction_type = 'RECEIVE';
SELECT deliver.transaction_id
INTO g_rcv_transactions_cache(p_timecard_bb_id).deliver_transaction_id
FROM rcv_transactions deliver
WHERE deliver.timecard_id = p_timecard_bb_id
AND deliver.po_line_id = p_po_line_id
AND deliver.transaction_type = 'DELIVER';
SELECT receive.transaction_id
INTO g_rcv_transactions_cache(p_timecard_bb_id).receive_transaction_id
FROM rcv_transactions receive
WHERE receive.timecard_id = p_timecard_bb_id
AND receive.po_distribution_id = p_po_distribution_id
AND receive.project_id = p_project_id /* Bug 14609848 */
AND receive.task_id = p_task_id /* Bug 14609848 */
AND receive.transaction_type = 'RECEIVE';
SELECT deliver.transaction_id
INTO g_rcv_transactions_cache(p_timecard_bb_id).deliver_transaction_id
FROM rcv_transactions deliver
WHERE deliver.timecard_id = p_timecard_bb_id
AND deliver.po_distribution_id = p_po_distribution_id
AND deliver.project_id = p_project_id /* Bug 14609848 */
AND deliver.task_id = p_task_id /* Bug 14609848 */
AND deliver.transaction_type = 'DELIVER';
select value INTO nls_num_chars from nls_session_parameters where parameter='NLS_NUMERIC_CHARACTERS';
PROCEDURE Update_Attributes
( p_attributes IN OUT NOCOPY TimecardAttributesRec
, p_messages IN OUT NOCOPY HXC_USER_TYPE_DEFINITION_GRP.message_table
) IS
l_api_name CONSTANT varchar2(30) := 'Update_Attributes';
po_update_flag VARCHAR2(1) :='N'; --bug 6998132
, message => 'Begin Update_Attributes'
);
SELECT 'Y'
INTO po_update_flag
FROM dual
WHERE EXISTS (SELECT segment1
FROM po_headers_all
WHERE segment1=p_attributes.po_number
AND org_id=hxc_timecard_properties.setup_mo_global_params(fnd_global.employee_id)
AND Nvl(closed_code,'OPEN') <> 'FINALLY CLOSED'
AND Nvl(user_hold_flag,'N') <> 'Y'
);
, message => 'End Update_Attributes'
);
END Update_Attributes;
, p_message_name => 'RCV_OTL_UPDATE_INVALID_PO'
, p_message_token => NULL
, p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
, p_message_field => 'PO Header Id'
, p_application_short_name => 'PO'
, p_timecard_bb_id => NULL
, p_time_attribute_id => p_attributes.time_attribute_id
);
, p_message_name => 'RCV_OTL_UPDATE_INVALID_PO'
, p_message_token => NULL
, p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
, p_message_field => 'PO Line Id'
, p_application_short_name => 'PO'
, p_timecard_bb_id => NULL
, p_time_attribute_id => p_attributes.time_attribute_id
);
, p_message_name => 'RCV_OTL_UPDATE_INVALID_PO'
, p_message_token => NULL
, p_message_level => HXC_USER_TYPE_DEFINITION_GRP.c_error
, p_message_field => 'PO Line Id'
, p_application_short_name => 'PO'
, p_timecard_bb_id => NULL
, p_time_attribute_id => p_attributes.time_attribute_id
);
, message => 'Amount for PO Line Id ' || p_attributes.po_line_id || ' updated to ' || get_po_line(p_attributes.po_line_id).timecard_amount
);
SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
INTO p_rti_row.interface_transaction_id
FROM dual;
p_rti_row.last_update_date := SYSDATE;
p_rti_row.last_updated_by := FND_GLOBAL.USER_ID;
p_rti_row.creation_date := p_rti_row.last_update_date;
p_rti_row.created_by := p_rti_row.last_updated_by;
SELECT RCV_HEADERS_INTERFACE_S.NEXTVAL
INTO l_rhi_row.header_interface_id
FROM dual;
l_rhi_row.last_update_date := SYSDATE;
l_rhi_row.last_updated_by := FND_GLOBAL.USER_ID;
l_rhi_row.creation_date := l_rhi_row.last_update_date;
l_rhi_row.created_by := l_rhi_row.last_updated_by;
, message => 'Updated transaction_date=' || l_rti_row.transaction_date
);
SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
INTO l_rcv_rti_row.interface_transaction_id
FROM dual;
SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
INTO l_del_rti_row.interface_transaction_id
FROM dual;
PROCEDURE Derive_Delete_Values
( p_rti_rows IN OUT NOCOPY rti_table
, p_attributes IN OUT NOCOPY TimecardAttributesRec
, receipt_date IN DATE
) IS
l_new_attributes TimecardAttributesRec;
l_api_name CONSTANT varchar2(30) := 'Derive_Delete_Values';
, message => 'Begin Derive_Delete_Values'
);
, message => 'End Derive_Delete_Values'
);
END Derive_Delete_Values;
SELECT rcv_interface_groups_s.NEXTVAL
INTO p_attributes.lpn_group_id
FROM dual;
-- Added a new parameter for receipt date when calling delete on blocks
-- such that we can have the request Transaction date or the system date
-- insteed of using transaction date from OLD records.
Derive_Delete_Values( p_rti_rows
, p_old_attributes
, p_attributes.po_receipt_date
);
p_attributes.delete_receive_rti_id := p_old_attributes.receive_rti_id;
p_attributes.delete_deliver_rti_id := p_old_attributes.deliver_rti_id;
p_attributes.transaction_type := 'DELETE ' || p_attributes.transaction_type;
IF p_attributes.detail_deleted = 'Y' THEN
-- BUG6343206
-- Added a new parameter for receipt date when calling delete on blocks
-- such that we can have the request Transaction date or the system date
-- insteed of using transaction date from OLD records.
Derive_Delete_Values( p_rti_rows
, p_old_attributes
, p_attributes.po_receipt_date
);
p_attributes.delete_receive_rti_id := p_old_attributes.receive_rti_id;
p_attributes.delete_deliver_rti_id := p_old_attributes.deliver_rti_id;
p_attributes.transaction_type := 'DELETE';
IF p_attributes.detail_deleted = 'Y' THEN
l_action := 'DELETE';
l_action := 'UPDATE';
l_action := 'INSERT';
PROCEDURE Insert_Interface_Values
( p_rhi_rows IN OUT NOCOPY rhi_table
, p_rti_rows IN OUT NOCOPY rti_table
-- Bug6343206
-- Reverting the changes done for BUG3550333 [115.69]
-- We are allowing the zero amount receipts to be created as of now.
-- There will be no entry going in as SUCCESS. so we need no track
-- those block by l_rti_status.
-- , p_rti_status IN OUT NOCOPY rti_status_table
) IS
--added for bugfix 5609476
CURSOR c_get_currency_code(v_po_header_id NUMBER) IS
SELECT currency_code
FROM po_headers
where po_header_id = v_po_header_id;
TYPE last_update_date_tbl IS TABLE OF RCV_HEADERS_INTERFACE.last_update_date%TYPE INDEX BY BINARY_INTEGER;
TYPE last_updated_by_tbl IS TABLE OF RCV_HEADERS_INTERFACE.last_updated_by%TYPE INDEX BY BINARY_INTEGER;
rhi_last_update_date last_update_date_tbl;
rhi_last_updated_by last_updated_by_tbl;
rti_last_update_date last_update_date_tbl;
rti_last_updated_by last_updated_by_tbl;
l_api_name CONSTANT varchar2(30) := 'Insert_Interface_Values';
, message => 'Begin Insert_Interface_Values'
);
rhi_last_update_date(row_idx) := p_rhi_rows(i).last_update_date;
rhi_last_updated_by(row_idx) := p_rhi_rows(i).last_updated_by;
, message => 'Inserting ' || rhi_header_interface_id.COUNT || ' rows into RHI'
);
INSERT INTO rcv_headers_interface( header_interface_id
, group_id
, processing_status_code
, receipt_source_code
, transaction_type
, auto_transact_code
, last_update_date
, last_updated_by
, creation_date
, created_by
, vendor_id
, vendor_site_id
, ship_to_organization_id
, location_id
, expected_receipt_date
, employee_id
, validation_flag
) VALUES ( rhi_header_interface_id(i)
, rhi_group_id(i)
, rhi_processing_status_code(i)
, rhi_receipt_source_code(i)
, rhi_transaction_type(i)
, rhi_auto_transact_code(i)
, rhi_last_update_date(i)
, rhi_last_updated_by(i)
, rhi_creation_date(i)
, rhi_created_by(i)
, rhi_vendor_id(i)
, rhi_vendor_site_id(i)
, rhi_ship_to_organization_id(i)
, rhi_location_id(i)
, rhi_expected_receipt_date(i)
, rhi_employee_id(i)
, rhi_validation_flag(i)
);
, message => 'Inserted ' || rhi_header_interface_id.COUNT || ' rows into RHI'
);
rti_last_update_date(row_idx) := p_rti_rows(i).last_update_date;
rti_last_updated_by(row_idx) := p_rti_rows(i).last_updated_by;
, message => 'Inserting '|| rti_interface_transaction_id.COUNT || ' rows into RHI'
);
INSERT INTO rcv_transactions_interface( interface_transaction_id
, header_interface_id
, group_id
, lpn_group_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, transaction_type
, transaction_date
, processing_status_code
, processing_mode_code
, transaction_status_code
, employee_id
, auto_transact_code
, receipt_source_code
, source_document_code
, parent_transaction_id
, po_header_id
, po_line_id
, po_line_location_id
, po_distribution_id
, project_id
, task_id
, expected_receipt_date
, validation_flag
, amount
, job_id
, timecard_id
, timecard_ovn
) VALUES ( rti_interface_transaction_id(i)
, rti_header_interface_id(i)
, rti_group_id(i)
, rti_lpn_group_id(i)
, rti_last_update_date(i)
, rti_last_updated_by(i)
, rti_creation_date(i)
, rti_created_by(i)
, rti_transaction_type(i)
, rti_transaction_date(i)
, rti_processing_status_code(i)
, rti_processing_mode_code(i)
, rti_transaction_status_code(i)
, rti_employee_id(i)
, rti_auto_transact_code(i)
, rti_receipt_source_code(i)
, rti_source_document_code(i)
, rti_parent_transaction_id(i)
, rti_po_header_id(i)
, rti_po_line_id(i)
, rti_po_line_location_id(i)
, rti_po_distribution_id(i)
, rti_project_id(i)
, rti_task_id(i)
, rti_expected_receipt_date(i)
, rti_validation_flag(i)
, rti_amount(i)
, rti_job_id(i)
, rti_timecard_id(i)
, rti_timecard_ovn(i)
);
, message => 'Inserted ' || rti_interface_transaction_id.COUNT || ' rows into RTI'
);
, message => 'End Insert_Interface_Values'
);
END Insert_Interface_Values;
p_dst_attributes.detail_deleted := p_block.deleted;
p_dst_attributes.detail_deleted := p_block.deleted;
g_txn_msg := 'Stage 02 - Start Processing the Blocks one by one before inserting in ROI';
-- This is required since in the derive_delete_values we build the rti based on the old_attributes
-- only and the rti does not contain the dist, shipment_id values.
-- Hence get_rti_idx fails in case of a po-projects OTL setup, since in this case we use distribution_id
-- to get the rti_idx, before calling PO_STORE_TIMECARD_PKG_GRP.store_timecard_details
IF p_blocks(blk_idx).changed = 'Y' THEN
RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
, module => l_log_head
, message => 'fill the po_distribution_id and line_location_id for old attributes'
);
g_txn_msg := 'Stage 03 - Done with Capture TimeCard Block Process, going to insert valid one to Receiving interface table';
Insert_Interface_Values(l_rhi_rows, l_rti_rows);
UPDATE rcv_transactions_interface
SET transaction_status_code = 'ERROR'
WHERE group_id = g_group_id
AND transaction_status_code = 'RUNNING';
l_delete_receive_rti_id RCV_TRANSACTIONS_INTERFACE.interface_transaction_id%TYPE;
l_delete_deliver_rti_id RCV_TRANSACTIONS_INTERFACE.interface_transaction_id%TYPE;
l_delete_receive_rti_id := l_all_attributes(l_bb_id).delete_receive_rti_id;
l_delete_deliver_rti_id := l_all_attributes(l_bb_id).delete_deliver_rti_id;
|| ' delete_receive_rti_id=' || l_delete_receive_rti_id
|| ' delete_deliver_rti_id=' || l_delete_deliver_rti_id
);
OR (l_transaction_type = 'DELETE'
AND l_rti_status.EXISTS(l_delete_receive_rti_id)
AND l_rti_status.EXISTS(l_delete_deliver_rti_id))
OR (l_transaction_type = 'DELETE RECEIVE'
AND l_rti_status.EXISTS(l_receive_rti_id)
AND l_rti_status.EXISTS(l_delete_receive_rti_id)
AND l_rti_status.EXISTS(l_delete_deliver_rti_id))
OR (l_transaction_type = 'DELETE CORRECT'
AND l_rti_status.EXISTS(l_receive_rti_id)
AND l_rti_status.EXISTS(l_deliver_rti_id)
AND l_rti_status.EXISTS(l_delete_receive_rti_id)
AND l_rti_status.EXISTS(l_delete_deliver_rti_id)) THEN
HXC_USER_TYPE_DEFINITION_GRP.t_tx_detail_status(blk_idx) := 'SUCCESS';
FOR rec IN (SELECT error_message_name ||' : '|| error_message msg
FROM po_interface_errors
WHERE interface_line_id IN (l_receive_rti_id,l_deliver_rti_id,
l_delete_receive_rti_id,l_delete_deliver_rti_id)
AND table_name = 'RCV_TRANSACTIONS_INTERFACE') LOOP
RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
, module => l_log_head
, message => 'RTP errors : ' || rec.msg
);
HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status (
p_process => 'Purchasing Retrieval Process'
, p_status => g_txn_status
, p_exception_description => g_txn_msg
);
HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status (
p_process => 'Purchasing Retrieval Process'
, p_status => 'ERRORS'
, p_exception_description => g_txn_msg || 'Hit Breakpoint. Ending process in error, because we are still debugging.'
);
HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status (
p_process => 'Purchasing Retrieval Process'
, p_status => 'ERRORS'
, p_exception_description => g_txn_msg || 'Error calling Reconcile_Actions, please see FND_LOG_MESSAGES for details'
);
HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status (
p_process => 'Purchasing Retrieval Process'
, p_status => 'ERRORS'
, p_exception_description => g_txn_msg || 'Error in Retrieve_Timecards_Body, please see FND_LOG_MESSAGES for details'
);
HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status (
p_process => 'Purchasing Retrieval Process'
, p_status => 'ERRORS'
, p_exception_description => 'Error in Retrieve_Timecards_Body, please see FND_LOG_MESSAGES for details'
);
HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status (
p_process => 'Purchasing Retrieval Process'
, p_status => 'ERRORS'
, p_exception_description => SUBSTR(g_txn_msg || 'Unexpected exception in Retrieve_Timecards_Body: ' || SQLERRM, 1, 2000)
);
PROCEDURE Update_Timecard( p_operation IN VARCHAR2 )
IS
l_bb_id HXC_TIME_BUILDING_BLOCKS.time_building_block_id%TYPE;
l_api_name CONSTANT varchar2(30) := 'Update_Timecard';
g_update_start := SYSDATE;
, message => 'Begin Update_Timecard'
);
Update_Attributes(l_all_attributes(l_bb_id), l_messages);
, message => 'End Update_Timecard'
);
g_update_stop := SYSDATE;
END Update_Timecard;
g_po_line_cache.delete;
l_attributes_rec.detail_deleted := 'Y';
l_attributes_rec.detail_deleted := 'N';
|| l_attributes_rec.detail_deleted || '*'
);
l_all_attributes(l_bb_id).detail_deleted = 'N' AND
l_old_attributes(l_bb_id).validation_status IN ('SUCCESS','SKIPPED')
) THEN
RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
, module => l_log_head
, message => 'Validating new block'
);
l_all_attributes(l_bb_id).detail_deleted = 'N') THEN
RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
, module => l_log_head
, message => 'Validating old block'
);
-- validate the new attributes if block is not deleted and old block is good/irrelevant
RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_PROCEDURE
, module => l_log_head
, message => 'l_old_attributes(l_bb_id).validation_status'
||l_old_attributes(l_bb_id).validation_status
);
l_all_attributes(l_bb_id).detail_deleted = 'N' AND
l_old_attributes(l_bb_id).validation_status IN ('SUCCESS','SKIPPED') THEN
IF (-1*(l_old_attributes(l_bb_id).po_billable_amount) <> l_all_attributes(l_bb_id).po_billable_amount ) THEN
RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
, module => l_log_head
, message => 'Validating new block'
);
l_all_attributes(l_bb_id).detail_deleted = 'N') THEN
RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
, module => l_log_head
, message => 'Validating new block'
);
* When user deletes, the flags detail_changed will be 'N', detail_new will be 'Y'
* detail_deleted will be 'Y'. We do not handle this case. We did not call the
* validate_attributes and because of this, we were deleting the timecards even
* if it is in a state where it should not be deleted. Added the following
* code to call the procedure that will validate the timecard before deleting it.
*/
IF l_all_attributes(l_bb_id).detail_new = 'N' AND
( l_all_attributes(l_bb_id).detail_changed = 'N' AND
l_all_attributes(l_bb_id).detail_deleted = 'Y'
) THEN
RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
, module => l_log_head
, message => 'Validating block that is deleted '||l_all_attributes(l_bb_id).old_block
);
/* bug 13850458 When an old block was in ERROR status, but updated
now. Need to validate the new block with all values. */
IF l_all_attributes(l_bb_id).detail_new = 'N' AND
( l_all_attributes(l_bb_id).detail_changed = 'Y' AND
l_all_attributes(l_bb_id).detail_deleted = 'N' AND
l_old_attributes(l_bb_id).validation_status IN ('ERROR')) THEN
RCV_HXT_GRP.string( log_level => FND_LOG.LEVEL_STATEMENT
, module => l_log_head
, message => 'Validating block that errored earlier'
);
|| ' Update: ' || TO_CHAR((g_update_stop - g_update_start) * 8640000, '99,999.90') || ' ms' || FND_GLOBAL.local_chr(10)
|| ' Validate: ' || TO_CHAR((g_validate_stop - g_validate_start) * 8640000, '99,999.90') || ' ms' || FND_GLOBAL.local_chr(10)
);
, p_new_condition => '[PO Line Id]{ IN (SELECT TO_CHAR(pol.po_line_id)
FROM po_headers poh, po_lines pol
WHERE poh.po_header_id = pol.po_header_id
AND pol.order_type_lookup_code in (''RATE'',''FIXED PRICE'') and poh.vendor_id = '
|| p_vendor_id || ')}');
, p_new_condition => '[PO Line Id]{ IN (SELECT TO_CHAR(pol.po_line_id)
FROM po_lines pol
WHERE pol.order_type_lookup_code in (''RATE'',''FIXED PRICE''))}');
HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status (
p_process => 'Purchasing Retrieval Process'
, p_status => 'SUCCESS'
, p_exception_description => 'No more rows to process'
);
HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status (
p_process => 'Purchasing Retrieval Process'
, p_status => 'ERRORS'
, p_exception_description => G_CONC_LOG
);
HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status (
p_process => 'Purchasing Retrieval Process'
, p_status => 'ERRORS'
, p_exception_description => G_CONC_LOG
);
HXC_INTEGRATION_LAYER_V1_GRP.update_transaction_status (
p_process => 'Purchasing Retrieval Process'
, p_status => 'ERRORS'
, p_exception_description => G_CONC_LOG
);