[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
ship_header_int_id ,
group_id ,
transaction_type ,
interface_source_code ,
interface_source_table ,
interface_source_line_id ,
validation_flag ,
ship_num ,
ship_date ,
ship_type_id ,
ship_type_code ,
-- Bug#13582437 problem in 12.2
NULL legal_entity_id ,
-- legal_entity_name ,
organization_id ,
organization_code ,
location_id ,
location_code ,
-- Bug#13056434 problem in 12.2
-- org_id ,
NULL org_id ,
-- Bug#13056434 problem in 12.2
taxation_country ,
document_sub_type ,
ship_header_id ,
last_task_code ,
DECODE(h.transaction_type,'CREATE', NVL(h.rcv_enabled_flag,'Y'), h.rcv_enabled_flag) rcv_enabled_flag, -- dependence
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
NULL user_defined_ship_num_code, -- from inl_parameters (AUTOMATIC or MANUAL)
NULL manual_ship_num_type, -- from inl_parameters (NUMERIC or ALPHA)
NULL LCM_FLOW, -- can be AAS=as a service PR= pre receiving
NULL dflt_country
FROM inl_ship_headers_int h
WHERE h.request_id = l_fnd_conc_request_id_int
ORDER BY h.organization_id -- Bug 16310024
;
SELECT
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 ,
-- Bug#13056434 problem in 12.2
-- org_id ,
NULL org_id ,
-- Bug#13056434 problem in 12.2
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 ,
attribute_category_lg ,
attribute1_lg ,
attribute2_lg ,
attribute3_lg ,
attribute4_lg ,
attribute5_lg ,
attribute6_lg ,
attribute7_lg ,
attribute8_lg ,
attribute9_lg ,
attribute10_lg ,
attribute11_lg ,
attribute12_lg ,
attribute13_lg ,
attribute14_lg ,
attribute15_lg ,
attribute_category_sl ,
attribute1_sl ,
attribute2_sl ,
attribute3_sl ,
attribute4_sl ,
attribute5_sl ,
attribute6_sl ,
attribute7_sl ,
attribute8_sl ,
attribute9_sl ,
attribute10_sl ,
attribute11_sl ,
attribute12_sl ,
attribute13_sl ,
attribute14_sl ,
attribute15_sl ,
ship_line_group_num ,
inventory_item_name ,
ship_line_group_id
FROM inl_ship_lines_int
WHERE ship_header_int_id = pc_ship_header_int_id
ORDER BY
ship_line_group_reference,
ship_line_src_type_code,
ship_header_id,
party_id,
party_site_id,
source_organization_id,
ship_line_int_id
;
UPDATE inl_ship_lines_int
SET processing_status_code = 'ERROR' ,
request_id = L_FND_CONC_REQUEST_ID,
last_updated_by = L_FND_USER_ID ,
last_update_date = SYSDATE ,
last_update_login = L_FND_LOGIN_ID ,
program_id = L_FND_CONC_PROGRAM_ID,
program_update_date = SYSDATE ,
program_application_id = L_FND_PROG_APPL_ID
WHERE ship_line_int_id = p_parent_table_id
AND processing_status_code <> 'ERROR';
SELECT ship_header_int_id
INTO l_ship_header_int_id
FROM inl_ship_lines_int
WHERE ship_line_int_id = p_parent_table_id;
UPDATE inl_matches_int
SET processing_status_code = 'ERROR' ,
request_id = L_FND_CONC_REQUEST_ID,
last_updated_by = L_FND_USER_ID ,
last_update_date = SYSDATE ,
last_update_login = L_FND_LOGIN_ID ,
program_id = L_FND_CONC_PROGRAM_ID,
program_update_date = SYSDATE ,
program_application_id = L_FND_PROG_APPL_ID
WHERE match_int_id = p_parent_table_id
AND processing_status_code <> 'ERROR';
UPDATE inl_ship_headers_int
SET processing_status_code = 'ERROR' ,
request_id = L_FND_CONC_REQUEST_ID,
last_updated_by = L_FND_USER_ID ,
last_update_date = SYSDATE ,
last_update_login = L_FND_LOGIN_ID ,
program_id = L_FND_CONC_PROGRAM_ID,
program_update_date = SYSDATE ,
program_application_id = L_FND_PROG_APPL_ID
WHERE ship_header_int_id = NVL(l_ship_header_int_id, p_parent_table_id)
AND processing_status_code <> 'ERROR';
l_debug_info := 'Insert detailed error message into Interface Error table.';
INSERT INTO inl_interface_errors (
interface_error_id, /*01*/
parent_table_name, /*02*/
parent_table_id, /*03*/
column_name, /*04*/
column_value, /*05*/
processing_date, /*06*/
error_message_name, /*07*/
error_message, /*08*/
token1_name, /*09*/
token1_value, /*10*/
token2_name, /*11*/
token2_value, /*12*/
token3_name, /*13*/
token3_value, /*14*/
token4_name, /*15*/
token4_value, /*16*/
token5_name, /*17*/
token5_value, /*18*/
token6_name, /*19*/
token6_value, /*20*/
created_by, /*21*/
creation_date, /*22*/
last_updated_by, /*23*/
last_update_date, /*24*/
last_update_login, /*25*/
program_id, /*26*/
program_application_id, /*27*/
program_update_date, /*28*/
request_id /*29*/
)
VALUES (
inl_interface_errors_s.NEXTVAL,/*01*/
p_parent_table_name, /*02*/
p_parent_table_id, /*03*/
p_column_name, /*04*/
p_column_value, /*05*/
SYSDATE, /*06*/
p_error_message_name, /*07*/
l_error_message, /*08*/
p_token1_name, /*09*/
SUBSTR(p_token1_value,0,200), /*10*/
p_token2_name, /*11*/
p_token2_value, /*12*/
p_token3_name, /*13*/
p_token3_value, /*14*/
p_token4_name, /*15*/
p_token4_value, /*16*/
p_token5_name, /*17*/
p_token5_value, /*18*/
p_token6_name, /*19*/
p_token6_value, /*20*/
L_FND_USER_ID, /*21*/
SYSDATE, /*22*/
L_FND_USER_ID, /*23*/
SYSDATE, /*24*/
L_FND_USER_ID, /*25*/
L_FND_CONC_PROGRAM_ID, /*26*/
L_FND_PROG_APPL_ID, /*27*/
SYSDATE, /*28*/
L_FND_CONC_REQUEST_ID /*29*/
);
l_debug_info := 'Delete Errors from previous validation.';
DELETE
FROM inl_interface_errors
WHERE parent_table_name = p_parent_table_name
AND parent_table_id = p_parent_table_id;
SELECT party_id
INTO l_return_value
FROM hz_parties
WHERE PARTY_NUMBER = p_party_number;
SELECT party_site_id
INTO l_return_value
FROM hz_party_sites
WHERE party_site_number = p_party_site_number;
/* Verifyed at Validate_HdrUpdateTrxType
IF x_ship_header_id IS NOT NULL THEN
x_ship_num := NULL;
ELSIF x_ship_num IS NOT NULL AND p_transaction_type = 'UPDATE' THEN
BEGIN
SELECT sh.ship_header_id
INTO x_ship_header_id
FROM inl_ship_headers_all sh --Bug#10381495
WHERE sh.ship_num = x_ship_num
AND sh.organization_id = p_organization_id;
SELECT COUNT(1)
INTO l_count_ship_num_exist
FROM inl_ship_headers_all sh --Bug#10381495
WHERE sh.ship_num = p_ship_num
AND sh.organization_id = p_organization_id;
SELECT st.ship_type_id
INTO x_ship_type_id
FROM inl_ship_types_b st
WHERE st.ship_type_code = x_ship_type_code;
SELECT
st.ship_type_code,
TRUNC(st.active_from_date),
TRUNC(st.active_to_date),
( SELECT COUNT(1)
FROM inl_alwd_line_types alt
WHERE alt.parent_table_id = st.ship_type_id
AND alt.parent_table_name = 'INL_SHIP_TYPES'
),
( SELECT COUNT(1)
FROM inl_alwd_source_types alst
WHERE alst.ship_type_id = st.ship_type_id
)
INTO
x_ship_type_code,
l_active_from_date,
l_active_to_date,
l_count_alw_ln_types,
l_count_alw_src_types
FROM inl_ship_types_vl st
WHERE (x_ship_type_id IS NOT NULL
AND st.ship_type_id = x_ship_type_id)
OR st.ship_type_code = x_ship_type_code
;
SELECT party_id
INTO x_party_id
FROM hz_parties
WHERE party_number = x_party_number;
SELECT
party_id,
party_number,
(SELECT COUNT(1)
FROM hz_party_usg_assignments ua
WHERE ua.party_id = hp.party_id
AND(UPPER(hp.party_type)
IN (SELECT
UPPER(cfopt.party_type_code)
FROM
inl_ship_types_vl cfot,
inl_alwd_party_types cfopt
WHERE
cfopt.parent_table_id = cfot.ship_type_id
AND cfopt.parent_table_name = 'INL_SHIP_TYPES'
AND cfot.ship_type_id = p_ship_type_id))
AND hp.party_id = hp.party_id),
(SELECT
COUNT(1)
FROM
hz_party_usages_vl pu,
hz_party_usg_assignments ua,
hz_parties hp1
WHERE
pu.party_usage_code = ua.party_usage_code
AND ua.party_id = hp1.party_id
AND UPPER(pu.party_usage_code)
IN
(SELECT UPPER(party_usage_code)
FROM
inl_ship_types_vl cfot,
inl_alwd_party_usages cfopt
WHERE cfopt.parent_table_id = cfot.ship_type_id
AND cfopt.parent_table_name = 'INL_SHIP_TYPES'
AND cfot.ship_type_id = p_ship_type_id)
AND hp1.party_id = hp.party_id)
INTO
x_party_id,
x_party_number,
l_count_alw_party_type,
l_count_alw_party_usg
FROM hz_parties hp
WHERE
(x_party_id IS NOT NULL
AND party_id = x_party_id)
OR party_number = x_party_number;
SELECT hps.party_site_id
INTO x_party_site_id
FROM hz_party_sites hps
WHERE party_site_number = x_party_site_number;
SELECT hps.party_site_id,
hps.party_site_number,
(SELECT COUNT(1)
FROM hz_party_sites hps1
WHERE hps1.party_id = p_party_id
AND hps1.party_site_id = hps.party_site_id),
(SELECT COUNT(1)
FROM hz_party_sites hps2,
hz_locations hl,
fnd_territories_vl ftv,
inl_ship_types_vl shv
WHERE hps.location_id = hl.location_id
AND ftv.territory_code = hl.country
AND shv.ship_type_id = p_ship_type_id
AND hps.party_id = p_party_id
AND ((shv.trd_pties_alwd_code = 1 AND hl.country = p_dflt_country)
OR (shv.trd_pties_alwd_code = 2 AND hl.country <> p_dflt_country)
OR (shv.trd_pties_alwd_code = 3))
AND hps.party_site_id = hps.party_site_id)
INTO
x_party_site_id,
x_party_site_number,
l_count_belongs_party,
l_count_alw_party
FROM hz_party_sites hps
WHERE ( x_party_site_id IS NOT null
AND party_site_id = x_party_site_id)
OR party_site_number = x_party_site_number;
SELECT ood.organization_id
INTO x_src_organization_id
FROM org_organization_definitions ood
WHERE ood.organization_code = x_src_organization_code;
SELECT
ood.organization_code,
ood.organization_id
INTO
x_src_organization_code,
x_src_organization_id
FROM
org_organization_definitions ood
WHERE (
x_src_organization_id IS NOT NULL
AND ood.organization_id = x_src_organization_id
)
OR
ood.organization_code = x_src_organization_code
;
SELECT COUNT(1)
INTO l_valid_ln_group
FROM inl_ship_line_groups slg
WHERE slg.ship_line_group_id = x_ship_line_group_id;
SELECT slg.ship_line_group_id
INTO x_ship_line_group_id
FROM inl_ship_line_groups slg
WHERE slg.ship_header_id = p_ship_header_id
AND slg.ship_line_group_num = x_ship_line_group_num;
SELECT sl.ship_line_group_id, slg.ship_line_group_num
INTO x_ship_line_group_id,
x_ship_line_group_num
FROM inl_ship_lines_all sl, --Bug#10381495
inl_ship_line_groups slg
WHERE sl.ship_header_id = p_ship_header_id
AND sl.ship_line_id = p_ship_line_id
AND sl.ship_line_group_id = slg.ship_line_group_id
;
SELECT COUNT (1)
INTO l_count_ship_ln_num_exist
FROM inl_ship_lines_all sl --Bug#10381495
WHERE sl.ship_line_id = x_ship_line_id;
SELECT sl.ship_line_id
INTO x_ship_line_id
FROM inl_ship_lines_all sl --Bug#10381495
WHERE sl.ship_header_id = p_ship_header_id
AND sl.ship_line_group_id = p_ship_line_group_id
AND sl.ship_line_num = x_ship_line_num;
SELECT slt.ship_line_type_id,
slt.dflt_landed_cost_flag, -- # Bug 9866323
slt.dflt_allocation_enabled_flag -- # Bug 9866323
INTO x_ship_line_type_id,
x_landed_cost_flag,
x_alloc_enabled_flag
FROM inl_ship_line_types_b slt
WHERE (
x_ship_line_type_id IS NOT NULL
AND slt.ship_line_type_id = x_ship_line_type_id
) OR
slt.ship_line_type_code = x_ship_line_type_code;
SELECT
slt.ship_line_type_code,
slt.active_from_date,
slt.active_to_date,
(SELECT COUNT(1)
FROM inl_alwd_line_types sltallow
WHERE sltallow.ship_line_type_id = slt.ship_line_type_id
AND sltallow.parent_table_name = 'INL_SHIP_TYPES'
AND sltallow.parent_table_id = p_ship_type_id),
slt.dflt_landed_cost_flag, -- # Bug 9866323
slt.dflt_allocation_enabled_flag -- # Bug 9866323
INTO
x_ship_line_type_code,
l_active_from_date,
l_active_to_date,
l_count_alw_ship_ln,
x_landed_cost_flag,
x_alloc_enabled_flag
FROM inl_ship_line_types_b slt
WHERE (x_ship_line_type_id IS NOT NULL
AND slt.ship_line_type_id = x_ship_line_type_id)
OR slt.ship_line_type_code = x_ship_line_type_code;
SELECT COUNT(1)
INTO l_count_alw_src_type
FROM fnd_lookups fl
WHERE fl.lookup_type = 'INL_SHIP_LINE_SRC_TYPES'
AND fl.lookup_code = p_ship_ln_src_tp_code;
SELECT gl.currency_code
INTO l_func_currency_code
FROM gl_sets_of_books gl,
financials_system_params_all fsp --Bug#10381495
WHERE gl.set_of_books_id = fsp.set_of_books_id
AND org_id = p_org_id;
SELECT COUNT(1)
INTO l_count_curr_code_valid
FROM fnd_currencies fc
WHERE fc.currency_code = p_currency_code;
SELECT COUNT(1)
INTO l_count_conv_tp_valid
FROM gl_daily_conversion_types dct
WHERE dct.conversion_type = p_curr_conv_type;
SELECT conversion_rate
INTO x_curr_conv_rate
FROM gl_daily_rates
WHERE from_currency = p_currency_code
AND to_currency = l_func_currency_code
AND conversion_type = p_curr_conv_type
AND TRUNC(conversion_date) = TRUNC(p_curr_conv_date);
l_debug_info := 'No rate has been defined for the selected rate type and rate date';
SELECT
msi.inventory_item_id,
msi.concatenated_segments
INTO x_inv_item_id,
x_inv_item_name
FROM mtl_system_items_kfv msi
WHERE msi.organization_id = p_organization_id
AND (
(x_inv_item_id IS NOT NULL
AND msi.inventory_item_id = x_inv_item_id)
OR msi.concatenated_segments = x_inv_item_name)
;
SELECT COUNT(1)
INTO l_count_txn_uom_valid
FROM mtl_units_of_measure mum
WHERE mum.uom_code = p_txn_uom_code;
SELECT NVL(l_inventory_item_id, sl.inventory_item_id),
NVL(l_txn_uom_code, sl.txn_uom_code),
NVL(l_txn_qty, sl.txn_qty),
NVL(l_txn_unit_price, sl.txn_unit_price)
INTO l_inventory_item_id,
l_txn_uom_code,
l_txn_qty,
l_txn_unit_price
FROM inl_ship_lines_all sl --Bug#10381495
WHERE sl.ship_line_id = p_ship_line_id;
SELECT rti.uom_code, muom.uom_code, pl.unit_price
INTO l_rcv_uom_code, l_po_uom_code, l_po_unit_price
FROM rcv_transactions_interface rti,
po_lines_all pl,
mtl_units_of_measure muom
WHERE muom.unit_of_measure = pl.unit_meas_lookup_code
AND pl.po_line_id = rti.po_line_id
AND interface_transaction_id = p_interface_source_line_id;
SELECT COUNT(1)
INTO l_count_2ary_uom_vld
FROM mtl_units_of_measure mum
WHERE mum.uom_code = x_2ary_uom_code;
SELECT ood.operating_unit
INTO x_org_id
FROM org_organization_definitions ood
WHERE ood.organization_id = x_organization_id;
SELECT ood.organization_id, ood.operating_unit
INTO x_organization_id, x_org_id
FROM org_organization_definitions ood
WHERE ood.organization_code = x_organization_code;
SELECT organization_id
INTO x_organization_id
FROM inl_ship_headers_all
WHERE ship_header_id = p_ship_header_id;
SELECT
ood.legal_entity,
ood.organization_id,
ood.organization_code,
ood.operating_unit,
DECODE(NVL(rp.pre_receive, 'N'),'N','AAS','PR'),
ipa.user_defined_ship_num_code,
ipa.manual_ship_num_type,
DECODE(ipa.organization_id, NULL, 0 , 1)
INTO
x_legal_entity_id,
x_organization_id,
x_organization_code,
x_org_id,
x_LCM_FLOW,
x_user_defined_ship_num_code,
x_manual_ship_num_type,
l_count_org_def_lcm
FROM org_organization_definitions ood,
inl_parameters ipa,
rcv_parameters rp
WHERE
ipa.organization_id (+) = ood.organization_id
AND rp.organization_id (+) = ood.organization_id
AND ((x_organization_id IS NOT NULL
AND ood.organization_id = x_organization_id)
OR
ood.organization_code = x_organization_code)
;
SELECT
hou.location_id,
hl.location_code,
hl.country
INTO
x_location_id,
x_location_code,
x_dflt_country
FROM
hr_organization_units hou,
hr_locations_all hl --Bug#11794483B
WHERE hl.location_id = hou.location_id
AND hou.organization_id = p_organization_id;
SELECT
hl.location_id,
hl.country
INTO
x_location_id,
x_dflt_country
FROM
hr_locations_all hl --Bug#11794483B
WHERE
hl.location_code = x_location_code;
SELECT
hl.location_code,
hl.location_id,
hl.country,
(SELECT COUNT(1)
FROM hr_locations_all hl1 --Bug#11794483B
WHERE hl1.location_id = hl.location_id
AND hl1.inventory_organization_id = p_organization_id)
INTO
x_location_code,
x_location_id,
x_dflt_country,
l_count_loc_org
FROM
hr_locations_all hl --Bug#11794483B
WHERE
(x_location_id IS NOT NULL
AND hl.location_id = x_location_id)
OR hl.location_code = x_location_code
;
SELECT INV_UTILITIES.inv_check_lcm(pla.item_id,
plla.ship_to_organization_id,
plla.consigned_flag,
NULL,
pha.vendor_id,
pha.vendor_site_id,
plla.line_location_id)
INTO l_lcm_flag
FROM po_headers_all pha
, po_lines_all pla
, po_line_locations_all plla
WHERE pha.po_header_id = pla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND pha.po_header_id = plla.po_header_id
AND plla.line_location_id = x_ship_ln_int_lst(l_ship_ln_int_idx).ship_line_source_id;
SELECT DISTINCT(sli.ship_line_src_type_code)
INTO l_ship_line_src_type_code
FROM inl_ship_lines_int sli
WHERE sli.ship_header_int_id = p_ship_header_int_id;
FUNCTION Validate_HdrUpdateTrxType(
p_ship_header_int_id IN NUMBER,
p_organization_id IN NUMBER,
p_organization_code IN VARCHAR2,
p_ship_type_id IN NUMBER,
p_ship_type_code IN VARCHAR2,
p_location_id IN NUMBER,
p_location_code IN VARCHAR2,
p_rcv_enabled_flag IN VARCHAR2,
x_ship_num IN OUT NOCOPY VARCHAR2,
x_ship_header_id IN OUT NOCOPY NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) RETURN BOOLEAN IS
l_program_name CONSTANT VARCHAR2(30) := 'Validate_HdrUpdateTrxType';
l_debug_info := 'Check columns for the UPDATE transaction type';
SELECT
sh.ship_status_code
INTO
l_ship_status_code
FROM inl_ship_headers_all sh --Bug#10381495
WHERE sh.ship_header_id = x_ship_header_id;
p_column_value => 'UPDATE',
p_error_message_name => 'INL_ERR_OI_CHK_SHIP_HEADER_ID',
p_token1_name => 'NAME',
p_token1_value => 'INL_SHIP_HEADERS_INT',
p_token2_name => 'ID',
p_token2_value => p_ship_header_int_id,
x_return_status => l_return_status);
p_column_value => 'UPDATE',
p_error_message_name => 'INL_ERR_TRX_TP_ID_NULL',
p_token1_name => 'COLUMN',
p_token1_value => 'SHIP_HEADER_ID OR SHIP_NUM+ORG',
p_token2_name => 'ID_NAME',
p_token2_value => 'INL_SHIP_HEADERS_INT',
p_token3_name => 'ID_VAL',
p_token3_value => p_ship_header_int_id,
x_return_status => l_return_status) ;
SELECT
sh.ship_status_code,
sh.ship_header_id
INTO
l_ship_status_code,
x_ship_header_id
FROM inl_ship_headers_all sh --Bug#10381495
WHERE sh.ship_num = x_ship_num
AND organization_id = l_organization_id;
p_column_value => 'UPDATE',
p_error_message_name => 'INL_ERR_NO_SHIP_NUM',
p_token1_name => 'SHIP_NUM',
p_token1_value => x_ship_num,
p_token2_name => 'ORGANIZATION_ID',
p_token2_value => l_organization_id,
x_return_status => l_return_status);
l_debug_info := 'COMPLETED Shipments cannot be updated';
p_column_value => 'UPDATE',
p_error_message_name => 'INL_ERR_OI_CHK_TRX_TP_STA_INVL',
p_token1_name => 'SHIP_HEADER_INT_ID',
p_token1_value => p_ship_header_int_id,
x_return_status => l_return_status) ;
p_column_value => 'UPDATE',
p_error_message_name => 'INL_ERR_TRX_TP_ID_NNULL',
p_token1_name => 'COLUMN',
p_token1_value => 'SHIP_TYPE_ID AND SHIP_TYPE_CODE',
p_token2_name => 'ID_NAME',
p_token2_value => 'INL_SHIP_HEADERS_INT',
p_token3_name => 'ID_VAL',
p_token3_value => p_ship_header_int_id,
x_return_status => l_return_status) ;
p_column_value => 'UPDATE',
p_error_message_name => 'INL_ERR_TRX_TP_ID_NNULL',
p_token1_name => 'COLUMN',
p_token1_value => 'LOCATION_ID AND LOCATION_CODE',
p_token2_name => 'ID_NAME',
p_token2_value => 'INL_SHIP_HEADERS_INT',
p_token3_name => 'ID_VAL',
p_token3_value => p_ship_header_int_id,
x_return_status => l_return_status) ;
p_column_value => 'UPDATE',
p_error_message_name => 'INL_ERR_TRX_TP_ID_NNULL',
p_token1_name => 'COLUMN',
p_token1_value => 'RCV_ENABLED_FLAG',
p_token2_name => 'ID_NAME',
p_token2_value => 'INL_SHIP_HEADERS_INT',
p_token3_name => 'ID_VAL',
p_token3_value => p_ship_header_int_id,
x_return_status => l_return_status) ;
END Validate_HdrUpdateTrxType;
FUNCTION Validate_LnUpdateTrxType(
p_validation_flag IN VARCHAR2,
p_ship_header_id IN NUMBER,
p_organization_id IN NUMBER,
p_interface_source_code IN VARCHAR2,
x_ship_ln_int_lst IN OUT NOCOPY ship_ln_int_list_type,
x_return_status OUT NOCOPY VARCHAR2
)RETURN BOOLEAN IS
l_program_name CONSTANT VARCHAR2(30) := 'Validate_LnUpdateTrxType';
l_debug_info := 'Check columns for the UPDATE transaction type';
l_debug_info := 'Check for columns that should be populated or null for UPDATE.';
p_column_value => 'UPDATE',
p_error_message_name => 'INL_ERR_TRX_TP_ID_NULL',
p_token1_name => 'COLUMN',
p_token1_value => 'SHIP_LINE_GROUP_ID OR SHIP_LINE_GROUP_NUM',
p_token2_name => 'ID_NAME',
p_token2_value => 'INL_SHIP_LINES_INT',
p_token3_name => 'ID_VAL',
p_token3_value => l_ship_line_int_id,
x_return_status => l_return_status) ;
p_column_value => 'UPDATE',
p_error_message_name => 'INL_ERR_TRX_TP_ID_NULL',
p_token1_name => 'COLUMN',
p_token1_value => 'SHIP_LINE_ID OR SHIP_LINE_NUM',
p_token2_name => 'ID_NAME',
p_token2_value => 'INL_SHIP_LINES_INT',
p_token3_name => 'ID_VAL',
p_token3_value => l_ship_line_int_id,
x_return_status => l_return_status) ;
p_column_value => 'UPDATE',
p_error_message_name => 'INL_ERR_TRX_TP_ID_NNULL',
p_token1_name => 'COLUMN',
p_token1_value => 'PARTY_ID AND PARTY_NUMBER',
p_token2_name => 'ID_NAME',
p_token2_value => 'INL_SHIP_LINES_INT',
p_token3_name => 'ID_VAL',
p_token3_value => l_ship_line_int_id,
x_return_status => l_return_status) ;
p_column_value => 'UPDATE',
p_error_message_name => 'INL_ERR_TRX_TP_ID_NNULL',
p_token1_name => 'COLUMN',
p_token1_value => 'PARTY_SITE_ID AND PARTY_SITE_NUMBER',
p_token2_name => 'ID_NAME',
p_token2_value => 'INL_SHIP_LINES_INT',
p_token3_name => 'ID_VAL',
p_token3_value => l_ship_line_int_id,
x_return_status => l_return_status) ;
p_column_value => 'UPDATE',
p_error_message_name => 'INL_ERR_TRX_TP_ID_NNULL',
p_token1_name => 'COLUMN',
p_token1_value => 'SOURCE_ORGANIZATION_ID AND SOURCE_ORGANIZATION_CODE',
p_token2_name => 'ID_NAME',
p_token2_value => 'INL_SHIP_LINES_INT',
p_token3_name => 'ID_VAL',
p_token3_value => l_ship_line_int_id,
x_return_status => l_return_status) ;
p_column_value => 'UPDATE',
p_error_message_name => 'INL_ERR_TRX_TP_ID_NNULL',
p_token1_name => 'COLUMN',
p_token1_value => 'SHIP_LINE_TYPE_ID AND SHIP_LINE_TYPE_CODE',
p_token2_name => 'ID_NAME',
p_token2_value => 'INL_SHIP_HEADERS_INT',
p_token3_name => 'ID_VAL',
p_token3_value => l_ship_line_int_id,
x_return_status => l_return_status) ;
p_column_value => 'UPDATE',
p_error_message_name => 'INL_ERR_TRX_TP_ID_NNULL',
p_token1_name => 'COLUMN',
p_token1_value => 'SHIP_LINE_SRC_TYPE_CODE',
p_token2_name => 'ID_NAME',
p_token2_value => 'INL_SHIP_LINES_INT',
p_token3_name => 'ID_VAL',
p_token3_value => l_ship_line_int_id,
x_return_status => l_return_status) ;
p_column_value => 'UPDATE',
p_error_message_name => 'INL_ERR_TRX_TP_ID_NNULL',
p_token1_name => 'COLUMN',
p_token1_value => 'SHIP_LINE_SOURCE_ID',
p_token2_name => 'ID_NAME',
p_token2_value => 'INL_SHIP_LINES_INT',
p_token3_name => 'ID_VAL',
p_token3_value => l_ship_line_int_id,
x_return_status => l_return_status) ;
p_column_value => 'UPDATE',
p_error_message_name => 'INL_ERR_TRX_TP_ID_NNULL',
p_token1_name => 'COLUMN',
p_token1_value => 'CURRENCY_CODE',
p_token2_name => 'ID_NAME',
p_token2_value => 'INL_SHIP_LINES_INT',
p_token3_name => 'ID_VAL',
p_token3_value => l_ship_line_int_id,
x_return_status => l_return_status) ;
p_column_value => 'UPDATE',
p_error_message_name => 'INL_ERR_TRX_TP_ID_NNULL',
p_token1_name => 'COLUMN',
p_token1_value => 'CURRENCY_CONVERSION_TYPE',
p_token2_name => 'ID_NAME',
p_token2_value => 'INL_SHIP_LINES_INT',
p_token3_name => 'ID_VAL',
p_token3_value => l_ship_line_int_id,
x_return_status => l_return_status) ;
p_column_value => 'UPDATE',
p_error_message_name => 'INL_ERR_TRX_TP_ID_NNULL',
p_token1_name => 'COLUMN',
p_token1_value => 'CURRENCY_CONVERSION_DATE',
p_token2_name => 'ID_NAME',
p_token2_value => 'INL_SHIP_LINES_INT',
p_token3_name => 'ID_VAL',
p_token3_value => l_ship_line_int_id,
x_return_status => l_return_status) ;
p_column_value => 'UPDATE',
p_error_message_name => 'INL_ERR_TRX_TP_ID_NNULL',
p_token1_name => 'COLUMN',
p_token1_value => 'CURRENCY_CONVERSION_RATE',
p_token2_name => 'ID_NAME',
p_token2_value => 'INL_SHIP_LINES_INT',
p_token3_name => 'ID_VAL',
p_token3_value => l_ship_line_int_id,
x_return_status => l_return_status) ;
p_column_value => 'UPDATE',
p_error_message_name => 'INL_ERR_TRX_TP_ID_NNULL',
p_token1_name => 'COLUMN',
p_token1_value => 'INVENTORY_ITEM_ID AND INVENTORY_ITEM_NAME',
p_token2_name => 'ID_NAME',
p_token2_value => 'INL_SHIP_LINES_INT',
p_token3_name => 'ID_VAL',
p_token3_value => l_ship_line_int_id,
x_return_status => l_return_status) ;
END Validate_LnUpdateTrxType;
SELECT COUNT(1)
INTO l_count_ln_proc_error
FROM inl_ship_lines_int sli
WHERE sli.ship_header_int_id = p_ship_header_int_id
AND sli.processing_status_code = 'ERROR';
SELECT COUNT(DISTINCT(sli.ship_line_src_type_code))
INTO l_count_ln_src_tp_code
FROM inl_ship_lines_int sli
WHERE sli.ship_header_int_id = p_ship_header_int_id;
ELSIF p_transaction_type = 'UPDATE' THEN
l_response :=
validate_HdrUpdateTrxType(
p_ship_header_int_id => p_ship_header_int_id,
p_organization_id => p_organization_id ,
p_organization_code => p_organization_code ,
p_ship_type_id => p_ship_type_id ,
p_ship_type_code => p_ship_type_code ,
p_location_id => p_location_id ,
p_location_code => p_location_code ,
p_rcv_enabled_flag => p_rcv_enabled_flag ,
x_ship_num => x_ship_num ,
x_ship_header_id => x_ship_header_id ,
x_return_status => l_return_status
);
PROCEDURE Delete_Ship (
p_ship_header_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_program_name CONSTANT VARCHAR2(100) := 'Delete_Ship';
l_debug_info := 'Delete from inl_ship_holds';
DELETE FROM inl_ship_holds
WHERE ship_header_id = p_ship_header_id;
l_debug_info := 'Delete inl_match_amounts';
DELETE FROM inl_match_amounts ma
WHERE EXISTS (SELECT 1
FROM inl_matches m
WHERE m.ship_header_id = p_ship_header_id
AND ma.match_amount_id = m.match_amount_id
)
AND NOT EXISTS (SELECT 1
FROM inl_matches m
WHERE m.ship_header_id <> p_ship_header_id
AND ma.match_amount_id = m.match_amount_id
)
;
l_debug_info := 'Delete inl_matches';
DELETE FROM inl_matches m
WHERE m.ship_header_id = p_ship_header_id;
l_debug_info := 'Delete from inl_allocations';
DELETE FROM inl_allocations
WHERE ship_header_id = p_ship_header_id;
l_debug_info := 'Delete from inl_tax_lines';
DELETE FROM inl_tax_lines tl
WHERE
tl.ship_header_id = p_ship_header_id
OR --Bug#9279355
tl.ship_header_id IS NULL
AND
(
EXISTS (
SELECT 1
FROM inl_associations assoc
WHERE assoc.from_parent_table_name = 'INL_TAX_LINES'
AND (assoc.from_parent_table_id = tl.tax_line_id
OR assoc.from_parent_table_id = tl.parent_tax_line_id)
AND assoc.ship_header_id = p_ship_header_id
)
AND
NOT EXISTS (
SELECT 1
FROM inl_associations assoc
WHERE assoc.from_parent_table_name = 'INL_TAX_LINES'
AND (assoc.from_parent_table_id = tl.tax_line_id
OR assoc.from_parent_table_id = tl.parent_tax_line_id)
AND assoc.ship_header_id <> p_ship_header_id
)
)
;
l_debug_info := 'Delete from inl_charge_lines';
DELETE
FROM inl_charge_lines cl
WHERE --Bug#9279355
(
EXISTS (
SELECT 1
FROM inl_associations assoc
WHERE assoc.from_parent_table_name = 'INL_CHARGE_LINES'
AND (assoc.from_parent_table_id = cl.charge_line_id
OR assoc.from_parent_table_id = cl.parent_charge_line_id)
AND assoc.ship_header_id = p_ship_header_id
)
AND
NOT EXISTS (
SELECT 1
FROM inl_associations assoc
WHERE assoc.from_parent_table_name = 'INL_CHARGE_LINES'
AND (assoc.from_parent_table_id = cl.charge_line_id
OR assoc.from_parent_table_id = cl.parent_charge_line_id)
AND assoc.ship_header_id <> p_ship_header_id
)
)
;
l_debug_info := 'Delete from inl_associations';
DELETE FROM inl_associations
WHERE ship_header_id = p_ship_header_id;
l_debug_info := 'Delete from inl_ship_lines_all';
DELETE FROM inl_ship_lines_all
WHERE ship_header_id = p_ship_header_id;
l_debug_info := 'Delete from inl_ship_line_groups';
DELETE FROM inl_ship_line_groups
WHERE ship_header_id = p_ship_header_id;
l_debug_info := 'Delete from inl_ship_headers_all';
DELETE FROM inl_ship_headers_all
WHERE ship_header_id = p_ship_header_id;
END Delete_Ship;
l_debug_info := 'Is other group: Select Data from group';
SELECT ship_line_group_id, /* 01 */
ship_line_group_reference, /* 02 */
ship_header_id, /* 03 */
-- ship_line_group_num, /* 04 */
src_type_code, /* 05 */
party_id, /* 06 */
party_site_id, /* 07 */
source_organization_id /* 08 */
INTO
current_SLnGr_rec.ship_line_group_id, /* 01 */
current_SLnGr_rec.ship_line_group_reference,/* 02 */
current_SLnGr_rec.ship_header_id, /* 03 */
-- current_SLnGr_rec.ship_line_group_num, /* 04 */
current_SLnGr_rec.src_type_code, /* 05 */
current_SLnGr_rec.party_id, /* 06 */
current_SLnGr_rec.party_site_id, /* 07 */
current_SLnGr_rec.source_organization_id /* 08 */
FROM inl_ship_line_groups
WHERE ship_header_id = x_ship_ln_int_rec.ship_header_id
AND NVL(src_type_code, L_FND_MISS_CHAR) = NVL(x_ship_ln_int_rec.ship_line_src_type_code, L_FND_MISS_CHAR)
AND party_id = x_ship_ln_int_rec.party_id
AND party_site_id = x_ship_ln_int_rec.party_site_id
AND source_organization_id = x_ship_ln_int_rec.source_organization_id
AND NVL(ship_line_group_reference, L_FND_MISS_NUM)= NVL(x_ship_ln_int_rec.ship_line_group_reference, L_FND_MISS_NUM) ;
SELECT inl_ship_line_groups_s.NEXTVAL
INTO current_SLnGr_rec.ship_line_group_id
FROM dual;
SELECT MAX(ship_line_group_num)
INTO current_SLnGr_rec.ship_line_group_num
FROM inl_ship_line_groups
WHERE ship_header_id = x_ship_ln_int_rec.ship_header_id;
l_debug_info := 'Inserting inl_ship_line_groups';
INSERT INTO inl_ship_line_groups (
ship_line_group_id, --01
ship_line_group_reference, --02
ship_header_id, --03
ship_line_group_num, --04
src_type_code, --05
party_id, --06
party_site_id, --07
source_organization_id, --08
ship_line_int_id, --09
interface_source_table, --10
interface_source_line_id, --11
created_by, --12
creation_date, --13
last_updated_by, --14
last_update_date, --15
last_update_login, --16
program_id, --17
program_update_date, --18
program_application_id, --19
request_id, --20
attribute_category, --21
attribute1, --22
attribute2, --23
attribute3, --24
attribute4, --25
attribute5, --26
attribute6, --27
attribute7, --28
attribute8, --29
attribute9, --30
attribute10, --31
attribute11, --32
attribute12, --33
attribute13, --34
attribute14, --35
attribute15 --36
)
VALUES
(
x_ship_ln_int_rec.ship_line_group_id , --01
x_ship_ln_int_rec.ship_line_group_reference, --02
x_ship_ln_int_rec.ship_header_id , --03
current_SLnGr_rec.ship_line_group_num , --04
current_SLnGr_rec.src_type_code , --05
current_SLnGr_rec.party_id , --06
current_SLnGr_rec.party_site_id , --07
current_SLnGr_rec.source_organization_id, --08
x_ship_ln_int_rec.ship_line_int_id , --09
x_ship_ln_int_rec.interface_source_table , --10
x_ship_ln_int_rec.interface_source_line_id , --11
L_FND_USER_ID , --12
SYSDATE , --13
L_FND_USER_ID , --14
SYSDATE , --15
L_FND_LOGIN_ID , --16
L_FND_CONC_PROGRAM_ID , --17
SYSDATE , --18
L_FND_PROG_APPL_ID , --19
L_FND_CONC_REQUEST_ID , --20
x_ship_ln_int_rec .attribute_category_lg , --21
x_ship_ln_int_rec .attribute1_lg , --22
x_ship_ln_int_rec .attribute2_lg , --23
x_ship_ln_int_rec .attribute3_lg , --24
x_ship_ln_int_rec .attribute4_lg , --25
x_ship_ln_int_rec .attribute5_lg , --26
x_ship_ln_int_rec .attribute6_lg , --27
x_ship_ln_int_rec .attribute7_lg , --28
x_ship_ln_int_rec .attribute8_lg , --29
x_ship_ln_int_rec .attribute9_lg , --30
x_ship_ln_int_rec .attribute10_lg , --31
x_ship_ln_int_rec .attribute11_lg , --32
x_ship_ln_int_rec .attribute12_lg , --33
x_ship_ln_int_rec .attribute13_lg , --34
x_ship_ln_int_rec .attribute14_lg , --35
x_ship_ln_int_rec .attribute15_lg --36
);
ELSIF p_transaction_type = 'UPDATE' THEN
l_debug_info := 'Updating Ship Line Group';
UPDATE inl_ship_line_groups
SET
ship_line_group_reference = NVL(x_ship_ln_int_rec.ship_line_group_reference,ship_line_group_reference),
ship_line_int_id = NVL(x_ship_ln_int_rec.ship_line_int_id ,ship_line_int_id ),
interface_source_table = NVL(x_ship_ln_int_rec.interface_source_table ,interface_source_table ),
interface_source_line_id = NVL(x_ship_ln_int_rec.interface_source_line_id ,interface_source_line_id),
last_updated_by = L_FND_USER_ID,
last_update_date = SYSDATE,
last_update_login = L_FND_LOGIN_ID,
program_id = L_FND_CONC_PROGRAM_ID,
program_update_date = SYSDATE,
program_application_id = L_FND_PROG_APPL_ID,
request_id = L_FND_CONC_REQUEST_ID,
attribute_category = NVL(x_ship_ln_int_rec.attribute_category_lg ,attribute_category ),
attribute1 = NVL(x_ship_ln_int_rec.attribute1_lg ,attribute1 ),
attribute2 = NVL(x_ship_ln_int_rec.attribute2_lg ,attribute2 ),
attribute3 = NVL(x_ship_ln_int_rec.attribute3_lg ,attribute3 ),
attribute4 = NVL(x_ship_ln_int_rec.attribute4_lg ,attribute4 ),
attribute5 = NVL(x_ship_ln_int_rec.attribute5_lg ,attribute5 ),
attribute6 = NVL(x_ship_ln_int_rec.attribute6_lg ,attribute6 ),
attribute7 = NVL(x_ship_ln_int_rec.attribute7_lg ,attribute7 ),
attribute8 = NVL(x_ship_ln_int_rec.attribute8_lg ,attribute8 ),
attribute9 = NVL(x_ship_ln_int_rec.attribute9_lg ,attribute9 ),
attribute10 = NVL(x_ship_ln_int_rec.attribute10_lg ,attribute10 ),
attribute11 = NVL(x_ship_ln_int_rec.attribute11_lg ,attribute11 ),
attribute12 = NVL(x_ship_ln_int_rec.attribute12_lg ,attribute12 ),
attribute13 = NVL(x_ship_ln_int_rec.attribute13_lg ,attribute13 ),
attribute14 = NVL(x_ship_ln_int_rec.attribute14_lg ,attribute14 ),
attribute15 = NVL(x_ship_ln_int_rec.attribute15_lg ,attribute15 )
WHERE ship_line_group_id = x_ship_ln_int_rec.ship_line_group_id
;
SELECT MAX(ship_line_num)
INTO x_ship_ln_int_lst(l_ship_ln_int_idx).ship_line_num
FROM inl_ship_lines_all
WHERE ship_header_id = x_ship_ln_int_lst(l_ship_ln_int_idx).ship_header_id
AND ship_line_group_id = x_ship_ln_int_lst(l_ship_ln_int_idx).ship_line_group_id;
SELECT inl_ship_Lines_all_s.NEXTVAL
INTO x_ship_ln_int_lst(l_ship_ln_int_idx).ship_line_id
FROM dual;
l_debug_info := 'Insert into Shipment Lines table';
INSERT INTO inl_ship_lines_all (
ship_header_id, --01
ship_line_group_id, --02
ship_line_id, --03
ship_line_num, --04
ship_line_type_id, --05
ship_line_src_type_code, --06
ship_line_source_id, --07
parent_ship_line_id, --08
adjustment_num, --09
match_id, --10
currency_code, --12
currency_conversion_type, --13
currency_conversion_date, --14
currency_conversion_rate, --15
inventory_item_id, --16
txn_qty, --17
txn_uom_code, --18
txn_unit_price, --19
primary_qty, --20
primary_uom_code, --21
primary_unit_price, --22
secondary_qty, --23
secondary_uom_code, --24
secondary_unit_price, --25
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
tax_already_calculated_flag, --40
ship_from_party_id, --41
ship_from_party_site_id, --42
ship_to_organization_id, --43
ship_to_location_id, --44
bill_from_party_id, --45
bill_from_party_site_id, --46
bill_to_organization_id, --47
bill_to_location_id, --48
poa_party_id, --49
poa_party_site_id, --50
poo_organization_id, --51
poo_location_id, --52
org_id, --53
ship_line_int_id, --54
interface_source_table, --55
interface_source_line_id, --56
created_by, --57
creation_date, --58
last_updated_by, --59
last_update_date, --60
last_update_login, --61
program_id, --62
program_update_date, --63
program_application_id, --64
request_id, --65
attribute_category, --66
attribute1, --67
attribute2, --68
attribute3, --69
attribute4, --70
attribute5, --71
attribute6, --72
attribute7, --73
attribute8, --74
attribute9, --75
attribute10, --76
attribute11, --77
attribute12, --78
attribute13, --79
attribute14, --80
attribute15 --81
)
VALUES
(
x_ship_ln_int_lst(l_ship_ln_int_idx).ship_header_id, --01
x_ship_ln_int_lst(l_ship_ln_int_idx).ship_line_group_id, --02
x_ship_ln_int_lst(l_ship_ln_int_idx).ship_line_id, --03
x_ship_ln_int_lst(l_ship_ln_int_idx).ship_line_num, --04
x_ship_ln_int_lst(l_ship_ln_int_idx).ship_line_type_id, --05
x_ship_ln_int_lst(l_ship_ln_int_idx).ship_line_src_type_code, --06
x_ship_ln_int_lst(l_ship_ln_int_idx).ship_line_source_id, --07
NULL, --08
0, --09
NULL, --10
x_ship_ln_int_lst(l_ship_ln_int_idx).currency_code, --12
x_ship_ln_int_lst(l_ship_ln_int_idx).currency_conversion_type, --13
x_ship_ln_int_lst(l_ship_ln_int_idx).currency_conversion_date, --14
x_ship_ln_int_lst(l_ship_ln_int_idx).currency_conversion_rate, --15
x_ship_ln_int_lst(l_ship_ln_int_idx).inventory_item_id, --16
x_ship_ln_int_lst(l_ship_ln_int_idx).txn_qty, --17
x_ship_ln_int_lst(l_ship_ln_int_idx).txn_uom_code, --18
x_ship_ln_int_lst(l_ship_ln_int_idx).txn_unit_price, --19
x_ship_ln_int_lst(l_ship_ln_int_idx).primary_qty, --20
x_ship_ln_int_lst(l_ship_ln_int_idx).primary_uom_code, --21
x_ship_ln_int_lst(l_ship_ln_int_idx).primary_unit_price, --22
x_ship_ln_int_lst(l_ship_ln_int_idx).secondary_qty, --23
x_ship_ln_int_lst(l_ship_ln_int_idx).secondary_uom_code, --24
x_ship_ln_int_lst(l_ship_ln_int_idx).secondary_unit_price, --25
x_ship_ln_int_lst(l_ship_ln_int_idx).landed_cost_flag, --30
x_ship_ln_int_lst(l_ship_ln_int_idx).allocation_enabled_flag, --31
x_ship_ln_int_lst(l_ship_ln_int_idx).trx_business_category, --32
x_ship_ln_int_lst(l_ship_ln_int_idx).intended_use, --33
x_ship_ln_int_lst(l_ship_ln_int_idx).product_fiscal_class, --34
x_ship_ln_int_lst(l_ship_ln_int_idx).product_category, --35
x_ship_ln_int_lst(l_ship_ln_int_idx).product_type, --36
x_ship_ln_int_lst(l_ship_ln_int_idx).user_def_fiscal_class, --37
x_ship_ln_int_lst(l_ship_ln_int_idx).tax_classification_code, --38
x_ship_ln_int_lst(l_ship_ln_int_idx).assessable_value, --39
'N', --40
x_ship_ln_int_lst(l_ship_ln_int_idx).ship_from_party_id, --41
x_ship_ln_int_lst(l_ship_ln_int_idx).ship_from_party_site_id, --42
x_ship_ln_int_lst(l_ship_ln_int_idx).ship_to_organization_id, --43
x_ship_ln_int_lst(l_ship_ln_int_idx).ship_to_location_id, --44
x_ship_ln_int_lst(l_ship_ln_int_idx).bill_from_party_id, --45
x_ship_ln_int_lst(l_ship_ln_int_idx).bill_from_party_site_id, --46
x_ship_ln_int_lst(l_ship_ln_int_idx).bill_to_organization_id, --47
x_ship_ln_int_lst(l_ship_ln_int_idx).bill_to_location_id, --48
x_ship_ln_int_lst(l_ship_ln_int_idx).poa_party_id, --49
x_ship_ln_int_lst(l_ship_ln_int_idx).poa_party_site_id, --50
x_ship_ln_int_lst(l_ship_ln_int_idx).poo_organization_id, --51
x_ship_ln_int_lst(l_ship_ln_int_idx).poo_location_id, --52
x_ship_ln_int_lst(l_ship_ln_int_idx).org_id, --53
x_ship_ln_int_lst(l_ship_ln_int_idx).ship_line_int_id, --54
x_ship_ln_int_lst(l_ship_ln_int_idx).interface_source_table, --55
x_ship_ln_int_lst(l_ship_ln_int_idx).interface_source_line_id, --56
L_FND_USER_ID, --57
SYSDATE, --58
L_FND_USER_ID, --59
SYSDATE, --60
L_FND_LOGIN_ID, --61
L_FND_CONC_PROGRAM_ID, --62
SYSDATE, --63
L_FND_PROG_APPL_ID, --64
L_FND_CONC_REQUEST_ID, --65
x_ship_ln_int_lst(l_ship_ln_int_idx).attribute_category_sl, --66
x_ship_ln_int_lst(l_ship_ln_int_idx).attribute1_sl, --67
x_ship_ln_int_lst(l_ship_ln_int_idx).attribute2_sl, --68
x_ship_ln_int_lst(l_ship_ln_int_idx).attribute3_sl, --69
x_ship_ln_int_lst(l_ship_ln_int_idx).attribute4_sl, --70
x_ship_ln_int_lst(l_ship_ln_int_idx).attribute5_sl, --71
x_ship_ln_int_lst(l_ship_ln_int_idx).attribute6_sl, --72
x_ship_ln_int_lst(l_ship_ln_int_idx).attribute7_sl, --73
x_ship_ln_int_lst(l_ship_ln_int_idx).attribute8_sl, --74
x_ship_ln_int_lst(l_ship_ln_int_idx).attribute9_sl, --75
x_ship_ln_int_lst(l_ship_ln_int_idx).attribute10_sl, --76
x_ship_ln_int_lst(l_ship_ln_int_idx).attribute11_sl, --77
x_ship_ln_int_lst(l_ship_ln_int_idx).attribute12_sl, --78
x_ship_ln_int_lst(l_ship_ln_int_idx).attribute13_sl, --79
x_ship_ln_int_lst(l_ship_ln_int_idx).attribute14_sl, --80
x_ship_ln_int_lst(l_ship_ln_int_idx).attribute15_sl --81
) ;
ELSIF p_transaction_type = 'UPDATE' THEN
l_debug_info := 'Update Shipment Lines table';
UPDATE inl_ship_lines_all sl
SET adjustment_num = 0,
match_id = NULL,
txn_qty = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).txn_qty , sl.txn_qty) ,
txn_uom_code = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).txn_uom_code , sl.txn_uom_code) ,
txn_unit_price = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).txn_unit_price , sl.txn_unit_price) ,
primary_qty = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).primary_qty , sl.primary_qty) ,
primary_uom_code = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).primary_uom_code , sl.primary_uom_code) ,
primary_unit_price = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).primary_unit_price , sl.primary_unit_price) ,
secondary_qty = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).secondary_qty , sl.secondary_qty) ,
secondary_uom_code = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).secondary_uom_code , sl.secondary_uom_code) ,
secondary_unit_price = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).secondary_unit_price , sl.secondary_unit_price) ,
landed_cost_flag = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).landed_cost_flag , sl.landed_cost_flag) ,
allocation_enabled_flag = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).allocation_enabled_flag , sl.allocation_enabled_flag) ,
trx_business_category = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).trx_business_category , sl.trx_business_category) ,
intended_use = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).intended_use , sl.intended_use) ,
product_fiscal_class = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).product_fiscal_class , sl.product_fiscal_class) ,
product_category = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).product_category , sl.product_category) ,
product_type = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).product_type , sl.product_type) ,
user_def_fiscal_class = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).user_def_fiscal_class , sl.user_def_fiscal_class) ,
tax_classification_code = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).tax_classification_code , sl.tax_classification_code) ,
assessable_value = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).assessable_value , sl.assessable_value) ,
ship_from_party_id = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).ship_from_party_id , sl.ship_from_party_id) ,
ship_from_party_site_id = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).ship_from_party_site_id , sl.ship_from_party_site_id) ,
ship_to_organization_id = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).ship_to_organization_id , sl.ship_to_organization_id) ,
ship_to_location_id = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).ship_to_location_id , sl.ship_to_location_id) ,
bill_from_party_id = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).bill_from_party_id , sl.bill_from_party_id) ,
bill_from_party_site_id = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).bill_from_party_site_id , sl.bill_from_party_site_id) ,
bill_to_organization_id = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).bill_to_organization_id , sl.bill_to_organization_id) ,
bill_to_location_id = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).bill_to_location_id , sl.bill_to_location_id) ,
poa_party_id = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).poa_party_id , sl.poa_party_id) ,
poa_party_site_id = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).poa_party_site_id , sl.poa_party_site_id) ,
poo_organization_id = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).poo_organization_id , sl.poo_organization_id) ,
poo_location_id = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).poo_location_id , sl.poo_location_id) ,
org_id = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).org_id , sl.org_id) , --NVL(p_org_id,org_id)
ship_line_int_id = x_ship_ln_int_lst(l_ship_ln_int_idx).ship_line_int_id ,
interface_source_table = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).interface_source_table , sl.interface_source_table) ,
interface_source_line_id = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).interface_source_line_id , sl.interface_source_line_id),
last_updated_by = L_FND_USER_ID ,
last_update_date = SYSDATE ,
last_update_login = L_FND_LOGIN_ID ,
program_id = L_FND_CONC_PROGRAM_ID ,
program_update_date = SYSDATE ,
program_application_id = L_FND_PROG_APPL_ID ,
request_id = L_FND_CONC_REQUEST_ID ,
attribute_category = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).attribute_category_sl , sl.attribute_category) ,
attribute1 = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).attribute1_sl , sl.attribute1) ,
attribute2 = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).attribute2_sl , sl.attribute2) ,
attribute3 = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).attribute3_sl , sl.attribute3) ,
attribute4 = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).attribute4_sl , sl.attribute4) ,
attribute5 = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).attribute5_sl , sl.attribute5) ,
attribute6 = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).attribute6_sl , sl.attribute6) ,
attribute7 = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).attribute7_sl , sl.attribute7) ,
attribute8 = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).attribute8_sl , sl.attribute8) ,
attribute9 = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).attribute9_sl , sl.attribute9) ,
attribute10 = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).attribute10_sl , sl.attribute10) ,
attribute11 = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).attribute11_sl , sl.attribute11) ,
attribute12 = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).attribute12_sl , sl.attribute12) ,
attribute13 = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).attribute13_sl , sl.attribute13) ,
attribute14 = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).attribute14_sl , sl.attribute14) ,
attribute15 = NVL(x_ship_ln_int_lst(l_ship_ln_int_idx).attribute15_sl , sl.attribute15)
WHERE ship_line_id = x_ship_ln_int_lst(l_ship_ln_int_idx).ship_line_id;
UPDATE inl_ship_lines_int
SET processing_status_code = 'COMPLETED' ,
ship_header_id = x_ship_ln_int_lst(l_ship_ln_int_idx).ship_header_id ,
ship_line_group_id = x_ship_ln_int_lst(l_ship_ln_int_idx).ship_line_group_id ,
ship_line_id = x_ship_ln_int_lst(l_ship_ln_int_idx).ship_line_id ,
request_id = L_FND_CONC_REQUEST_ID ,
last_updated_by = L_FND_USER_ID ,
last_update_date = SYSDATE ,
last_update_login = L_FND_LOGIN_ID ,
program_id = L_FND_CONC_PROGRAM_ID ,
program_update_date = SYSDATE ,
program_application_id = L_FND_PROG_APPL_ID
WHERE ship_line_int_id = x_ship_ln_int_lst(l_ship_ln_int_idx).ship_line_int_id ;
IF x_ship_hdr_int_rec.transaction_type = 'DELETE' THEN
IF x_ship_hdr_int_rec.last_task_code > 10 THEN --Bug#11794483C
Delete_Ship(
p_ship_header_id => x_ship_hdr_int_rec.ship_header_id,
x_return_status => l_return_status);
ELSIF x_ship_hdr_int_rec.transaction_type = 'UPDATE' THEN
l_debug_info := 'Updating the Shipment Header';
UPDATE inl_ship_headers_all sh --Bug#10381495
SET ship_date = NVL(x_ship_hdr_int_rec.ship_date ,sh.ship_date ),
taxation_country = NVL(x_ship_hdr_int_rec.taxation_country ,sh.taxation_country ),
document_sub_type = NVL(x_ship_hdr_int_rec.document_sub_type ,sh.document_sub_type ),
ship_header_int_id = NVL(x_ship_hdr_int_rec.ship_header_int_id ,sh.ship_header_int_id ),
interface_source_code = NVL(x_ship_hdr_int_rec.interface_source_code ,sh.interface_source_code ),
interface_source_table = NVL(x_ship_hdr_int_rec.interface_source_table ,sh.interface_source_table ),
interface_source_line_id = NVL(x_ship_hdr_int_rec.interface_source_line_id,sh.interface_source_line_id),
simulation_id = NVL(p_simulation_id ,sh.simulation_id ), -- Bug#9279355
ship_status_code = 'VALIDATION REQ' ,
last_updated_by = L_FND_USER_ID ,
last_update_date = SYSDATE ,
last_update_login = L_FND_LOGIN_ID ,
program_id = L_FND_CONC_PROGRAM_ID ,
program_update_date = SYSDATE ,
program_application_id = L_FND_PROG_APPL_ID ,
request_id = L_FND_CONC_REQUEST_ID ,
attribute_category = NVL(x_ship_hdr_int_rec.attribute_category ,sh.attribute_category ),
attribute1 = NVL(x_ship_hdr_int_rec.attribute1 ,sh.attribute1 ),
attribute2 = NVL(x_ship_hdr_int_rec.attribute2 ,sh.attribute2 ),
attribute3 = NVL(x_ship_hdr_int_rec.attribute3 ,sh.attribute3 ),
attribute4 = NVL(x_ship_hdr_int_rec.attribute4 ,sh.attribute4 ),
attribute5 = NVL(x_ship_hdr_int_rec.attribute5 ,sh.attribute5 ),
attribute6 = NVL(x_ship_hdr_int_rec.attribute6 ,sh.attribute6 ),
attribute7 = NVL(x_ship_hdr_int_rec.attribute7 ,sh.attribute7 ),
attribute8 = NVL(x_ship_hdr_int_rec.attribute8 ,sh.attribute8 ),
attribute9 = NVL(x_ship_hdr_int_rec.attribute9 ,sh.attribute9 ),
attribute10 = NVL(x_ship_hdr_int_rec.attribute10 ,sh.attribute10 ),
attribute11 = NVL(x_ship_hdr_int_rec.attribute11 ,sh.attribute11 ),
attribute12 = NVL(x_ship_hdr_int_rec.attribute12 ,sh.attribute12 ),
attribute13 = NVL(x_ship_hdr_int_rec.attribute13 ,sh.attribute13 ),
attribute14 = NVL(x_ship_hdr_int_rec.attribute14 ,sh.attribute14 ),
attribute15 = NVL(x_ship_hdr_int_rec.attribute15 ,sh.attribute15)
WHERE ship_header_id = x_ship_hdr_int_rec .ship_header_id;
SELECT user_defined_ship_num_code,
next_ship_num,
inl_ship_headers_all_s.NEXTVAL
INTO l_user_defined_ship_num_code,
l_next_ship_num,
x_ship_hdr_int_rec.ship_header_id
FROM inl_parameters
WHERE organization_id = x_ship_hdr_int_rec.organization_id
FOR UPDATE OF next_ship_num;
UPDATE inl_parameters
SET next_ship_num = l_next_ship_num + 1,
last_updated_by = L_FND_USER_ID ,
last_update_date = SYSDATE ,
last_update_login = L_FND_LOGIN_ID
WHERE organization_id = x_ship_hdr_int_rec.organization_id;
l_debug_info := 'Insert into Shipment Headers table';
INSERT
INTO inl_ship_headers_all ( --Bug#10381495
ship_header_id ,-- 01
ship_num ,-- 02
ship_date ,-- 03
ship_type_id ,-- 04
ship_status_code ,-- 05
pending_matching_flag ,-- 06
rcv_enabled_flag ,-- 07
legal_entity_id ,-- 08
organization_id ,-- 09
location_id ,-- 10
org_id ,-- 11
taxation_country ,-- 12
document_sub_type ,-- 13
ship_header_int_id ,-- 14
interface_source_code ,-- 15
interface_source_table ,-- 16
interface_source_line_id ,-- 17
simulation_id ,-- 18 -- Bug#9279355
adjustment_num ,-- 19
created_by ,-- 20
creation_date ,-- 21
last_updated_by ,-- 22
last_update_date ,-- 23
last_update_login ,-- 24
program_id ,-- 25
program_update_date ,-- 26
program_application_id ,-- 27
request_id ,-- 28
attribute_category ,-- 29
attribute1 ,-- 30
attribute2 ,-- 31
attribute3 ,-- 32
attribute4 ,-- 33
attribute5 ,-- 34
attribute6 ,-- 35
attribute7 ,-- 36
attribute8 ,-- 37
attribute9 ,-- 38
attribute10 ,-- 39
attribute11 ,-- 40
attribute12 ,-- 41
attribute13 ,-- 42
attribute14 ,-- 43
attribute15 -- 44
) VALUES (
x_ship_hdr_int_rec.ship_header_id ,--01
l_ship_num ,--02
x_ship_hdr_int_rec.ship_date ,--03
x_ship_hdr_int_rec.ship_type_id ,--04
'INCOMPLETE' ,--05
NULL ,--06
x_ship_hdr_int_rec.rcv_enabled_flag ,--07
x_ship_hdr_int_rec.legal_entity_id ,--08
x_ship_hdr_int_rec.organization_id ,--09
x_ship_hdr_int_rec.location_id ,--10
x_ship_hdr_int_rec.org_id ,--11
x_ship_hdr_int_rec.taxation_country ,--12
x_ship_hdr_int_rec.document_sub_type ,--13
x_ship_hdr_int_rec.ship_header_int_id ,--14
x_ship_hdr_int_rec.interface_source_code ,--15
x_ship_hdr_int_rec.interface_source_table ,--16
x_ship_hdr_int_rec.interface_source_line_id,--17
p_simulation_id ,--18 -- Bug#9279355
0 ,--19
L_FND_USER_ID ,--20
SYSDATE ,--21
L_FND_USER_ID ,--22
SYSDATE ,--23
L_FND_LOGIN_ID ,--24
L_FND_CONC_PROGRAM_ID ,--25
SYSDATE ,--26
L_FND_PROG_APPL_ID ,--27
L_FND_CONC_REQUEST_ID ,--28
x_ship_hdr_int_rec .attribute_category ,--29
x_ship_hdr_int_rec .attribute1 ,--30
x_ship_hdr_int_rec .attribute2 ,--31
x_ship_hdr_int_rec .attribute3 ,--32
x_ship_hdr_int_rec .attribute4 ,--33
x_ship_hdr_int_rec .attribute5 ,--34
x_ship_hdr_int_rec .attribute6 ,--35
x_ship_hdr_int_rec .attribute7 ,--36
x_ship_hdr_int_rec .attribute8 ,--37
x_ship_hdr_int_rec .attribute9 ,--38
x_ship_hdr_int_rec .attribute10 ,--39
x_ship_hdr_int_rec .attribute11 ,--40
x_ship_hdr_int_rec .attribute12 ,--41
x_ship_hdr_int_rec .attribute13 ,--42
x_ship_hdr_int_rec .attribute14 ,--43
x_ship_hdr_int_rec .attribute15 --44
) ;
SELECT MIN(match_id)
INTO x_parent_match_id
FROM inl_matches
WHERE to_parent_table_id = p_to_parent_table_id
AND to_parent_table_name = p_to_parent_table_name
AND from_parent_table_id = p_from_parent_table_id
AND from_parent_table_name = p_from_parent_table_name
;
SELECT lcm_shipment_line_id
INTO x_new_to_parent_table_id
FROM rcv_transactions rt
WHERE rt.transaction_id = x_new_to_parent_table_id
;
SELECT l.line_type_lookup_code,
d.org_id,
l.inventory_item_id,
l.match_type
INTO l_line_type_lookup_code,
x_org_id, --Bug#10381495
l_inventory_item_id,
l_match_type
FROM ap_invoice_distributions_all d, --Bug#10381495
ap_invoice_lines_all l --Bug#10381495
WHERE d.invoice_distribution_id = p_from_parent_table_id
AND d.invoice_id = l.invoice_id
AND d.invoice_line_number = l.line_number
;
SELECT
mat1.matched_curr_code ,
mat1.matched_curr_conversion_rate,
mat1.matched_curr_conversion_type,
mat1.matched_curr_conversion_date,
NULL nrec_tax_amt ,
NULL charge_line_type_id ,
mat1.matched_qty ,
mat1.matched_uom_code ,
NVL(DECODE(mat1.matched_curr_code, mat2.matched_curr_code, mat2.matched_amt,
inl_landedcost_pvt.Converted_Amt(
mat2.matched_amt,
mat2.matched_curr_code,
mat1.matched_curr_code,
mat1.matched_curr_conversion_type,
mat1.matched_curr_conversion_date))
, mat1.matched_amt) AS matched_amt,
mat1.match_id,
mat2.from_parent_table_name,
mat2.from_parent_table_id
INTO
l_mat_curr_code_P ,
l_mat_curr_rate_P ,
l_mat_curr_type_P ,
l_mat_curr_date_P ,
l_mat_nrec_tax_amt_P ,
l_mat_charge_line_type_id_P,
l_mat_qty_P ,
l_mat_uom_code_P ,
l_mat_amt_P ,
l_mat_par_mat_id_P ,
l_corr_from_parent_table_name,
l_corr_from_parent_table_id
FROM
inl_matches mat1,
(select *
from inl_matches m2
where NOT (m2.from_parent_table_name = p_from_parent_table_name
AND m2.from_parent_table_id = p_from_parent_table_id)
) mat2
WHERE
mat2.parent_match_id (+) = mat1.match_id
AND mat2.match_type_code (+) = 'CORRECTION'
AND mat1.from_parent_table_name = x_new_to_parent_table_name
AND mat1.from_parent_table_id = x_new_to_parent_table_id
AND mat1.match_id
=(
SELECT MAX(mat1B.match_id)
FROM inl_matches mat1B
WHERE mat1B.to_parent_table_name = mat1.to_parent_table_name
AND mat1B.to_parent_table_id = mat1.to_parent_table_id
AND mat1B.from_parent_table_name = mat1.from_parent_table_name
AND mat1B.from_parent_table_id = mat1.from_parent_table_id
)
AND (mat2.match_id is null
OR mat2.match_id = mat1.match_id
OR mat2.match_id
=(
SELECT MAX(DECODE(mat2.match_id, mat1.match_id, NULL, mat2.match_id))
FROM inl_matches mat1C,
inl_matches mat2C
WHERE mat2C.parent_match_id (+) = mat1C.match_id
AND mat2C.match_type_code (+) = 'CORRECTION'
AND NOT (mat2C.from_parent_table_name = p_from_parent_table_name
AND mat2C.from_parent_table_id = p_from_parent_table_id)
AND mat1C.from_parent_table_name = mat1.from_parent_table_name
AND mat1C.from_parent_table_id = mat1.from_parent_table_id
))
;
SELECT
mP.matched_curr_code ,
mP.matched_curr_conversion_rate,
mP.matched_curr_conversion_type,
mP.matched_curr_conversion_date,
NULL nrec_tax_amt ,
NULL charge_line_type_id ,
mP.matched_qty ,
mP.matched_uom_code ,
mP.matched_amt ,
mP.match_id
INTO
l_mat_curr_code_P ,
l_mat_curr_rate_P ,
l_mat_curr_type_P ,
l_mat_curr_date_P ,
l_mat_nrec_tax_amt_P ,
l_mat_charge_line_type_id_P,
l_mat_qty_P ,
l_mat_uom_code_P ,
l_mat_amt_P ,
l_mat_par_mat_id_P
FROM
inl_corr_matches_v mP -- 1 get the parent
WHERE mP.from_parent_table_name = x_new_to_parent_table_name
AND mP.from_parent_table_id = x_new_to_parent_table_id
AND mP.match_id
=(
SELECT MAX(m1P.match_id)
FROM inl_corr_matches_v m1P
WHERE m1P.from_parent_table_name = mP.from_parent_table_name
AND m1P.from_parent_table_id = mP.from_parent_table_id
)
AND (mP.correction_match_id IS NULL
OR mP.correction_match_id
=(
SELECT MAX(m1P.correction_match_id)
FROM inl_corr_matches_v m1P
WHERE m1P.from_parent_table_name = mP.from_parent_table_name
AND m1P.from_parent_table_id = mP.from_parent_table_id
))
;
SELECT
m.matched_curr_code ,
m.matched_curr_conversion_rate,
m.matched_curr_conversion_type,
m.matched_curr_conversion_date,
m.nrec_tax_amt ,
m.charge_line_type_id ,
m.matched_qty ,
m.matched_uom_code ,
m.matched_amt ,
m.match_id
INTO
l_mat_curr_code_P ,
l_mat_curr_rate_P ,
l_mat_curr_type_P ,
l_mat_curr_date_P ,
l_mat_nrec_tax_amt_P ,
l_mat_charge_line_type_id_P,
l_mat_qty_P ,
l_mat_uom_code_P ,
l_mat_amt_P ,
l_mat_par_mat_id_P
FROM inl_corr_matches_v m
WHERE m.from_parent_table_name = x_new_to_parent_table_name
AND m.from_parent_table_id = x_new_to_parent_table_id
AND m.match_id =
(
SELECT MAX(m1.match_id)
FROM inl_corr_matches_v m1
WHERE m1.from_parent_table_name = x_new_to_parent_table_name
AND m1.from_parent_table_id = x_new_to_parent_table_id
)
;
SELECT ship_header_id
INTO x_ship_header_id
FROM inl_ship_headers_all --Bug#10381495
WHERE ship_header_id = x_new_to_parent_table_id;
SELECT ship_header_id
INTO x_ship_header_id
FROM inl_ship_lines_all --Bug#10381495
WHERE ship_line_id = x_new_to_parent_table_id;
SELECT ship_header_id
INTO x_ship_header_id
FROM inl_ship_line_groups
WHERE ship_line_group_id = x_new_to_parent_table_id;
l_ship_header_id_tab.DELETE;
SELECT DISTINCT(a.ship_header_id) BULK COLLECT
INTO l_ship_header_id_tab
FROM inl_charge_lines c,
inl_associations a
WHERE c.charge_line_id = x_new_to_parent_table_id
AND a.from_parent_table_name = 'INL_CHARGE_LINES'
AND a.from_parent_table_id = c.charge_line_id;
SELECT ship_header_id
INTO x_ship_header_id
FROM inl_tax_lines
WHERE tax_line_id = x_new_to_parent_table_id;
SELECT ship_header_id
INTO x_ship_header_id
FROM inl_matches
WHERE match_id = l_mat_par_mat_id_P;
SELECT DISTINCT assoc2.ship_header_id
FROM inl_associations assoc2
WHERE (assoc2.from_parent_table_name,assoc2.from_parent_table_id)
IN (select assoc.from_parent_table_name,assoc.from_parent_table_id
from inl_associations assoc
WHERE assoc.to_parent_table_name = p_to_parent_table_name
AND assoc.to_parent_table_id = p_to_parent_table_id
)
;
SELECT DISTINCT assoc2.ship_header_id
FROM inl_associations assoc2
WHERE CONNECT_BY_ISLEAF = 1
START WITH (assoc2.from_parent_table_name,assoc2.from_parent_table_id)
IN (select assoc.from_parent_table_name,assoc.from_parent_table_id
from inl_associations assoc
WHERE assoc.to_parent_table_name = p_to_parent_table_name
AND assoc.to_parent_table_id = p_to_parent_table_id
)
CONNECT BY PRIOR assoc2.from_parent_table_name = assoc2.to_parent_table_name
AND assoc2.from_parent_table_id = assoc2.to_parent_table_id
;
inl_shipment_pvt.Update_PendingMatchingFlag( --BUG#8198498
p_ship_header_id => prorates_affctdByItem_lin(i).ship_header_id,
p_pending_matching_flag => 'Y',
x_return_status => l_return_status
);
SELECT COUNT(*)
INTO l_count_unproc_match
FROM inl_matches m
WHERE m.ship_header_id = p_ship_header_id
AND NVL(m.adj_already_generated_flag, 'N') = 'N';
SELECT
MIN(DECODE(mp.process_enabled_flag,'Y',NVL(mi.adj_group_date,p_initial_sysdate),p_initial_sysdate)) adj_group_date, -- OPM Integration
mi.group_id
FROM inl_matches_int mi,
rcv_transactions rt, -- OPM Integration
mtl_parameters mp
WHERE mi.processing_status_code = 'RUNNING' /* Point 1: If any change occur here the other 4 points might be affected */
AND (p_group_id IS NULL
OR p_group_id = mi.group_id)
AND mi.to_parent_table_name = 'RCV_TRANSACTIONS'
AND mi.to_parent_table_id = rt.transaction_id
AND rt.organization_id = mp.organization_id
AND mi.request_id = l_fnd_conc_request_id_int --Bug#11794442
group by mi.group_id
order by MIN(DECODE(mp.process_enabled_flag,'Y',NVL(mi.adj_group_date,p_initial_sysdate),p_initial_sysdate)), mi.group_id
;
SELECT NULL new_match_Amount_id, -- BUG#8411723 => MURALI --BUG#8264388
max(DECODE(mp.process_enabled_flag,'Y',
NVL(mi.adj_group_date,pc_initial_sysdate),pc_initial_sysdate)) adj_group_date, -- OPM Integration,
-- if an freight invoice has 2 lines to different organization can have 2
-- different dates but need to be consider as an unic match amount in order to be re-prorated
sum(mi.matched_amt) matched_amt,
sum(mi.nrec_tax_amt) nrec_tax_amt,
count(distinct(
DECODE(mp.process_enabled_flag,'Y',
NVL(mi.adj_group_date,pc_initial_sysdate),pc_initial_sysdate))) count_adj_group_date,
mi.matched_curr_code ,
mi.matched_curr_conversion_type,
mi.matched_curr_conversion_date,
mi.matched_curr_conversion_rate,
mi.transaction_type,
mi.group_id, --BUG#8264388
mi.charge_line_type_id, --BUG#8264388
mi.tax_code --BUG#8264388
FROM inl_matches_int mi,
rcv_transactions rt, -- OPM Integration
mtl_parameters mp -- OPM Integration
WHERE mi.processing_status_code = 'RUNNING'
AND ((pc_group_id IS NULL AND mi.group_id IS NULL)
OR mi.group_id = pc_group_id) --Bug#11794442
AND mi.match_amounts_flag = 'Y'
AND mi.to_parent_table_name = 'RCV_TRANSACTIONS'
AND mi.to_parent_table_id = rt.transaction_id
AND rt.organization_id = mp.organization_id
AND mi.request_id = l_fnd_conc_request_id_int --Bug#11794442
GROUP BY
mi.matched_curr_code ,
mi.matched_curr_conversion_type,
mi.matched_curr_conversion_date,
mi.matched_curr_conversion_rate,
mi.transaction_type,
mi.group_id,
mi.charge_line_type_id,
mi.tax_code
ORDER BY mi.group_id -- BUG#8411723 => MURALI --BUG#8264388
;
SELECT NULL new_match_id, -- BUG#8411723 => MURALI
DECODE(mp.process_enabled_flag,'Y',NVL(mi.adj_group_date,pc_initial_sysdate),pc_initial_sysdate) adj_group_date, -- OPM Integration
mi.match_type_code ,
mi.from_parent_table_name,
mi.from_parent_table_id ,
mi.to_parent_table_name ,
mi.to_parent_table_id ,
mi.matched_qty ,
mi.matched_uom_code ,
mi.matched_amt ,
mi.matched_curr_code ,
mi.matched_curr_conversion_type,
mi.matched_curr_conversion_date,
mi.matched_curr_conversion_rate,
mi.replace_estim_qty_flag ,
mi.charge_line_type_id ,
mi.party_id ,
mi.party_site_id ,
mi.tax_code ,
mi.nrec_tax_amt ,
mi.tax_amt_included_flag,
mi.match_int_id ,
mi.transaction_type ,
NULL ship_header_id ,
NULL org_id , --Bug#10381495
mi.match_amounts_flag , --BUG#8264388
mi.group_id --BUG#8264388
FROM inl_matches_int mi,
rcv_transactions rt, -- OPM Integration
mtl_parameters mp
WHERE mi.processing_status_code = 'RUNNING' /* Point 1: If any change occur here the other 4 points might be affected */
AND ((pc_group_id IS NULL AND mi.group_id IS NULL)
OR mi.group_id = pc_group_id) --Bug#11794442
AND mi.to_parent_table_name = 'RCV_TRANSACTIONS'
AND mi.to_parent_table_id = rt.transaction_id
AND rt.organization_id = mp.organization_id
AND mi.request_id = l_fnd_conc_request_id_int --Bug#11794442
order by DECODE(mp.process_enabled_flag,'Y',NVL(mi.adj_group_date,pc_initial_sysdate),pc_initial_sysdate), -- OPM Integration
mi.match_int_id -- BUG#8411723 => MURALI
;
SELECT sh.ship_num
FROM inl_ship_lines sl,
inl_ship_headers sh,
inl_matches_int mi,
rcv_transactions rt
WHERE sh.ship_header_id = sl.ship_header_id
AND sl.ship_line_id = rt.lcm_shipment_line_id
AND mi.to_parent_table_name = 'RCV_TRANSACTIONS'
AND mi.to_parent_table_id = rt.transaction_id
AND mi.group_id = pc_group_id
FOR UPDATE OF sh.ship_header_id NOWAIT;
l_g_records_inserted_bkp NUMBER;
l_debug_info := 'Record for group_id ' || distgroupid_List(iGroup).group_id || 'cannot be reserved for update. It has already been reserved by another user.';
UPDATE inl_matches_int mi
SET mi.processing_status_code = 'PENDING',
mi.request_id = L_FND_CONC_REQUEST_ID,
mi.last_updated_by = L_FND_USER_ID,
mi.last_update_date = SYSDATE,
mi.last_update_login = L_FND_LOGIN_ID,
mi.program_id = L_FND_CONC_PROGRAM_ID,
mi.program_update_date = SYSDATE,
mi.program_application_id = L_FND_PROG_APPL_ID
WHERE mi.group_id = distgroupid_List(iGroup).group_id;
l_g_records_inserted_bkp:= nvl(g_records_inserted,0);
SELECT inl_match_Amounts_s.NEXTVAL
INTO matchAmountsToProcess_List(iMatch).new_match_Amount_id
FROM dual; -- BUG#8411723 => MURALI
INSERT INTO inl_match_Amounts(
match_amount_id ,
adj_group_date ,-- OPM Integration
matched_amt ,
matched_curr_code ,
matched_curr_conversion_type,
matched_curr_conversion_date,
matched_curr_conversion_rate,
group_id , --BUG#8264388
charge_line_type_id , --BUG#8264388
tax_code , --BUG#8264388
nrec_tax_amt , --BUG#8264388
program_id ,
program_update_date ,
program_application_id ,
request_id ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login
) VALUES
(
matchAmountsToProcess_List(iMatch).new_match_Amount_id ,
DECODE(matchAmountsToProcess_List(iMatch).count_adj_group_date,1
,matchAmountsToProcess_List(iMatch).adj_group_date
,NULL) , -- OPM Integration
matchAmountsToProcess_List(iMatch).matched_amt ,
matchAmountsToProcess_List(iMatch).matched_curr_code ,
matchAmountsToProcess_List(iMatch).matched_curr_conversion_type ,
matchAmountsToProcess_List(iMatch).matched_curr_conversion_date ,
matchAmountsToProcess_List(iMatch).matched_curr_conversion_rate ,
matchAmountsToProcess_List(iMatch).group_id , --BUG#8264388
matchAmountsToProcess_List(iMatch).charge_line_type_id , --BUG#8264388
matchAmountsToProcess_List(iMatch).tax_code , --BUG#8264388
matchAmountsToProcess_List(iMatch).nrec_tax_amt , --BUG#8264388
L_FND_CONC_PROGRAM_ID ,
SYSDATE ,
L_FND_PROG_APPL_ID ,
L_FND_CONC_REQUEST_ID ,
L_FND_USER_ID ,
SYSDATE ,
L_FND_USER_ID ,
SYSDATE ,
L_FND_LOGIN_ID
);
SELECT inl_matches_s.NEXTVAL
INTO matchesToProcess_List(iMatch).new_match_id
FROM dual; -- BUG#8411723 => MURALI
INSERT INTO inl_matches(
match_id , /* 01 */
adj_group_date , /* 01A*/ --OPM Integration
match_type_code , /* 02 */
ship_header_id , /* 03 */
from_parent_table_name , /* 04 */
from_parent_table_id , /* 05 */
to_parent_table_name , /* 06 */
to_parent_table_id , /* 07 */
parent_match_id , /* 08 */
matched_qty , /* 09 */
matched_uom_code , /* 10 */
matched_amt , /* 11 */
matched_curr_code , /* 12 */
matched_curr_conversion_type , /* 13 */
matched_curr_conversion_date , /* 14 */
matched_curr_conversion_rate , /* 15 */
adj_already_generated_flag , /* 16 */
replace_estim_qty_flag , /* 17 */
existing_match_info_flag , /* 18 */
charge_line_type_id , /* 19 */
party_id , /* 20 */
party_site_id , /* 21 */
tax_code , /* 22 */
nrec_tax_amt , /* 23 */
tax_amt_included_flag , /* 24 */
match_amount_id , /* 25 */
match_int_id , /* 26 */
program_id , /* 27 */
program_update_date , /* 28 */
program_application_id , /* 29 */
request_id , /* 30 */
created_by , /* 31 */
creation_date , /* 32 */
last_updated_by , /* 33 */
last_update_date , /* 34 */
last_update_login /* 35 */
)
VALUES(
matchesToProcess_List(iMatch).new_match_id , /* 01 */
matchesToProcess_List(iMatch).adj_group_date , /* 01A */ --OPM Integration
matchesToProcess_List(iMatch).match_type_code , /* 02 */
matchesToProcess_List(iMatch).ship_header_id , /* 03 */
matchesToProcess_List(iMatch).from_parent_table_name , /* 04 */
matchesToProcess_List(iMatch).from_parent_table_id , /* 05 */
l_new_to_parent_table_name , /* 06 */
l_new_to_parent_table_id , /* 07 */
l_parent_match_id , /* 08 */
l_matched_qty , /* 09 */
l_matched_uom_code , /* 10 */
l_matched_amt , /* 11 */
l_matched_curr_code , /* 12 */
l_matched_curr_conversion_type , /* 13 */
l_matched_curr_conversion_date , /* 14 */
l_matched_curr_conversion_rate , /* 15 */
'N' , /* 16 */
matchesToProcess_List(iMatch).replace_estim_qty_flag , /* 17 */
l_existing_match_info_flag , /* 18 */
matchesToProcess_List(iMatch).charge_line_type_id , /* 19 */
matchesToProcess_List(iMatch).party_id , /* 20 */
matchesToProcess_List(iMatch).party_site_id , /* 21 */
matchesToProcess_List(iMatch).tax_code , /* 22 */
l_nrec_tax_amt , /* 23 */
matchesToProcess_List(iMatch).tax_amt_included_flag , /* 24 */
DECODE(matchesToProcess_List(iMatch).match_amounts_flag,
'Y',
(SELECT ima.match_amount_id
FROM inl_match_amounts ima
WHERE ima.group_id = matchesToProcess_List(iMatch).group_id
AND nvl(ima.charge_line_type_id,-9) = nvl(matchesToProcess_List(iMatch).charge_line_type_id,-9)
AND nvl(ima.tax_code,'XxX') = nvl(matchesToProcess_List(iMatch).tax_code,'XxX')
AND ima.matched_curr_code = matchesToProcess_List(iMatch).matched_curr_code
)
, NULL
), /* 25 */
matchesToProcess_List(iMatch).match_int_id , /* 26 */
L_FND_CONC_PROGRAM_ID , /* 27 */
SYSDATE , /* 28 */
L_FND_PROG_APPL_ID , /* 29 */
L_FND_CONC_REQUEST_ID , /* 30 */
L_FND_USER_ID , /* 31 */
SYSDATE , /* 32 */
L_FND_USER_ID , /* 33 */
SYSDATE , /* 34 */
L_FND_LOGIN_ID /* 35 */
);
inl_shipment_pvt.Update_PendingMatchingFlag( --BUG#8198498
p_ship_header_id => matchesToProcess_List(iMatch).ship_header_id,
p_pending_matching_flag => 'Y',
x_return_status => l_return_status
);
UPDATE inl_matches_int m
SET m.processing_status_code = 'COMPLETED' ,
m.request_id = L_FND_CONC_REQUEST_ID,
m.last_updated_by = L_FND_USER_ID ,
m.last_update_date = SYSDATE ,
m.last_update_login = L_FND_LOGIN_ID ,
m.program_id = L_FND_CONC_PROGRAM_ID,
m.program_update_date = SYSDATE ,
m.program_application_id = L_FND_PROG_APPL_ID
WHERE m.match_int_id = matchesToProcess_List(iMatch).match_int_id;
g_records_inserted := g_records_inserted + 1;
g_records_inserted := l_g_records_inserted_bkp;
UPDATE inl_matches_int mi
SET mi.processing_status_code = 'ERROR' ,
mi.request_id = L_FND_CONC_REQUEST_ID ,
mi.last_updated_by = L_FND_USER_ID ,
mi.last_update_date = SYSDATE ,
mi.last_update_login = L_FND_LOGIN_ID ,
mi.program_id = L_FND_CONC_PROGRAM_ID ,
mi.program_update_date = SYSDATE ,
mi.program_application_id = L_FND_PROG_APPL_ID
WHERE mi.request_id = l_fnd_conc_request_id_int --Bug#11794442
AND ((distgroupid_List(iGroup).group_id IS NULL AND mi.group_id IS NULL)
OR mi.group_id = distgroupid_List(iGroup).group_id);
SELECT MIN(match_id)
INTO p_parent_match_id
FROM inl_matches
WHERE from_parent_table_id = p_to_parent_table_id
AND from_parent_table_name = p_to_parent_table_name
;
SELECT UPPER(clt.allocation_basis)
INTO l_allocation_basis
FROM inl_charge_line_types_vl clt
WHERE clt.charge_line_type_id = p_charge_line_type_id;
p_updated_match_id IN NUMBER,
p_ship_line_id IN NUMBER,
p_matched_qty IN NUMBER,
p_matched_uom_code IN VARCHAR2,
p_replace_estim_qty_flag IN VARCHAR2,
p_match_type_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) RETURN BOOLEAN IS
l_program_name CONSTANT VARCHAR2(30) := 'Validate_MatchQty';
SELECT m1.match_id,
m1.matched_qty ,
m1.matched_uom_code
FROM inl_corr_matches_v m1
WHERE m1.to_parent_table_name = 'INL_SHIP_LINES'
AND m1.to_parent_table_id = p_ship_line_id
AND m1.match_type_code = 'ITEM'
AND m1.replace_estim_qty_flag = 'N'
-- In case of resubmit the same actual value more than once, the
-- new line is created as existing_match_info_flag = 'Y'.
-- For checking purposes, it will consider only the latest record
AND m1.match_id IN (SELECT MAX(m2.match_id)
FROM inl_corr_matches_v m2
WHERE m2.to_parent_table_name = m1.to_parent_table_name
AND m2.to_parent_table_id = m1.to_parent_table_id
AND m2.from_parent_table_name = m1.from_parent_table_name
AND m2.from_parent_table_id = m1.from_parent_table_id
AND m2.match_type_code = 'ITEM'
AND m2.replace_estim_qty_flag = 'N'
)
;
SELECT m1.transaction_type,
m1.match_type_code ,
m1.match_int_id ,
m1.matched_qty ,
m1.matched_uom_code ,
m1.replace_estim_qty_flag
FROM inl_matches_int m1
WHERE m1.to_parent_table_name = 'INL_SHIP_LINES'
AND m1.to_parent_table_id = p_ship_line_id
AND m1.match_int_id <> NVL(p_updated_match_id, 0)
AND m1.processing_status_code = 'RUNNING'
ORDER BY m1.match_int_id
;
SELECT
sh.organization_id ,--Bug#9660043
--Bug#9660043 sl.ship_header_id ,
--Bug#9660043 sl.ship_line_num ,
sl.inventory_item_id ,
--Bug#9660043 sl.ship_line_group_id ,
sla.txn_qty , --Bug#9660043
sla.txn_uom_code --Bug#9660043
INTO
l_organization_id , --Bug#9660043
--Bug#9660043 l_ship_header_id ,
--Bug#9660043 l_ship_line_num ,
l_inventory_item_id ,
--Bug#9660043 l_ship_line_group_id,
l_qty , --Bug#9660043
l_uom_code --Bug#9660043
FROM
inl_ship_lines_all sl, --Bug#10381495
inl_ship_headers_all sh, --Bug#9660043 --Bug#10381495
inl_adj_ship_lines_v sla
WHERE sl.ship_line_id = p_ship_line_id
AND sh.ship_header_id = sl.ship_header_id --Bug#9660043
AND sla.ship_header_id = sl.ship_header_id --Bug#9660043
AND sla.ship_line_group_id= sl.ship_line_group_id --Bug#9660043
AND sla.ship_line_num = sl.ship_line_num; --Bug#9660043
SELECT sh.organization_id
INTO l_organization_id
FROM inl_ship_headers sh
WHERE sh.ship_header_id = l_ship_header_id;
SELECT sla.txn_qty,
sla.txn_uom_code
INTO l_qty,
l_uom_code
FROM inl_adj_ship_lines_v sla
WHERE sla.ship_header_id = l_ship_header_id
AND sla.ship_line_group_id = l_ship_line_group_id
AND sla.ship_line_num = l_ship_line_num;
SELECT m1.matched_qty,
m1.matched_uom_code,
m1.match_id
INTO l_unproc_actual_qty,
l_unproc_uom_code,
l_unproc_match_id
FROM inl_corr_matches_v m1
WHERE m1.to_parent_table_name = 'INL_SHIP_LINES'
AND m1.to_parent_table_id = p_ship_line_id
AND m1.match_type_code = 'ITEM'
AND m1.replace_estim_qty_flag = 'Y'
AND m1.adj_already_generated_flag = 'N'
-- For checking purposes, it will consider only
-- the latest and not processed record
AND m1.match_id IN (SELECT MAX(m2.match_id)
FROM inl_corr_matches_v m2
WHERE m2.to_parent_table_name = m1.to_parent_table_name
AND m2.to_parent_table_id = m1.to_parent_table_id
AND m2.match_type_code = 'ITEM'
AND m2.replace_estim_qty_flag = 'Y'
AND m2.adj_already_generated_flag = 'N'
)
;
IF p_updated_match_id = l_unproc_match_id THEN
l_qty := p_matched_qty;
l_debug_info := 'Unprocessed value will be updated by the current record(replace_estim_qty_flag = Y):';
SELECT SUM(decode(m1.matched_uom_code,
l_uom_code, m1.matched_qty,
NVL(inl_landedcost_pvt.Converted_Qty(l_organization_id,
l_inventory_item_id,
m1.matched_qty,
m1.matched_uom_code,
l_uom_code
)
,0)
)
)
INTO l_unproc_actual_qty
FROM inl_corr_matches_v m1
WHERE m1.to_parent_table_name = 'INL_SHIP_LINES'
AND m1.to_parent_table_id = p_ship_line_id
AND m1.match_type_code = 'ITEM'
AND m1.replace_estim_qty_flag = 'N'
AND m1.adj_already_generated_flag = 'N'
;
IF OI_matches_List(iMat).transaction_type <> 'DELETE' THEN
l_qty := OI_matches_List(iMat) .matched_qty;
l_debug_info := 'Validate if exist any ELC update on going.';
p_debug_info => 'Verify if Ship Line has an ELC update'
);
p_debug_info => 'Verify if any related Ship Line has an ELC update');
p_debug_info => 'Verify if Ship Line has an ELC update to parent RCV_TRANSACTIONS with FOR UPDATE of inl_ship_headers');
SELECT
sl.new_currency_conversion_rate,
sl.new_currency_conversion_date,
sl.new_currency_conversion_type,
sl.new_txn_unit_price,
sh.ship_num
INTO
l_new_currency_conversion_rate,
l_new_currency_conversion_date,
l_new_currency_conversion_type,
l_new_txn_unit_price,
l_ship_num -- SCM-051
FROM
rcv_transactions rt,
inl_adj_ship_lines_v sl,
inl_ship_headers_all sh -- SCM-051
WHERE
rt.transaction_id = p_to_parent_table_id
AND sh.ship_header_id = sl.ship_header_id -- SCM-051
AND NVL(sl.parent_ship_line_id,sl.ship_line_id) = rt.lcm_shipment_line_id
FOR UPDATE of sh.ship_header_id NOWAIT; -- SCM-051
p_debug_info => 'Verify if Ship Line has an ELC update to parent INL_SHIP_LINES with FOR UPDATE of inl_ship_headers');
SELECT
sl.new_currency_conversion_rate,
sl.new_currency_conversion_date,
sl.new_currency_conversion_type,
sl.new_txn_unit_price,
sh.ship_num
INTO
l_new_currency_conversion_rate,
l_new_currency_conversion_date,
l_new_currency_conversion_type,
l_new_txn_unit_price,
l_ship_num
FROM
inl_adj_ship_lines_v sl,
inl_ship_headers_all sh -- SCM-051
WHERE
sh.ship_header_id = sl.ship_header_id -- SCM-051
AND NVL(sl.parent_ship_line_id,sl.ship_line_id) = p_to_parent_table_id
FOR UPDATE of sh.ship_header_id NOWAIT; -- SCM-051
p_debug_info => 'ELC Update on going to p_to_parent_table_id: ' || p_to_parent_table_id);
p_error_message_name => 'INL_ERR_OI_CHK_ELC_UPDATE',
p_token1_name => 'MATCH_INT_ID',
p_token1_value => p_match_int_id,
p_token2_name => 'SHIP_NUM',
p_token2_value => l_ship_num,
p_token3_name => 'SHIP_NUM',
p_token3_value => l_ship_num,
x_return_status => l_return_status
) ;
p_debug_info => 'Verify if any related charge Line has an ELC update');
p_debug_info => 'Select FOR UPDATE of inl_ship_headers');
SELECT sh.ship_num
INTO l_ship_num
FROM inl_ship_headers_all sh,
inl_ship_lines_all sl,
rcv_transactions rt
WHERE sh.ship_header_id = sl.ship_header_id
AND rt.transaction_id = p_to_parent_table_id
AND NVL(sl.parent_ship_line_id,sl.ship_line_id) = rt.lcm_shipment_line_id
AND ROWNUM = 1
FOR UPDATE OF sh.ship_header_id NOWAIT;
p_debug_info => 'Verify if Charge Line has an ELC update to parent RCV_TRANSACTIONS');
SELECT
NVL(COUNT(*),0)
INTO
l_count_elc_ong
FROM
inl_adj_charge_lines_v cl,
inl_associations a,
inl_ship_lines_all sl,
rcv_transactions rt
WHERE
rt.transaction_id = p_to_parent_table_id
AND NVL(sl.parent_ship_line_id,sl.ship_line_id) = rt.lcm_shipment_line_id
AND a.from_parent_table_name = 'INL_CHARGE_LINES'
AND (
(a.to_parent_table_name = 'INL_SHIP_HEADERS'
AND a.to_parent_table_id = sl.ship_header_id)
OR
(a.to_parent_table_name = 'INL_SHIP_LINE_GROUPS'
AND a.to_parent_table_id = sl.ship_line_group_id)
OR
(a.to_parent_table_name = 'INL_SHIP_LINES'
AND a.to_parent_table_id = NVL(sl.parent_ship_line_id,sl.ship_line_id))
)
AND cl.charge_line_type_id = p_charge_line_type_id
AND cl.adjustment_num <= 0
AND cl.charge_line_id IN
(
SELECT
cl1.charge_line_id
FROM
inl_charge_lines cl1
WHERE
CONNECT_BY_ISLEAF = 1
START WITH cl1.charge_line_id = a.from_parent_table_id
CONNECT BY PRIOR cl1.charge_line_id = cl1.parent_charge_line_id
)
AND (cl.new_currency_conversion_rate IS NOT NULL
OR cl.new_currency_conversion_date IS NOT NULL
OR cl.new_currency_conversion_type IS NOT NULL
OR cl.new_charge_amt IS NOT NULL
OR cl.adjustment_type_flag = 'A') -- new ELC charge line waiting for submit
;
p_debug_info => 'Verify if Charge Line has an ELC update to parent INL_SHIP_LINES');
SELECT sh.ship_num
INTO l_ship_num
FROM inl_ship_headers_all sh,
inl_ship_lines_all sl
WHERE sh.ship_header_id = sl.ship_header_id
AND sl.ship_line_id = p_to_parent_table_id
AND ROWNUM = 1
FOR UPDATE OF sh.ship_header_id NOWAIT;
p_debug_info => 'Verify if Charge Line has an ELC update to parent INL_SHIP_LINES');
SELECT
NVL(COUNT(*),0)
INTO
l_count_elc_ong
FROM
inl_adj_charge_lines_v cl,
inl_associations a,
inl_ship_lines_all sl
WHERE
sl.ship_line_id = p_to_parent_table_id
AND a.from_parent_table_name = 'INL_CHARGE_LINES'
AND (
(a.to_parent_table_name = 'INL_SHIP_HEADERS'
AND a.to_parent_table_id = sl.ship_header_id)
OR
(a.to_parent_table_name = 'INL_SHIP_LINE_GROUPS'
AND a.to_parent_table_id = sl.ship_line_group_id)
OR
(a.to_parent_table_name = 'INL_SHIP_LINES'
AND a.to_parent_table_id = NVL(sl.parent_ship_line_id,sl.ship_line_id))
)
AND cl.charge_line_type_id = p_charge_line_type_id
AND cl.adjustment_num <= 0
AND (cl.new_currency_conversion_rate IS NOT NULL
OR cl.new_currency_conversion_date IS NOT NULL
OR cl.new_charge_amt IS NOT NULL
OR cl.adjustment_type_flag = 'A') -- new ELC charge line waiting for submit
;
p_error_message_name => 'INL_ERR_OI_CHK_ELC_UPDATE',
p_token1_name => 'MATCH_INT_ID',
p_token1_value => p_match_int_id,
p_token2_name => 'SHIP_NUM',
p_token2_value => l_ship_num,
p_token3_name => 'SHIP_NUM',
p_token3_value => l_ship_num,
x_return_status => l_return_status
) ;
SELECT m.group_id
INTO l_group_id
FROM inl_matches_int m
WHERE m.match_int_id = p_match_int_id;
SELECT unit_of_measure
INTO l_uom
FROM mtl_units_of_measure
WHERE uom_code = p_matched_uom_code;
SELECT adj_already_generated_flag
INTO l_adj_alr_gen_flag
FROM inl_matches
WHERE match_id = p_parent_match_id;
SELECT adj_already_generated_flag
INTO l_adj_alr_gen_flag
FROM inl_matches
WHERE match_id = p_match_id;
SELECT ship_header_id
INTO l_ship_header_id
FROM inl_ship_headers_all --Bug#10381495
WHERE ship_header_id = p_to_parent_table_id;
SELECT ship_header_id
INTO l_ship_header_id
FROM inl_ship_lines_all --Bug#10381495
WHERE ship_line_id = p_to_parent_table_id;
SELECT ship_header_id
INTO l_ship_header_id
FROM inl_ship_line_groups
WHERE ship_line_group_id = p_to_parent_table_id;
l_ship_header_id_tab.DELETE;
SELECT DISTINCT(a.ship_header_id)
BULK COLLECT INTO l_ship_header_id_tab
FROM inl_charge_lines c,
inl_associations a
WHERE c.charge_line_id = p_to_parent_table_id
AND a.from_parent_table_name = 'INL_CHARGE_LINES'
AND a.from_parent_table_id = c.charge_line_id;
SELECT ship_header_id
INTO l_ship_header_id
FROM inl_tax_lines
WHERE tax_line_id = p_to_parent_table_id;
SELECT
NVL(sl.ship_header_id, sli.ship_header_id),
DECODE(sl.ship_header_id, NULL, 'N', 'Y')
INTO
l_ship_header_id,
l_INT_importedOk
FROM
inl_ship_lines_int sli,
inl_ship_lines_all sl --Bug#10381495
WHERE
sli.ship_line_int_id = p_to_parent_table_id
AND sl.ship_line_int_id = p_to_parent_table_id;
SELECT sli.ship_header_id
INTO l_ship_header_id
FROM inl_ship_lines_int sli
WHERE sli.ship_line_int_id = p_to_parent_table_id;
SELECT sl.ship_header_id
INTO l_ship_header_id
FROM inl_ship_lines sl
WHERE sl.ship_line_int_id = p_to_parent_table_id;
SELECT
NVL(sh.ship_header_id, shi.ship_header_id),
DECODE(sh.ship_header_id, NULL, 'N', 'Y')
INTO
l_ship_header_id,
l_INT_importedOk
FROM
inl_ship_headers_int shi,
inl_ship_headers_all sh --Bug#10381495
WHERE
shi.ship_header_int_id = p_to_parent_table_id
AND sh.ship_header_int_id = p_to_parent_table_id
;
SELECT shi.ship_header_id
INTO l_ship_header_id
FROM inl_ship_headers_int shi
WHERE shi.ship_header_int_id = p_to_parent_table_id;
SELECT sh.ship_header_id
INTO l_ship_header_id
FROM inl_ship_headers sh
WHERE sh.ship_header_int_id = p_to_parent_table_id;
SELECT
rt.lcm_shipment_line_id,
sl.ship_header_id
INTO
l_ship_line_id,
l_ship_header_id
FROM
rcv_transactions rt,
inl_ship_lines_all sl --Bug#10381495
WHERE
rt.transaction_id = p_to_parent_table_id
AND sl.ship_line_id = rt.lcm_shipment_line_id
;
SELECT lcm_shipment_line_id
INTO l_ship_line_id
FROM rcv_transactions rt
WHERE rt.transaction_id = p_to_parent_table_id;
SELECT sl.ship_header_id
INTO l_ship_header_id
FROM inl_ship_lines sl
WHERE sl.ship_line_id = l_ship_line_id;
SELECT ship_header_id
INTO l_ship_header_id
FROM inl_matches
WHERE match_id = p_parent_match_id;
ELSIF p_transaction_type IN('UPDATE', 'DELETE') THEN
IF p_match_id IS NULL THEN
l_result := L_FND_FALSE;
SELECT adj_already_generated_flag,
replace_estim_qty_flag
INTO l_adj_already_generated_flag,
l_replace_estim_qty_flag
FROM inl_matches m
WHERE m.match_id = p_match_id;
SELECT m1.match_id
INTO l_other_REQF_Y_match_id
FROM inl_matches m1
WHERE m1.to_parent_table_name = p_to_parent_table_name
AND m1.to_parent_table_id = p_to_parent_table_id
AND m1.match_type_code = 'ITEM'
AND m1.replace_estim_qty_flag = 'Y'
AND m1.adj_already_generated_flag = 'N'
--For validation purpose, it will consider only the latest and not processed record
AND m1.match_id IN (SELECT MAX(m2.match_id)
FROM inl_matches m2
WHERE m2.to_parent_table_name = m1.to_parent_table_name
AND m2.to_parent_table_id = m1.to_parent_table_id
AND m2.match_type_code = 'ITEM'
AND m2.replace_estim_qty_flag = 'Y'
AND m2.adj_already_generated_flag = 'N'
)
;
SELECT 'x'
INTO l_field
FROM hz_parties
WHERE p_party_id = party_id;
SELECT 'x'
INTO l_field
FROM hz_party_sites
WHERE party_site_id = p_party_site_id;
SELECT match_type_code ,
matched_curr_code ,
to_parent_table_name,
to_parent_table_id
INTO l_parent_match_type_code ,
l_parent_matched_curr_code ,
l_parent_to_parent_table_name,
l_parent_to_parent_table_ID
FROM inl_matches
WHERE match_id = p_match_int_rec.parent_match_id;
p_updated_match_id => p_match_int_rec.match_id,
p_ship_line_id => NVL(l_parent_to_parent_table_id,
p_match_int_rec.to_parent_table_id),
p_matched_qty => p_match_int_rec.matched_qty,
p_matched_uom_code => p_match_int_rec.matched_uom_code,
p_replace_estim_qty_flag => p_match_int_rec.replace_estim_qty_flag,
p_match_type_code => p_match_int_rec.match_type_code,
x_return_status => l_return_status) ;
l_debug_info := 'Validate any ELC Update on going. Call Validate_MatchElcOnGoing';
FUNCTION Select_MatchesToProc(
p_group_id IN NUMBER,
p_commit IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) RETURN NUMBER IS
l_program_name CONSTANT VARCHAR2(30) := 'Select_MatchesToProc';
l_lock_name VARCHAR2(50) := 'select_MatchesToProc';
UPDATE inl_matches_int m
SET m.request_id = l_fnd_conc_request_id_int
WHERE (nvl(m.request_id,1) > 0
OR m.request_id = -1)
AND m.processing_status_code = 'PENDING'
AND (p_group_id IS NULL
OR m.group_id = p_group_id)
AND (ROWNUM < l_lines
OR p_group_id IS NOT NULL)
--Bug#16198838/14707257 Mark only records complying with hr:sec profile
--Begin
AND EXISTS
(
SELECT 1
FROM
rcv_transactions rt,
org_organization_definitions ood
WHERE
rt.transaction_id = m.to_parent_table_id
AND ood.organization_id = rt.organization_id
)
--Bug#16198838/14707257 End
; -- same group id should be process together
UPDATE inl_matches_int mi
SET mi.request_id = l_fnd_conc_request_id_int
WHERE (nvl(mi.request_id,1) > 0
OR mi.request_id = -1)
AND mi.processing_status_code = 'PENDING'
AND EXISTS (
SELECT 1
FROM inl_matches_int mi2
WHERE mi2.processing_status_code = 'PENDING'
AND mi2.request_id = l_fnd_conc_request_id_int
AND mi2.from_parent_table_name = mi.from_parent_table_name
AND mi2.from_parent_table_id = mi.from_parent_table_id
AND mi2.match_int_id <> mi.match_int_id
);
UPDATE inl_matches_int mi
SET mi.request_id = l_fnd_conc_request_id_int
WHERE (nvl(mi.request_id,1) > 0
OR mi.request_id = -1)
AND mi.processing_status_code = 'PENDING'
AND mi.to_parent_table_name = 'RCV_TRANSACTIONS'
AND EXISTS (
SELECT 1
FROM inl_matches_int mi2
WHERE mi2.processing_status_code = 'PENDING'
AND mi2.request_id = l_fnd_conc_request_id_int
AND mi2.match_int_id <> mi.match_int_id
AND mi2.to_parent_table_name = 'RCV_TRANSACTIONS'
AND mi2.to_parent_table_id = mi.to_parent_table_id
);
UPDATE inl_matches_int mi
SET mi.request_id = l_fnd_conc_request_id_int
WHERE (nvl(mi.request_id,1) > 0
OR mi.request_id = -1)
AND mi.processing_status_code = 'PENDING'
AND EXISTS (
SELECT 1
FROM inl_matches_int mi2
WHERE mi2.processing_status_code = 'PENDING'
AND mi2.request_id = l_fnd_conc_request_id_int
AND mi2.group_id = mi.group_id
AND mi2.match_int_id <> mi.match_int_id
);
SELECT DISTINCT
mi.group_id
FROM
inl_matches_int mi
WHERE
mi.processing_status_code = 'PENDING'
AND mi.request_id = l_fnd_conc_request_id_int
AND NOT EXISTS
(
SELECT 1
FROM
rcv_transactions rt,
org_organization_definitions ood
WHERE
rt.transaction_id = mi.to_parent_table_id
AND ood.organization_id = rt.organization_id
)
)
LOOP
INL_LOGGING_PVT.Log_Statement (
p_module_name => g_module_name,
p_procedure_name => l_program_name,
p_debug_info => ' Group unmarked:'||l_non_hrs.group_id);
UPDATE
inl_matches_int mi
SET mi.request_id = ABS(l_fnd_conc_request_id_int)
WHERE mi.group_id = l_non_hrs.group_id
AND mi.processing_status_code = 'PENDING';
p_debug_info => l_return_value||' selected records');
END Select_MatchesToProc;
SELECT DISTINCT group_id
FROM inl_matches_int
WHERE match_int_id < pc_max_id
AND (nvl(request_id,1) > 0
OR request_id = -1)
AND processing_status_code = 'PENDING'
AND (p_group_id IS NULL
OR group_id = p_group_id)
;
SELECT match_int_id ,
group_id ,
processing_status_code,
transaction_type ,
match_type_code ,
null,
from_parent_table_name,
from_parent_table_id ,
to_parent_table_name ,
to_parent_table_id ,
null,
matched_qty ,
matched_uom_code ,
matched_amt ,
matched_curr_code ,
matched_curr_conversion_type,
matched_curr_conversion_date,
matched_curr_conversion_rate,
replace_estim_qty_flag ,
null,
charge_line_type_id ,
party_id ,
party_number ,
party_site_id ,
party_site_number ,
tax_code ,
nrec_tax_amt ,
tax_amt_included_flag,
match_amounts_flag,
null
FROM inl_matches_int m
WHERE (p_group_id IS NULL
OR m.group_id = p_group_id)
AND m.processing_status_code = 'PENDING'
AND m.request_id = l_fnd_conc_request_id_int --Bug#11794442
ORDER BY group_id,match_int_id; --Bug#11794442
SELECT NVL(MAX(match_int_id),0)
INTO l_max_match_int_id
FROM inl_matches_int;
l_rec_num:= NVL(Select_MatchesToProc(
p_group_id => p_group_id,
p_commit => p_commit,
x_return_status => l_return_status
),0);
l_debug_info := 'Delete errors from previous analysis performed on the current line. Call Reset_InterfError';
UPDATE inl_matches_int
SET processing_status_code = 'RUNNING' ,
party_id = match_int_list(i).party_id ,
party_site_id = match_int_list(i).party_site_id ,
-- request_id = L_FND_CONC_REQUEST_ID ,--Bug#11794442
last_updated_by = L_FND_USER_ID ,
last_update_date = SYSDATE ,
last_update_login = L_FND_LOGIN_ID ,
program_id = L_FND_CONC_PROGRAM_ID ,
program_update_date = SYSDATE ,
program_application_id = L_FND_PROG_APPL_ID
WHERE match_int_id = match_int_list(i).match_int_id;
UPDATE inl_matches_int mi
SET mi.processing_status_code = 'ERROR' ,
mi.request_id = L_FND_CONC_REQUEST_ID ,
mi.last_updated_by = L_FND_USER_ID ,
mi.last_update_date = SYSDATE ,
mi.last_update_login = L_FND_LOGIN_ID ,
mi.program_id = L_FND_CONC_PROGRAM_ID ,
mi.program_update_date = SYSDATE ,
mi.program_application_id = L_FND_PROG_APPL_ID
WHERE (match_int_list(i).group_id IS NOT NULL
AND mi.group_id = match_int_list(i).group_id)
OR (match_int_list(i).group_id IS NULL
AND mi.match_int_id = match_int_list(i).match_int_id);
SELECT NVL(MAX(match_int_id),0)
INTO l_max_match_int_id2
FROM inl_matches_int
WHERE match_int_id < l_max_match_int_id+1
AND (nvl(request_id,1) > 0
OR request_id = -1)
AND processing_status_code = 'PENDING'
AND (p_group_id IS NULL
OR group_id = p_group_id)
;
CURSOR c_shipToDelete IS
SELECT shi.ship_header_int_id
FROM inl_ship_headers_int shi
WHERE (p_group_id IS NULL OR shi.group_id = p_group_id)
AND processing_status_code = 'COMPLETED';
TYPE shipToDelete_ListType IS TABLE OF c_shipToDelete%ROWTYPE;
shipToDelete_List shipToDelete_ListType;
OPEN c_shipToDelete;
FETCH c_shipToDelete BULK COLLECT INTO shipToDelete_List;
CLOSE c_shipToDelete;
IF NVL(shipToDelete_List.COUNT, 0) > 0 THEN
FOR iHead IN 1 .. shipToDelete_List.COUNT
LOOP
l_debug_info := 'Delete from INL_SHIP_HEADERS_INT ';
DELETE FROM inl_ship_headers_int
WHERE ship_header_int_id = shipToDelete_List(iHead).ship_header_int_id;
l_debug_info := 'Delete from INL_SHIP_LINES_INT ';
DELETE FROM inl_ship_lines_int
WHERE ship_header_int_id = shipToDelete_List(iHead).ship_header_int_id;
l_debug_info := 'Delete from INL_MATCHES_INT';
DELETE FROM inl_matches_int
WHERE (p_group_id IS NULL OR group_id = p_group_id)
AND processing_status_code = 'COMPLETED';
g_records_inserted := 0;
ELSIF NVL(G_RECORDS_COMPLETED,0) <> NVL(G_RECORDS_INSERTED,0) THEN -- Bug #16310024
retcode := 1;
FND_FILE.put_line( FND_FILE.log, '< ***** ' || 'LCM Shipments Inserted: ' || g_records_inserted); -- Bug #9258936
g_records_inserted := 0;
FND_FILE.put_line( FND_FILE.log, '< ***** ' || 'Records Inserted: ' || g_records_inserted); -- Bug #9258936
FUNCTION Select_LCMShipToProc(
p_group_id IN NUMBER,
p_simulation_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) RETURN NUMBER IS
l_program_name CONSTANT VARCHAR2(30) := 'select_LCMShipToProc';
l_lock_name VARCHAR2(50) := 'select_LCMShipToProc';
UPDATE inl_ship_headers_int
SET request_id = l_fnd_conc_request_id_int
WHERE (nvl(request_id,1) > 0
OR request_id = -1) --Bug#11794483B
AND processing_status_code = 'PENDING'
AND (p_group_id IS NULL
OR group_id = p_group_id)
AND ROWNUM < l_lines;
p_debug_info => l_return_value||' selected records');
END Select_LCMShipToProc;
INSERT INTO inl_interface_errors (
interface_error_id, /*01*/
parent_table_name, /*02*/
parent_table_id, /*03*/
column_name, /*04*/
column_value, /*05*/
processing_date, /*06*/
error_message_name, /*07*/
error_message, /*08*/
token1_name, /*09*/
token1_value, /*10*/
created_by, /*11*/
creation_date, /*12*/
last_updated_by, /*13*/
last_update_date, /*14*/
last_update_login, /*15*/
program_id, /*16*/
program_application_id, /*17*/
program_update_date, /*18*/
request_id /*19*/
)
SELECT
inl_interface_errors_s.NEXTVAL, /*01*/
'INL_SHIP_HEADERS_INT', /*02*/
h.ship_header_int_id, /*03*/
'ship_header_int_id', /*04*/
h.ship_header_int_id, /*05*/
SYSDATE, /*06*/
'INL_FAILED_ON_CONC_SUB', /*07*/
'No Line has been found', /*08*/
'CONC', /*09*/
'Run_ShipPreProcessor (step0)', /*10*/
L_FND_USER_ID, /*11*/
SYSDATE, /*12*/
L_FND_USER_ID, /*13*/
SYSDATE, /*14*/
L_FND_USER_ID, /*15*/
L_FND_CONC_PROGRAM_ID, /*16*/
L_FND_PROG_APPL_ID, /*17*/
SYSDATE, /*18*/
L_FND_CONC_REQUEST_ID /*19*/
FROM inl_ship_headers_int h
WHERE NVL(h.request_id,1) > 0
AND h.processing_status_code = 'PENDING'
AND h.transaction_type = 'CREATE'
AND NOT EXISTS
(SELECT 1
FROM inl_ship_lines_int l
WHERE l.ship_header_int_id = h.ship_header_int_id
AND ROWNUM < 2);
l_debug_info := 'Inserted '||SQL%ROWCOUNT ||' error messages into Interface Error table.';
UPDATE inl_ship_headers_int h
SET h.processing_status_code = 'ERROR' ,
h.request_id = L_FND_CONC_REQUEST_ID,
h.last_updated_by = L_FND_USER_ID ,
h.last_update_date = SYSDATE ,
h.last_update_login = L_FND_LOGIN_ID ,
h.program_id = L_FND_CONC_PROGRAM_ID,
h.program_update_date = SYSDATE ,
h.program_application_id = L_FND_PROG_APPL_ID
WHERE NVL(h.request_id,1) > 0
AND processing_status_code = 'PENDING'
AND transaction_type = 'CREATE'
AND NOT EXISTS
(SELECT 1
FROM inl_ship_lines_int l
WHERE l.ship_header_int_id = h.ship_header_int_id
AND ROWNUM < 2);
IF x_ship_hdr_int_rec.transaction_type IN ('UPDATE','DELETE') --Bug#11794483C
THEN
l_ship_header_id := x_ship_hdr_int_rec.ship_header_id;
ELSIF p_transaction_type = 'UPDATE' THEN
l_response_int :=
validate_LnUpdateTrxType(
p_validation_flag => p_validation_flag,
p_ship_header_id => p_ship_header_id,
p_organization_id => p_organization_id,
p_interface_source_code => p_interface_source_code,
x_ship_ln_int_lst => x_ship_ln_int_lst,
x_return_status => l_return_status
);
SELECT
ship_header_int_id
FROM inl_ship_headers_int h
WHERE request_id = l_fnd_conc_request_id_int
AND processing_status_code = 'PENDING';
l_rec_num:= select_LCMShipToProc(
p_group_id => p_group_id,
p_simulation_id => p_simulation_id,
x_return_status => l_return_status
);
OR l_ship_hdr_int_list(l_ship_hdr_int_idx).transaction_type = 'UPDATE'
THEN
l_debug_info := 'Import Shipment Lines. Call Import_Lines for ship_header_id: ' || l_ship_hdr_int_list(l_ship_hdr_int_idx).ship_header_int_id;
SELECT r.package_name
INTO l_rule_package_name
FROM inl_rules_vl r,
inl_parameters p
WHERE r.rule_id (+) = p.rule_id
AND r.enabled_flag (+) = 'Y'
AND p.organization_id = l_organization_id;
g_records_inserted := g_records_inserted + 1;
UPDATE inl_ship_headers_int
SET processing_status_code = 'COMPLETED' ,
request_id = L_FND_CONC_REQUEST_ID ,
last_updated_by = L_FND_USER_ID ,
last_update_date = SYSDATE ,
last_update_login = L_FND_LOGIN_ID ,
program_id = L_FND_CONC_PROGRAM_ID ,
program_update_date = SYSDATE ,
program_application_id = L_FND_PROG_APPL_ID ,
ship_header_id = l_ship_hdr_int_list(l_ship_hdr_int_idx).ship_header_id
WHERE ship_header_int_id = l_ship_hdr_int_list(l_ship_hdr_int_idx).ship_header_int_id;
l_debug_info := 'Checking if any record remain from the selected records.';
l_debug_info := l_ship_hdr_int_err_list.COUNT||' records remain from the previous selected records('||l_fnd_conc_request_id_int||').';
SELECT DISTINCT m.group_id
FROM inl_matches_int m,
rcv_transactions rt,
inl_ship_lines_all sl
WHERE sl.ship_line_id = rt.lcm_shipment_line_id
AND rt.transaction_id = m.to_parent_table_id
AND m.to_parent_table_name = 'RCV_TRANSACTIONS'
AND m.processing_status_code = 'ERROR'
AND sl.ship_header_id = p_ship_header_id;
SELECT COUNT(1)
INTO l_count_elc_pend_shipment
FROM inl_matches_int m,
rcv_transactions rt,
inl_ship_lines_all sl,
inl_ship_headers_all sh
WHERE sh.ship_header_id = sl.ship_header_id
AND sl.ship_line_id = rt.lcm_shipment_line_id
AND rt.transaction_id = m.to_parent_table_id
AND m.group_id = l_matches_int_error(i).group_id
AND m.to_parent_table_name = 'RCV_TRANSACTIONS'
AND sh.pending_update_flag = 'Y';
l_debug_info := 'Update inl_matches_int to PENDING for group ID: ' || l_matches_int_error(i).group_id;
UPDATE inl_matches_int mi
SET processing_status_code = 'PENDING',
mi.last_updated_by = L_FND_USER_ID,
mi.last_update_date = SYSDATE,
mi.last_update_login = L_FND_LOGIN_ID
WHERE group_id = l_matches_int_error(i).group_id;