[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 INL_SHIP_HEADERS_INT_S.nextval, INL_INTERFACE_GROUPS_S.nextval
INTO l_ship_header_int_id, l_group_id
FROM dual;
p_debug_info => 'Inserting into inl_ship_headers_int' );
INSERT INTO inl_ship_headers_int(
ship_header_int_id, /* 01 */
group_id, /* 02 */
transaction_type, /* 03 */
processing_status_code, /* 04 */
interface_source_code, /* 05 */
interface_source_table, /* 06 */
interface_source_line_id, /* 07 */
validation_flag, /* 08 */
rcv_enabled_flag, /* 09 */
ship_num, /* 10 */
ship_date, /* 11 */
ship_type_id, /* 12 */
ship_type_code, /* 13 */
organization_id, /* 14 */
organization_code, /* 15 */
location_id, /* 16 */
location_code, /* 17 */
taxation_country, /* 18 */
document_sub_type, /* 19 */
ship_header_id, /* 20 */
last_task_code, /* 21 */
created_by, /* 22 */
creation_date, /* 23 */
last_updated_by, /* 24 */
last_update_date, /* 25 */
last_update_login, /* 26 */
request_id, /* 27 */
program_id, /* 28 */
program_application_id, /* 29 */
program_update_date /* 30 */
)
VALUES(
l_ship_header_int_id, /* 01 */
l_group_id, /* 02 */
p_lci_table(i).transaction_type, /* 03 */
p_lci_table(i).processing_status_code, /* 04 */
p_lci_table(i).interface_source_code, /* 05 */
p_lci_table(i).hdr_interface_source_table, /* 06 */
p_lci_table(i).hdr_interface_source_line_id, /* 07 */
p_lci_table(i).validation_flag, /* 08 */
p_lci_table(i).rcv_enabled_flag, /* 09 */--Bug#9279355
p_lci_table(i).ship_num, /* 10 */--Bug#8971617
p_lci_table(i).ship_date, /* 11 */
p_lci_table(i).ship_type_id, /* 12 */
p_lci_table(i).ship_type_code, /* 13 */
p_lci_table(i).organization_id, /* 14 */
p_lci_table(i).organization_code, /* 15 */
p_lci_table(i).location_id, /* 16 */
p_lci_table(i).location_code, /* 17 */
p_lci_table(i).taxation_country, /* 18 */
p_lci_table(i).document_sub_type, /* 19 */
p_lci_table(i).ship_header_id, /* 20 */
p_lci_table(i).last_task_code, /* 21 */
fnd_global.user_id, /* 22 */
SYSDATE, /* 23 */
fnd_global.user_id, /* 24 */
SYSDATE, /* 25 */
fnd_global.login_id, /* 26 */
fnd_global.conc_request_id, /* 27 */
fnd_global.conc_program_id, /* 28 */
fnd_global.prog_appl_id, /* 29 */
decode(fnd_global.conc_request_id, -1, NULL, SYSDATE) /* 30 */
);
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, /* 01 */
ship_line_int_id, /* 02 */
processing_status_code, /* 03 */
ship_line_group_reference, /* 04 */
party_id, /* 05 */
party_number, /* 06 */
party_site_id, /* 07 */
party_site_number, /* 08 */
source_organization_id, /* 09 */
source_organization_code, /* 10 */
ship_line_num, /* 11 */
ship_line_type_id, /* 12 */
ship_line_type_code, /* 13 */
ship_line_src_type_code, /* 14 */
ship_line_source_id, /* 15 */
currency_code, /* 16 */
currency_conversion_type, /* 17 */
currency_conversion_date, /* 18 */
currency_conversion_rate, /* 19 */
inventory_item_id, /* 20 */
txn_qty, /* 21 */
txn_uom_code, /* 22 */
txn_unit_price, /* 23 */
primary_qty, /* 24 */
primary_uom_code, /* 25 */
primary_unit_price, /* 26 */
secondary_qty, /* 27 */
secondary_uom_code, /* 28 */
secondary_unit_price, /* 29 */
landed_cost_flag, /* 30 */
allocation_enabled_flag, /* 31 */
trx_business_category, /* 32 */
intended_use, /* 33 */
product_fiscal_class, /* 34 */
product_category, /* 35 */
product_type, /* 36 */
user_def_fiscal_class, /* 37 */
tax_classification_code, /* 38 */
assessable_value, /* 39 */
ship_from_party_id, /* 40 */
ship_from_party_number, /* 41 */
ship_from_party_site_id, /* 42 */
ship_from_party_site_number, /* 43 */
ship_to_organization_id, /* 44 */
ship_to_organization_code, /* 45 */
ship_to_location_id, /* 46 */
ship_to_location_code, /* 47 */
bill_from_party_id, /* 48 */
bill_from_party_number, /* 49 */
bill_from_party_site_id, /* 50 */
bill_from_party_site_number, /* 51 */
bill_to_organization_id, /* 52 */
bill_to_organization_code, /* 53 */
bill_to_location_id, /* 54 */
bill_to_location_code, /* 55 */
poa_party_id, /* 56 */
poa_party_number, /* 57 */
poa_party_site_id, /* 58 */
poa_party_site_number, /* 59 */
poo_organization_id, /* 60 */
poo_to_organization_code, /* 61 */
poo_location_id, /* 62 */
poo_location_code, /* 63 */
ship_header_id, /* 64 */
ship_line_id, /* 65 */
interface_source_table, /* 66 */
interface_source_line_id, /* 67 */
created_by, /* 68 */
creation_date, /* 69 */
last_updated_by, /* 70 */
last_update_date, /* 71 */
last_update_login, /* 72 */
request_id, /* 73 */
program_id, /* 74 */
program_application_id, /* 75 */
program_update_date) /* 76 */
VALUES(
l_ship_header_int_id, /* 01 */
l_ship_line_int_id, /* 02 */
p_lci_table(i).processing_status_code, /* 03 */
p_lci_table(i).ship_line_group_reference, /* 04 */
p_lci_table(i).party_id, /* 05 */
p_lci_table(i).party_number, /* 06 */
p_lci_table(i).party_site_id, /* 07 */
p_lci_table(i).party_site_number, /* 08 */
p_lci_table(i).source_organization_id, /* 09 */
p_lci_table(i).source_organization_code, /* 10 */
p_lci_table(i).ship_line_num, /* 11 */
p_lci_table(i).ship_line_type_id, /* 12 */
p_lci_table(i).ship_line_type_code, /* 13 */
p_lci_table(i).ship_line_src_type_code, /* 14 */
p_lci_table(i).ship_line_source_id, /* 15 */
p_lci_table(i).currency_code, /* 16 */
p_lci_table(i).currency_conversion_type, /* 17 */
p_lci_table(i).currency_conversion_date, /* 18 */
p_lci_table(i).currency_conversion_rate, /* 19 */
p_lci_table(i).inventory_item_id, /* 20 */
p_lci_table(i).txn_qty, /* 21 */
p_lci_table(i).txn_uom_code, /* 22 */
p_lci_table(i).txn_unit_price, /* 23 */
p_lci_table(i).primary_qty, /* 24 */
p_lci_table(i).primary_uom_code, /* 25 */
p_lci_table(i).primary_unit_price, /* 26 */
p_lci_table(i).secondary_qty, /* 27 */
p_lci_table(i).secondary_uom_code, /* 28 */
p_lci_table(i).secondary_unit_price, /* 29 */
p_lci_table(i).landed_cost_flag, /* 30 */
p_lci_table(i).allocation_enabled_flag, /* 31 */
p_lci_table(i).trx_business_category, /* 32 */
p_lci_table(i).intended_use, /* 33 */
p_lci_table(i).product_fiscal_class, /* 34 */
p_lci_table(i).product_category, /* 35 */
p_lci_table(i).product_type, /* 36 */
p_lci_table(i).user_def_fiscal_class, /* 37 */
p_lci_table(i).tax_classification_code, /* 38 */
p_lci_table(i).assessable_value, /* 39 */
p_lci_table(i).ship_from_party_id, /* 40 */
p_lci_table(i).ship_from_party_number, /* 41 */
p_lci_table(i).ship_from_party_site_id, /* 42 */
p_lci_table(i).ship_from_party_site_number, /* 43 */
p_lci_table(i).ship_to_organization_id, /* 44 */
p_lci_table(i).ship_to_organization_code, /* 45 */
p_lci_table(i).ship_to_location_id, /* 46 */
p_lci_table(i).ship_to_location_code, /* 47 */
p_lci_table(i).bill_from_party_id, /* 48 */
p_lci_table(i).bill_from_party_number, /* 49 */
p_lci_table(i).bill_from_party_site_id, /* 50 */
p_lci_table(i).bill_from_party_site_number, /* 51 */
p_lci_table(i).bill_to_organization_id, /* 52 */
p_lci_table(i).bill_to_organization_code, /* 53 */
p_lci_table(i).bill_to_location_id, /* 54 */
p_lci_table(i).bill_to_location_code, /* 55 */
p_lci_table(i).poa_party_id, /* 56 */
p_lci_table(i).poa_party_number, /* 57 */
p_lci_table(i).poa_party_site_id, /* 58 */
p_lci_table(i).poa_party_site_number, /* 59 */
p_lci_table(i).poo_organization_id, /* 60 */
p_lci_table(i).poo_to_organization_code, /* 61 */
p_lci_table(i).poo_location_id, /* 62 */
p_lci_table(i).poo_location_code, /* 63 */
p_lci_table(i).ship_header_id, /* 64 */
p_lci_table(i).ship_line_id, /* 65 */
p_lci_table(i).line_interface_source_table, /* 66 */
p_lci_table(i).line_interface_source_line_id, /* 67 */
fnd_global.user_id, /* 68 */
SYSDATE, /* 69 */
fnd_global.user_id, /* 70 */
SYSDATE, /* 71 */
fnd_global.login_id, /* 72 */
fnd_global.conc_request_id, /* 73 */
fnd_global.conc_program_id, /* 74 */
fnd_global.prog_appl_id, /* 75 */
decode(fnd_global.conc_request_id, -1, NULL, SYSDATE) /* 76 */
);
ROLLBACK TO Insert_LCMInterface_GRP;
ROLLBACK TO Insert_LCMInterface_GRP;
ROLLBACK TO Insert_LCMInterface_GRP;
END Insert_LCMInterface;
l_records_inserted NUMBER := 0;
SELECT
ship_to_org_id,
ship_to_location_id,
receipt_num,
vendor_id,
nvl(vendor_site_id,p_int_rec(i).vendor_site_id), --Bug#8820297
customer_id
INTO
l_ship_to_org_id,
l_ship_to_location_id,
l_receipt_num,
l_vendor_id,
l_vendor_site_id,
l_customer_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,
nvl(vendor_site_id,p_int_rec(i).vendor_site_id), --Bug#8820297
customer_id
INTO
l_ship_to_org_id,
l_ship_to_location_id,
l_receipt_num,
l_vendor_id,
l_vendor_site_id,
l_customer_id
FROM rcv_headers_interface
WHERE header_interface_id = p_int_rec(i).header_interface_id;
SELECT operating_unit, organization_name
INTO l_org_id, l_ship_to_org_name
FROM org_organization_definitions
WHERE organization_id = l_ship_to_org_id;
SELECT nvl(user_defined_ship_num_code,'AUTOMATIC') --Bug#8971617
INTO l_user_defined_ship_num_code
FROM inl_parameters ipa
WHERE ipa.organization_id = l_ship_to_org_id;
SELECT party_id
INTO l_party_id
FROM po_vendors
WHERE vendor_id = l_vendor_id;
SELECT party_id
INTO l_party_id
FROM hz_customer_party_find_v
WHERE customer_id = l_customer_id;
SELECT hl.location_code, hl.country
INTO l_ship_to_location_code,
l_taxation_country
FROM hr_locations hl
WHERE hl.location_id = l_lci_table(l_ind_lci).location_id
AND hl.receiving_site_flag = 'Y';
SELECT hl.location_code, hl.country
INTO l_ship_to_location_code,
l_taxation_country
FROM hr_organization_units hou,
hr_locations hl
WHERE hl.location_id = hou.location_id
AND hl.receiving_site_flag = 'Y'
AND hou.organization_id = l_ship_to_org_id;
SELECT hou.default_legal_context_id legal_entity,
gl.currency_code
INTO l_legal_entity_id,
l_dflt_currency_code
FROM hr_operating_units hou,
gl_sets_of_books gl
WHERE gl.set_of_books_id = hou.set_of_books_id
AND hou.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,
muom.uom_code --Bug#9884458
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,
l_po_UOM_code --Bug#9884458
FROM zx_lines_det_factors zdf,
po_line_locations_all pll,
po_lines_all pl, --Bug#9884458
mtl_units_of_measure muom --Bug#9884458
WHERE pll.line_location_id = p_int_rec(i).po_line_location_id
AND pll.po_line_id = pl.po_line_id --Bug#9884458
AND muom.unit_of_measure = pl.unit_meas_lookup_code --Bug#9884458
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
SELECT rsl.shipment_line_id
INTO l_ship_line_src_id
FROM rcv_shipment_lines rsl
WHERE requisition_line_id = p_int_rec(i).requisition_line_id;
SELECT unit_price
INTO l_txn_unit_price
FROM po_requisition_lines_all prl
WHERE prl.requisition_line_id = p_int_rec(i).requisition_line_id;
SELECT unit_selling_price
INTO l_txn_unit_price
FROM oe_order_lines_all
WHERE line_id = p_int_rec(i).oe_order_line_id;
SELECT mum.uom_code
INTO l_sec_uom_code
FROM mtl_units_of_measure mum
WHERE mum.unit_of_measure = p_int_rec(i).secondary_unit_of_measure;
p_debug_info => 'Insert inl_ship_lines_int values in PL/SQL table.');
p_debug_info => 'Call Insert_LCMInterface to insert data in lcm interface table');
INL_INTEGRATION_GRP.Insert_LCMInterface(p_api_version => l_api_version,
p_init_msg_list => FND_API.G_FALSE,
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_headers_interface rhi
SET processing_status_code = 'LC_INTERFACED'
WHERE processing_status_code='LC_PENDING'
AND header_interface_id=l_lci_table(i).header_interface_id;
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 in ('RECEIVE', 'MATCH') OR --Bug#9275335
(transaction_type ='SHIP' AND auto_transact_code IN ('RECEIVE','DELIVER')))
AND source_document_code IN ('PO', 'REQ', 'RMA')
AND Nvl(shipment_header_id,Header_interface_id) = Nvl(l_lci_table(i).shipment_header_id,l_lci_table(i).header_interface_id);
l_records_inserted := l_records_inserted + 1;
FND_FILE.put_line( FND_FILE.log, '< ***** ' || 'Records Inserted: ' || l_records_inserted); -- Bug#9258936
UPDATE rcv_transactions_interface
SET unit_landed_cost = l_actual_unit_landed_cost,
lcm_adjustment_num = l_actual_ajust_num -- opm integration
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'
-- SCM-051 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,
lcm_adjustment_num = NULL, -- opm integration
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'
-- SCM-051 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'
-- SCM-051 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
FROM inl_ship_headers ish,
inl_ship_lines isl,
rcv_transactions rtr,
mtl_parameters mp --BUG#8933768
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
AND rtr.organization_id = mp.organization_id --BUG#8933768
AND NVL(mp.process_enabled_flag,'N') <> 'Y' --BUG#8933768
ORDER BY isl.ship_line_num;
SELECT islv.adjustment_num, islv.unit_landed_cost
FROM inl_shipln_landed_costs_v islv
WHERE islv.adjustment_num <= pc_adjustment_num
AND islv.ship_header_id = p_ship_header_id
AND islv.ship_line_group_id = pc_ship_line_group_id --Bug 7678900
AND islv.ship_line_num = pc_ship_line_num
order by islv.adjustment_num desc
;
SELECT ABS(islv.adjustment_num) adjustment_num, islv.unit_landed_cost -- SCM-051
FROM inl_shipln_landed_costs_v islv,
( SELECT DISTINCT(adjustment_num) adjustment_num
FROM inl_allocations
WHERE allocation_id > p_max_allocation_id
AND ship_header_id = p_ship_header_id
UNION
SELECT MAX(adjustment_num)
FROM inl_allocations
WHERE allocation_id <= p_max_allocation_id
AND ship_header_id = p_ship_header_id
) alloc
WHERE
islv.ship_header_id = p_ship_header_id
AND islv.ship_line_group_id = pc_ship_line_group_id --Bug 7678900
AND islv.ship_line_num = pc_ship_line_num
AND islv.adjustment_num = alloc.adjustment_num
ORDER BY ABS(islv.adjustment_num) -- SCM-051
;
l_debug_info := 'Insert INTO CST_LC_ADJ_INTERFACE TABLE';
INSERT INTO cst_lc_adj_interface (
transaction_id, /* 01 */
rcv_transaction_id, /* 02 */
organization_id, /* 03 */
inventory_item_id, /* 04 */
transaction_date, /* 05 */
prior_landed_cost, /* 06 */
new_landed_cost, /* 07 */
process_status, /* 08 */
process_phase, /* 09 */
group_id, /* 10 */
creation_date, /* 11 */
created_by, /* 12 */
last_update_date, /* 13 */
last_updated_by, /* 14 */
last_update_login, /* 15 */
request_id, /* 16 */
program_application_id, /* 17 */
program_id, /* 18 */
program_update_date /* 19 */
) VALUES (
NULL, -- transaction_id /* 01 */
ship_ln_list(i).transaction_id, /* 02 */
ship_ln_list(i).organization_id, /* 03 */
ship_ln_list(i).inventory_item_id, /* 04 */
l_current_date, /* 05 */
l_prior_landed_cost, /* 06 */
shipln_landed_costs_v_list(j).unit_landed_cost,/* 07 */ --Bug#10032820
1, --process_status (1 = Pending) /* 08 */
1, --process_phase (1 = Pending) /* 09 */
NULL, -- group_id /* 10 */
l_current_date, -- creation_date /* 11 */
FND_GLOBAL.user_id, -- created_by /* 12 */
l_current_date, -- last_update_date /* 13 */
FND_GLOBAL.user_id, --last_updated_by /* 14 */
FND_GLOBAL.login_id, --last_update_login /* 15 */
NULL, --request_id, /* 16 */
NULL, --program_application_id, /* 17 */
NULL, --program_id, /* 18 */
NULL --program_update_date /* 19 */
);
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;
SELECT
pl.po_line_id,
pl.item_id,
pl.item_revision,
pl.category_id,
pl.unit_price,
pl.quantity pl_quantity,
pl.amount pl_amount,
pl.secondary_uom,
pl.secondary_unit_of_measure,
pl.secondary_qty,
pl.line_num,
pl.item_description,
pll.secondary_quantity,
pll.line_location_id,
INL_SHIPMENT_PVT.Get_SrcAvailableQty('PO', pll.line_location_id) quantity,
pll.unit_meas_lookup_code,
pll.po_release_id,
pll.ship_to_location_id,
pll.ship_to_organization_id,
ph.bill_to_location_id,
pll.shipment_num,
pll.org_id,
pll.match_option,
pll.amount,
pll.value_basis,
pll.matching_basis,
'Y' lcm_flag
FROM po_headers_all ph,
po_lines_all pl,
po_line_locations_all pll,
po_releases_all pr -- Bug 9734841
WHERE ph.po_header_id = p_po_header_id
AND pl.po_header_id = ph.po_header_id
AND pll.po_line_id = pl.po_line_id
AND NVL(pll.po_release_id, -999) = NVL(p_po_release_id, -999) -- Bug 14280113
AND pll.po_release_id = pr.po_release_id (+)
AND NVL(pr.approved_flag, 'N') <> 'Y'
AND NVL(pl.cancel_flag,'N') = 'N'
AND NVL(pll.cancel_flag, 'N') = 'N'
AND DECODE(ph.type_lookup_code, 'PLANNED', 'SCHEDULED', pll.shipment_type) = pll.shipment_type -- Bug 9746741
AND INV_UTILITIES.inv_check_lcm(pl.item_id,
pll.ship_to_organization_id,
NULL,
NULL,
p_vendor_id,
p_vendor_site_id) = 'Y'
-- Debug 13064637 pick PO details based on the profile option set
AND DECODE(FND_PROFILE.VALUE('RCV_CLOSED_PO_DEFAULT_OPTION'),'N',NVL(pll.closed_code,'OPEN'), 'OPEN') NOT IN ('CLOSED','CLOSED FOR RECEIVING')
ORDER BY pll.ship_to_organization_id,
pll.ship_to_location_id,
pl.line_num,
pll.line_location_id;
SELECT ialw.ship_line_type_id,
islt.dflt_landed_cost_flag,
islt.dflt_allocation_enabled_flag
INTO l_ship_line_type_id,
l_landed_cost_flag,
l_allocation_enabled_flag
FROM inl_alwd_line_types ialw,
inl_ship_line_types_b islt -- Bug 9814077
WHERE ialw.parent_table_name = 'INL_SHIP_TYPES'
AND ialw.parent_table_id = l_ship_type_id
AND ialw.dflt_ship_line_type_flag = 'Y'
AND ialw.ship_line_type_id = islt.ship_line_type_id;
SELECT pv.party_id
INTO l_party_id
FROM po_vendors pv
WHERE pv.vendor_id = p_po_hdr_rec.vendor_id;
SELECT pv.party_site_id
INTO l_party_site_id
FROM po_vendor_sites_all pv
WHERE pv.vendor_site_id = p_po_hdr_rec.vendor_site_id;
g_records_inserted := g_records_inserted + 1;
g_lines_inserted := g_lines_inserted ||
'// Line Number: ' || c_pll_tab(i).line_num || ' LCM Flag: ' || c_pll_tab(i).lcm_flag;
SELECT hl.location_code,
hl.country
INTO l_ship_to_location_code,
l_taxation_country
FROM hr_locations hl
WHERE hl.location_id = l_ship_to_location_id
AND hl.receiving_site_flag = 'Y';
SELECT NVL(user_defined_ship_num_code,'AUTOMATIC')
INTO l_user_defined_ship_num_code
FROM inl_parameters ipa
WHERE ipa.organization_id = l_ship_to_organization_id;
SELECT uom_code
INTO l_lci_table(l_ind_lci).txn_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = c_pll_tab(i).unit_meas_lookup_code;
SELECT mum.uom_code
INTO l_sec_uom_code
FROM mtl_units_of_measure mum
WHERE mum.unit_of_measure = c_pll_tab(i).secondary_unit_of_measure;
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_lci_table(l_ind_lci).trx_business_category ,
l_lci_table(l_ind_lci).intended_use,
l_lci_table(l_ind_lci).product_fiscal_class,
l_lci_table(l_ind_lci).product_category,
l_lci_table(l_ind_lci).product_type,
l_lci_table(l_ind_lci).user_def_fiscal_class,
l_lci_table(l_ind_lci).tax_classification_code
FROM zx_lines_det_factors zdf,
po_line_locations_all pll
WHERE pll.line_location_id = c_pll_tab(i).line_location_id
AND zdf.application_id = 201
AND zdf.trx_id = NVL(pll.po_release_id,pll.po_header_id)
AND zdf.trx_line_id = pll.line_location_id
AND zdf.entity_code = DECODE(pll.po_release_id,NULL,'PURCHASE_ORDER','RELEASE')
AND zdf.event_class_code = DECODE(pll.po_release_id,NULL,'PO_PA','RELEASE');
p_debug_info => 'Call Insert_LCMInterface to insert data in lcm interface table');
INL_INTEGRATION_GRP.Insert_LCMInterface(p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
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);
p_var_name => 'g_records_inserted',
p_var_value => g_records_inserted);
SELECT ph.segment1, -- 01
ph.vendor_id, -- 02
ph.vendor_site_id, -- 03
pr.revision_num, -- 04
ph.currency_code, -- 05
ph.rate_type, -- 06
ph.rate_date, -- 07
ph.rate, -- 08
ph.org_id, -- 09
ph.approved_date, -- 10
ph.ship_via_lookup_code freight_code, -- 11
ph.org_id -- 12
INTO l_po_hdr_rec.segment1, -- 01
l_simulation_rec.vendor_id, -- 02
l_simulation_rec.vendor_site_id, -- 03
l_simulation_rec.parent_table_revision_num, -- 04
l_po_hdr_rec.currency_code, -- 05
l_po_hdr_rec.rate_type, -- 06
l_po_hdr_rec.rate_date, -- 07
l_po_hdr_rec.rate, -- 08
l_po_hdr_rec.org_id, -- 09
l_po_hdr_rec.approved_date, -- 10
l_simulation_rec.freight_code, -- 11
l_simulation_rec.org_id -- 12
FROM po_headers_all ph,
po_releases_all pr
WHERE ph.po_header_id = pr.po_header_id
AND pr.po_release_id = p_po_release_id;
SELECT ph.po_header_id, -- 01
ph.segment1, -- 02
ph.vendor_id, -- 03
ph.vendor_site_id, -- 04
ph.revision_num, -- 05
ph.currency_code, -- 06
ph.rate_type, -- 07
ph.rate_date, -- 08
ph.rate, -- 09
ph.org_id, -- 10
ph.approved_date, -- 11
ph.ship_via_lookup_code freight_code, -- 12
ph.org_id -- 13
INTO l_simulation_rec.parent_table_id, -- 01
l_po_hdr_rec.segment1, -- 02
l_simulation_rec.vendor_id, -- 03
l_simulation_rec.vendor_site_id, -- 04
l_simulation_rec.parent_table_revision_num, -- 05
l_po_hdr_rec.currency_code, -- 06
l_po_hdr_rec.rate_type, -- 07
l_po_hdr_rec.rate_date, -- 08
l_po_hdr_rec.rate, -- 09
l_po_hdr_rec.org_id, -- 10
l_po_hdr_rec.approved_date, -- 11
l_simulation_rec.freight_code, -- 12
l_simulation_rec.org_id -- 13
FROM po_headers_all ph
WHERE po_header_id = p_po_header_id;
SELECT pll.line_location_id,
pll.shipment_num,
pl.line_num,
ph.segment1
FROM po_line_locations pll,
po_lines pl,
po_headers ph,
inl_simulations s
WHERE INV_UTILITIES.inv_check_lcm(
pl.item_id,
pll.ship_to_organization_id,
NULL,
NULL,
ph.vendor_id,
ph.vendor_site_id) = 'Y'
AND ph.po_header_id = pl.po_header_id
AND pll.po_header_id = pl.po_header_id
AND pll.po_line_id = pl.po_line_id
AND pl.po_header_id = s.parent_table_id
AND s.parent_table_name = 'PO_HEADERS'
AND s.simulation_id = p_simulation_id
AND NOT EXISTS (SELECT sl.ship_line_id
FROM inl_ship_lines sl,
inl_ship_headers sh
WHERE sh.ship_header_id = sl.ship_header_id
AND sl.ship_line_source_id = pll.line_location_id
AND sl.ship_line_src_type_code = 'PO'
AND sh.simulation_id = s.simulation_id)
-- Bug #9821615 -- AND (pll.po_release_id IS NULL
-- Bug #9941402
AND (ph.type_lookup_code IN ('PLANNED','BLANKET')
AND EXISTS (SELECT po_release_id
FROM po_releases pr
WHERE pr.po_release_id = pll.po_release_id)
OR ph.type_lookup_code = 'STANDARD');
SELECT meaning
INTO l_src_type
FROM fnd_lookup_values_vl l
WHERE l.lookup_type = 'INL_SHIP_LINE_SRC_TYPES'
AND l.lookup_code = 'PO';
SELECT sh.ship_num,
sl.ship_line_id,
sl.ship_line_num,
sl.ship_line_src_type_code,
sl.ship_line_source_id,
sl.txn_qty,
muv.unit_of_measure,
sl.txn_unit_price,
sl.currency_code
FROM inl_ship_lines_all sl,
inl_ship_headers sh,
mtl_units_of_measure_vl muv
WHERE muv.uom_code = sl.txn_uom_code
AND sl.ship_header_id = sh.ship_header_id
AND sl.ship_line_src_type_code = 'PO'
AND sh.simulation_id = p_simulation_id
ORDER BY sh.ship_num, sl.ship_line_num;
SELECT INL_SHIPMENT_PVT.Get_SrcAvailableQty('PO', pll.line_location_id) quantity,
pll.cancel_flag,
pl.po_line_id,
pl.unit_meas_lookup_code,
pl.unit_price,
pl.cancel_flag,
pl.line_num,
ph.currency_code,
ph.segment1
INTO l_po_ln_loc_qty,
l_po_ln_loc_cancel_flag,
l_po_line_id,
l_po_ln_uom,
l_po_ln_unit_price,
l_po_ln_cancel_flag,
l_po_ln_num,
l_po_currency,
l_po_number
FROM po_line_locations pll,
po_lines pl,
po_headers ph
WHERE ph.po_header_id = pl.po_header_id
AND pl.po_line_id = pll.po_line_id
AND pll.line_location_id = c_ship_ln_tab(i).ship_line_source_id;
SELECT meaning
INTO l_src_type
FROM fnd_lookup_values_vl l
WHERE l.lookup_type = 'INL_SHIP_LINE_SRC_TYPES'
AND l.lookup_code = 'PO';
SELECT meaning
INTO l_src_type
FROM fnd_lookup_values_vl l
WHERE l.lookup_type = 'INL_SHIP_LINE_SRC_TYPES'
AND l.lookup_code = 'PO';
SELECT ph.segment1,
ph.po_header_id,
ph.vendor_id po_vendor_id,
ph.vendor_site_id po_vendor_site_id,
ph.ship_via_lookup_code po_freight_code,
ph.revision_num po_revision_num,
s.vendor_id simu_vendor_id,
s.vendor_site_id simu_vendor_site_id,
s.freight_code simu_freight_code,
s.parent_table_revision_num simu_revision_num,
s.parent_table_name, -- Bug 14280113
pv.vendor_name,
pvs.vendor_site_code,
pv1.vendor_name,
pvs1.vendor_site_code,
pr.release_num po_release_num, -- Bug 14280113
pr.revision_num po_release_revision_num -- Bug 14280113
INTO l_po_number,
l_po_header_id,
l_po_vendor_id,
l_po_vendor_site_id,
l_po_freight_code,
l_po_revision_num,
l_simu_vendor_id,
l_simu_vendor_site_id,
l_simu_freight_code,
l_simu_revision_num,
l_parent_table_name, -- Bug 14280113
l_po_vendor_name,
l_po_vendor_site_code,
l_simu_vendor_name,
l_simu_vendor_site_code,
l_po_release_num, -- Bug 14280113
l_po_release_revision_num -- Bug 14280113
FROM po_vendor_sites pvs1,
po_vendors pv1,
po_vendor_sites pvs,
po_vendors pv,
po_releases pr, -- Bug 14280113
po_headers_all ph,
inl_simulations s
WHERE pr.po_header_id (+) = ph.po_header_id -- Bug 14280113
AND pvs1.vendor_site_id = s.vendor_site_id
AND pv1.vendor_id = s.vendor_id
AND pvs.vendor_site_id = ph.vendor_site_id
AND pv.vendor_id = ph.vendor_id
-- Bug 14280113
--AND ph.po_header_id = s.parent_table_id
AND ((s.parent_table_name = 'PO_HEADERS'
AND s.parent_table_id = ph.po_header_id)
OR (s.parent_table_name = 'PO_RELEASES'
AND s.parent_table_id = pr.po_release_id))
AND s.simulation_id = p_simulation_id;
SELECT ph.vendor_id,
ph.vendor_site_id,
pl.item_id,
pll.ship_to_organization_id
FROM po_headers_all ph,
po_lines_all pl,
po_line_locations_all pll
WHERE ph.po_header_id = pl.po_header_id
AND pl.po_line_id = pll.po_line_id
AND ph.po_header_id = p_po_header_id;