[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_LCMInterface (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_lci_table IN OUT NOCOPY lci_table,
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) := 'Insert_LCMInterface';
SAVEPOINT Insert_LCMInterface_GRP;
SELECT nvl(user_defined_ship_num_code,'AUTOMATIC')
INTO l_user_defined_ship_num_code
FROM inl_parameters
WHERE organization_id = p_lci_table(i).organization_id;
SELECT INL_SHIP_HEADERS_INT_S.nextval
INTO l_ship_header_int_id
FROM dual;
SELECT INL_INTERFACE_GROUPS_S.nextval
INTO l_group_id
FROM dual;
INSERT INTO inl_ship_headers_int(ship_header_int_id,
group_id,
transaction_type,
processing_status_code,
interface_source_code,
interface_source_table,
interface_source_line_id,
validation_flag,
ship_num,
ship_date,
ship_type_id,
ship_type_code,
legal_entity_id,
legal_entity_name,
organization_id,
organization_code,
location_id,
location_code,
org_id,
taxation_country,
document_sub_type,
ship_header_id,
last_task_code,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date)
VALUES( l_ship_header_int_id,
l_group_id,
p_lci_table(i).transaction_type,
p_lci_table(i).processing_status_code,
p_lci_table(i).interface_source_code,
p_lci_table(i).hdr_interface_source_table,
p_lci_table(i).hdr_interface_source_line_id,
p_lci_table(i).validation_flag,
l_ship_num,
p_lci_table(i).ship_date,
p_lci_table(i).ship_type_id,
p_lci_table(i).ship_type_code,
p_lci_table(i).legal_entity_id,
p_lci_table(i).legal_entity_name,
p_lci_table(i).organization_id,
p_lci_table(i).organization_code,
p_lci_table(i).location_id,
p_lci_table(i).location_code,
p_lci_table(i).org_id,
p_lci_table(i).taxation_country,
p_lci_table(i).document_sub_type,
p_lci_table(i).ship_header_id,
p_lci_table(i).last_task_code,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
fnd_global.conc_request_id,
fnd_global.conc_program_id,
fnd_global.prog_appl_id,
decode(fnd_global.conc_request_id, -1,
NULL,
SYSDATE));
SELECT INL_SHIP_LINES_INT_S.nextval
INTO l_ship_line_int_id
FROM dual;
INSERT INTO inl_ship_lines_int (ship_header_int_id,
ship_line_int_id,
group_id,
processing_status_code,
validation_flag,
ship_line_group_reference,
party_id,
party_number,
party_site_id,
party_site_number,
source_organization_id,
source_organization_code,
ship_line_num,
ship_line_type_id,
ship_line_type_code,
ship_line_src_type_code,
ship_line_source_id,
currency_code,
currency_conversion_type,
currency_conversion_date,
currency_conversion_rate,
inventory_item_id,
txn_qty,
txn_uom_code,
txn_unit_price,
primary_qty,
primary_uom_code,
primary_unit_price,
secondary_qty,
secondary_uom_code,
secondary_unit_price,
landed_cost_flag,
allocation_enabled_flag,
trx_business_category,
intended_use,
product_fiscal_class,
product_category,
product_type,
user_def_fiscal_class,
tax_classification_code,
assessable_value,
ship_from_party_id,
ship_from_party_number,
ship_from_party_site_id,
ship_from_party_site_number,
ship_to_organization_id,
ship_to_organization_code,
ship_to_location_id,
ship_to_location_code,
bill_from_party_id,
bill_from_party_number,
bill_from_party_site_id,
bill_from_party_site_number,
bill_to_organization_id,
bill_to_organization_code,
bill_to_location_id,
bill_to_location_code,
poa_party_id,
poa_party_number,
poa_party_site_id,
poa_party_site_number,
poo_organization_id,
poo_to_organization_code,
poo_location_id,
poo_location_code,
org_id,
ship_header_id,
ship_line_id,
interface_source_table,
interface_source_line_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date)
VALUES( l_ship_header_int_id,
l_ship_line_int_id,
l_group_id,
p_lci_table(i).processing_status_code,
p_lci_table(i).validation_flag,
p_lci_table(i).ship_line_group_reference,
p_lci_table(i).party_id,
p_lci_table(i).party_number,
p_lci_table(i).party_site_id,
p_lci_table(i).party_site_number,
p_lci_table(i).source_organization_id,
p_lci_table(i).source_organization_code,
p_lci_table(i).ship_line_num,
p_lci_table(i).ship_line_type_id,
p_lci_table(i).ship_line_type_code,
p_lci_table(i).ship_line_src_type_code,
p_lci_table(i).ship_line_source_id,
p_lci_table(i).currency_code,
p_lci_table(i).currency_conversion_type,
p_lci_table(i).currency_conversion_date,
p_lci_table(i).currency_conversion_rate,
p_lci_table(i).inventory_item_id,
p_lci_table(i).txn_qty,
p_lci_table(i).txn_uom_code,
p_lci_table(i).txn_unit_price,
p_lci_table(i).primary_qty,
p_lci_table(i).primary_uom_code,
p_lci_table(i).primary_unit_price,
p_lci_table(i).secondary_qty,
p_lci_table(i).secondary_uom_code,
p_lci_table(i).secondary_unit_price,
p_lci_table(i).landed_cost_flag,
p_lci_table(i).allocation_enabled_flag,
p_lci_table(i).trx_business_category,
p_lci_table(i).intended_use,
p_lci_table(i).product_fiscal_class,
p_lci_table(i).product_category,
p_lci_table(i).product_type,
p_lci_table(i).user_def_fiscal_class,
p_lci_table(i).tax_classification_code,
p_lci_table(i).assessable_value,
p_lci_table(i).ship_from_party_id,
p_lci_table(i).ship_from_party_number,
p_lci_table(i).ship_from_party_site_id,
p_lci_table(i).ship_from_party_site_number,
p_lci_table(i).ship_to_organization_id,
p_lci_table(i).ship_to_organization_code,
p_lci_table(i).ship_to_location_id,
p_lci_table(i).ship_to_location_code,
p_lci_table(i).bill_from_party_id,
p_lci_table(i).bill_from_party_number,
p_lci_table(i).bill_from_party_site_id,
p_lci_table(i).bill_from_party_site_number,
p_lci_table(i).bill_to_organization_id,
p_lci_table(i).bill_to_organization_code,
p_lci_table(i).bill_to_location_id,
p_lci_table(i).bill_to_location_code,
p_lci_table(i).poa_party_id,
p_lci_table(i).poa_party_number,
p_lci_table(i).poa_party_site_id,
p_lci_table(i).poa_party_site_number,
p_lci_table(i).poo_organization_id,
p_lci_table(i).poo_to_organization_code,
p_lci_table(i).poo_location_id,
p_lci_table(i).poo_location_code,
p_lci_table(i).org_id,
p_lci_table(i).ship_header_id,
p_lci_table(i).ship_line_id,
p_lci_table(i).line_interface_source_table,
p_lci_table(i).line_interface_source_line_id,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
fnd_global.conc_request_id,
fnd_global.conc_program_id,
fnd_global.prog_appl_id,
decode(fnd_global.conc_request_id, -1, NULL, SYSDATE));
ROLLBACK TO Insert_LCMInterface_GRP;
ROLLBACK TO Insert_LCMInterface_GRP;
ROLLBACK TO Insert_LCMInterface_GRP;
END Insert_LCMInterface;
p_debug_info => 'Update RTIs to LC_INTERFACED'
);
UPDATE rcv_transactions_interface rti
SET processing_status_code = 'LC_INTERFACED'
WHERE transaction_status_code = 'PENDING'
AND processing_status_code = 'LC_PENDING'
AND (transaction_type = 'RECEIVE' OR
(transaction_type ='SHIP' AND auto_transact_code IN ('RECEIVE','DELIVER')))
AND source_document_code = 'PO'
AND EXISTS ( SELECT 'lcm shipment'
FROM po_line_locations_all pll
WHERE pll.line_location_id = rti.po_line_location_id
AND pll.lcm_flag = 'Y' )
AND Nvl(shipment_header_id,Header_interface_id) = currentHeaderId;
SELECT ship_to_org_id,
ship_to_location_id,
receipt_num,
vendor_id,
vendor_site_id
INTO l_ship_to_org_id,
l_ship_to_location_id,
l_receipt_num,
l_vendor_id,
l_vendor_site_id
FROM rcv_shipment_headers
WHERE shipment_header_id = p_int_rec(i).shipment_header_id;
SELECT receipt_num
INTO l_receipt_num
FROM rcv_headers_interface rhi
WHERE header_interface_id = p_int_rec(i).header_interface_id;
SELECT SHIP_TO_ORGANIZATION_ID,
location_id,
receipt_num,
vendor_id,
vendor_site_id
INTO l_ship_to_org_id,
l_ship_to_location_id,
l_receipt_num,
l_vendor_id,
l_vendor_site_id
FROM rcv_headers_interface
WHERE header_interface_id = p_int_rec(i).header_interface_id;
SELECT operating_unit
INTO l_org_id
FROM org_organization_definitions
WHERE organization_id = l_ship_to_org_id;
SELECT party_id
INTO l_party_id
FROM po_vendors
WHERE vendor_id = l_vendor_id;
SELECT ood.legal_entity,
ftv.territory_code
INTO l_legal_entity_id,
l_taxation_country
FROM fnd_territories_vl ftv,
hr_locations_v hlv,
hr_all_organization_units hru,
hr_legal_entities hle,
org_organization_definitions ood,
gl_sets_of_books gsb
WHERE ftv.territory_code = hlv.country
AND hlv.location_id = hru.location_id
AND ood.organization_id = hle.organization_id (+)
AND hru.organization_id = ood.organization_id
AND gsb.set_of_books_id = ood.set_of_books_id
AND ood.organization_id = l_org_id;
p_debug_info => 'Insert into inl_ship_headers_int table'
);
SELECT pvs.party_site_id
INTO l_party_site_id
FROM po_vendor_sites_all pvs
WHERE pvs.vendor_site_id = l_vendor_site_id
AND pvs.org_id = p_int_rec(i).org_id;
SELECT ship_line_type_id
INTO l_ship_line_type_id
FROM inl_alwd_line_types
WHERE parent_table_name = 'INL_SHIP_TYPES'
AND parent_table_id = l_ship_type_id
AND dflt_ship_line_type_flag='Y'
;
SELECT zdf.trx_business_category,
zdf.line_intended_use,
zdf.product_fisc_classification,
zdf.product_category,
zdf.product_type,
zdf.user_defined_fisc_class,
zdf.output_tax_classification_code
INTO l_trx_business_category,
l_line_intended_use,
l_product_fisc_classification,
l_product_category,
l_product_type,
l_user_defined_fisc_class,
l_output_tax_classf_code
FROM zx_lines_det_factors zdf,
po_line_locations_all pll
WHERE pll.line_location_id = p_int_rec(i).po_line_location_id
AND zdf.application_id = 201
AND zdf.trx_id = NVL(pll.po_release_id,pll.po_header_id) --Bug 7680733
AND zdf.trx_line_id = pll.line_location_id
AND zdf.entity_code = DECODE(pll.po_release_id,NULL,'PURCHASE_ORDER','RELEASE') --Bug 7680733
AND zdf.event_class_code = DECODE(pll.po_release_id,NULL,'PO_PA','RELEASE'); --Bug 7680733
p_debug_info => 'Insert inl_ship_lines_int values in PL/SQL table.'
);
Insert_LCMInterface(
p_api_version => l_api_version,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_lci_table => l_lci_table,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
UPDATE rcv_transactions_interface rti
SET processing_status_code = 'LC_INTERFACED'
WHERE transaction_status_code = 'PENDING'
AND processing_status_code = 'LC_PENDING'
AND (transaction_type = 'RECEIVE' OR
(transaction_type ='SHIP' AND auto_transact_code IN ('RECEIVE','DELIVER')))
AND source_document_code = 'PO'
AND EXISTS ( SELECT 'lcm shipment'
FROM po_line_locations_all pll
WHERE pll.line_location_id = rti.po_line_location_id
AND pll.lcm_flag = 'Y' )
AND Nvl(shipment_header_id,Header_interface_id) = Nvl(p_int_rec(i).shipment_header_id,p_int_rec(i).header_interface_id);
UPDATE rcv_headers_interface rhi
SET processing_status_code = 'LC_INTERFACED'
WHERE processing_status_code='LC_PENDING'
AND header_interface_id=p_int_rec(i).header_interface_id;
UPDATE rcv_transactions_interface
SET unit_landed_cost = l_actual_unit_landed_cost
WHERE processing_status_code = 'RUNNING'
AND transaction_status_code = 'PENDING'
AND processing_mode_code = p_processing_mode
AND group_id = nvl(p_group_id, group_id)
AND mo_global.check_access(org_id) = 'Y'
AND processing_mode_code = p_processing_mode
AND source_document_code = 'PO'
AND transaction_type NOT IN('SHIP', 'RECEIVE', 'ACCEPT', 'REJECT','TRANSFER','UNORDERED')
AND lcm_shipment_line_id IS NOT NULL
AND lcm_shipment_line_id = p_rti_rec(i).line_id;
p_var_name => 'RTIs updated',
p_var_value => sql%rowcount);
UPDATE rcv_transactions_interface rti
SET unit_landed_cost = NULL,
processing_status_code = 'ERROR'
WHERE processing_status_code = 'RUNNING'
AND transaction_status_code = 'PENDING'
AND processing_mode_code = p_processing_mode
AND group_id = nvl(p_group_id, group_id)
AND processing_mode_code = p_processing_mode
AND mo_global.check_access(org_id) = 'Y'
AND source_document_code = 'PO'
AND transaction_type NOT IN('SHIP', 'RECEIVE', 'ACCEPT', 'REJECT','TRANSFER','UNORDERED')
AND lcm_shipment_line_id IS NOT NULL
AND lcm_shipment_line_id = p_rti_rec(i).line_id;
UPDATE rcv_transactions_interface
SET processing_status_code = 'ERROR'
WHERE processing_status_code = 'RUNNING'
AND transaction_status_code = 'PENDING'
AND mo_global.check_access(org_id) = 'Y'
AND processing_mode_code = p_processing_mode
AND group_id = nvl(p_group_id, group_id)
AND source_document_code = 'PO'
AND transaction_type NOT IN ('SHIP', 'RECEIVE', 'ACCEPT', 'REJECT','TRANSFER','UNORDERED')
AND lcm_shipment_line_id IS NOT NULL;
p_debug_info => sql%rowcount || ' RTIs updated to Error');
PROCEDURE Call_UpdateRCV (p_ship_lines_table IN ship_lines_table,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_proc_name CONSTANT VARCHAR2(30) := 'Call_UpdateRCV';
l_rti_lc_info_table RCV_UPDATE_RTI_LC.rcv_cost_table := RCV_UPDATE_RTI_LC.rcv_cost_table();
l_rcv_int_table RCV_UPDATE_RTI_LC.lcm_int_table := RCV_UPDATE_RTI_LC.lcm_int_table();
p_debug_info => 'Before calling RCV_UPDATE_RTI_LC.Update_RTI: ' || to_char(SYSDATE,'DD-MON-YYYY HH:MI:SS'));
RCV_UPDATE_RTI_LC.Update_RTI(p_int_rec => l_rti_lc_info_table,
x_lcm_int => l_rcv_int_table);
p_debug_info => 'After calling RCV_UPDATE_RTI_LC.Update_RTI: ' || to_char(SYSDATE,'DD-MON-YYYY HH:MI:SS'));
END Call_UpdateRCV;
p_debug_info => 'Before calling Call_UpdateRCV');
Call_UpdateRCV (p_ship_lines_table => l_ship_lines_table,
x_return_status => l_return_status);
p_debug_info => 'After calling Call_UpdateRCV');
p_var_name => 'Call_UpdateRCV l_return_status: ',
p_var_value => l_return_status);
PROCEDURE Call_InsertRCV(p_ship_header_id IN NUMBER,
p_ship_lines_table IN ship_lines_table,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_proc_name CONSTANT VARCHAR2(30) := 'Call_InsertRCV';
l_ship_lines_table RCV_INSERT_FROM_INL.rti_rec_table:= RCV_INSERT_FROM_INL.rti_rec_table();
p_debug_info => 'Before call RCV_INSERT_FROM_INL.insert_rcv_tables');
RCV_INSERT_FROM_INL.insert_rcv_tables(p_int_rec => l_ship_lines_table,
p_ship_header_id => p_ship_header_id);
p_debug_info => 'After call RCV_INSERT_FROM_INL.insert_rcv_tables');
END Call_InsertRCV;
p_debug_info => 'Before call Call_InsertRCV');
Call_InsertRCV(p_ship_header_id => p_ship_header_id,
p_ship_lines_table => l_ship_lines_table,
x_return_status => l_return_status);
p_debug_info => 'After call Call_InsertRCV');
p_var_name => 'Call_InsertRCV l_return_status:',
p_var_value => l_return_status);
SELECT ish.organization_id,
isl.ship_line_group_id,
isl.ship_line_id,
isl.inventory_item_id,
isl.parent_ship_line_id,
isl.ship_line_num,
rtr.transaction_id,
ish.adjustment_num
FROM inl_ship_headers ish,
inl_ship_lines isl,
rcv_transactions rtr
WHERE ish.ship_header_id = isl.ship_header_id
AND rtr.po_line_location_id = isl.ship_line_source_id
AND rtr.lcm_shipment_line_id = isl.ship_line_id
AND rtr.parent_transaction_id = -1
AND ish.ship_header_id = p_ship_header_id
ORDER BY isl.ship_line_id;
SELECT islv.unit_landed_cost
INTO l_new_landed_cost
FROM inl_shipln_landed_costs_v islv
WHERE islv.adjustment_num = ship_ln_list(i).adjustment_num
AND islv.ship_header_id = p_ship_header_id
AND islv.ship_line_group_id = ship_ln_list(i).ship_line_group_id --Bug 7678900
AND islv.ship_line_num = ship_ln_list(i).ship_line_num;
SELECT islv.unit_landed_cost
INTO l_prior_landed_cost
FROM inl_shipln_landed_costs_v islv
WHERE islv.adjustment_num = ship_ln_list(i).adjustment_num - 1
AND islv.ship_header_id = p_ship_header_id
AND islv.ship_line_group_id = ship_ln_list(i).ship_line_group_id --Bug 7678900
AND islv.ship_line_num = ship_ln_list(i).ship_line_num;
l_debug_info := 'Insert INTO CST_LC_ADJ_INTERFACE TABLE';
INSERT INTO cst_lc_adj_interface (transaction_id,
rcv_transaction_id,
organization_id,
inventory_item_id,
transaction_date,
prior_landed_cost,
new_landed_cost,
process_status,
process_phase,
group_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES (NULL, -- transaction_id
ship_ln_list(i).transaction_id,
ship_ln_list(i).organization_id,
ship_ln_list(i).inventory_item_id,
l_current_date,
l_prior_landed_cost,
l_new_landed_cost,
1, --process_status (1 = Pending)
1, --process_phase (1 = Pending)
NULL, -- group_id
l_current_date, -- creation_date
FND_GLOBAL.user_id, -- created_by
l_current_date, -- last_update_date
FND_GLOBAL.user_id, --last_updated_by
FND_GLOBAL.login_id, --last_update_login
NULL, --request_id,
NULL, --program_application_id,
NULL, --program_id,
NULL); --program_update_date
SELECT
sl.currency_code,
sl.currency_conversion_type,
nvl(sl.currency_conversion_date,sl.creation_date),
sl.currency_conversion_rate
INTO
x_currency_code,
x_currency_conversion_type,
x_currency_conversion_date,
x_currency_conversion_rate
FROM inl_ship_lines_all sl
WHERE sl.ship_line_id = p_ship_line_id;