[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE inl_ship_lines_int
SET processing_status_code = 'ERROR' ,
request_id = fnd_global.conc_request_id,
last_updated_by = fnd_global.user_id ,
last_update_date = SYSDATE ,
last_update_login = fnd_global.login_id ,
program_id = fnd_global.conc_program_id,
program_update_date = SYSDATE ,
program_application_id = fnd_global.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 = fnd_global.conc_request_id,
last_updated_by = fnd_global.user_id ,
last_update_date = SYSDATE ,
last_update_login = fnd_global.login_id ,
program_id = fnd_global.conc_program_id,
program_update_date = SYSDATE ,
program_application_id = fnd_global.prog_appl_id
WHERE match_int_id = p_parent_table_id
AND processing_status_code <> 'ERROR';
UPDATE inl_match_amounts_int
SET processing_status_code = 'ERROR' ,
request_id = fnd_global.conc_request_id,
last_updated_by = fnd_global.user_id ,
last_update_date = SYSDATE ,
last_update_login = fnd_global.login_id ,
program_id = fnd_global.conc_program_id,
program_update_date = SYSDATE ,
program_application_id = fnd_global.prog_appl_id
WHERE match_amount_int_id = p_parent_table_id
AND processing_status_code <> 'ERROR';
UPDATE inl_ship_headers_int
SET processing_status_code = 'ERROR' ,
request_id = fnd_global.conc_request_id,
last_updated_by = fnd_global.user_id ,
last_update_date = SYSDATE ,
last_update_login = fnd_global.login_id ,
program_id = fnd_global.conc_program_id,
program_update_date = SYSDATE ,
program_application_id = fnd_global.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*/
p_token1_value, /*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*/
fnd_global.user_id, /*21*/
SYSDATE, /*22*/
fnd_global.user_id, /*23*/
SYSDATE, /*24*/
fnd_global.user_id, /*25*/
fnd_global.conc_program_id, /*26*/
fnd_global.prog_appl_id, /*27*/
SYSDATE, /*28*/
fnd_global.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;
SELECT
user_defined_ship_num_code
INTO
l_user_defined_ship_num_code
FROM inl_parameters
WHERE organization_id = p_organization_id;
SELECT
ship_type_code
INTO
l_ship_type_code
FROM INL_SHIP_TYPES_B
WHERE ship_type_id = p_ship_type_id
AND active_from_date <= trunc(SYSDATE)
AND (active_to_date IS NULL
OR active_to_date >= trunc(SYSDATE))
;
SELECT
slt.ship_line_type_code
INTO
l_ship_line_type_code
FROM inl_ship_line_types_b slt
WHERE slt.ship_line_type_id = p_ship_line_type_id
AND slt.active_from_date <= trunc(SYSDATE)
AND (slt.active_to_date IS NULL
OR slt.active_to_date >= trunc(SYSDATE))
AND EXISTS (SELECT 1
FROM inl_alwd_line_types sltallow
WHERE sltallow.ship_line_type_id = p_ship_line_type_id
AND sltallow.parent_table_id = p_ship_type_id
AND sltallow.parent_table_name = 'INL_SHIP_TYPES'
AND ROWNUM < 2)
;
SELECT COUNT(1)
INTO l_org_count
FROM hr_operating_units hou
WHERE L_SYSDATE BETWEEN NVL((TRUNC(hou.date_from)), L_SYSDATE)
AND NVL((TRUNC(hou.date_to) + .99999) - 1, L_SYSDATE)
AND hou.organization_id = p_org_id;
SELECT COUNT(1)
INTO l_organization_count
FROM org_organization_definitions
WHERE organization_id = p_organization_id;
SELECT COUNT(1)
INTO l_organization_count
FROM inl_parameters ipa
WHERE ipa.organization_id = p_organization_id;
SELECT 1
INTO l_aux
FROM fnd_lookup_values_vl
WHERE lookup_type = 'INL_OI_LAST_TASK_CODES'
AND lookup_code = p_last_task_code
AND enabled_flag = 'Y'
AND start_date_active <= L_TRUNC_SYSDATE
AND
(
end_date_active IS NULL
OR end_date_active >= L_TRUNC_SYSDATE
) ;
SELECT COUNT( *)
INTO l_aux
FROM inl_ship_lines_int
WHERE ship_header_int_id = p_ship_header_int_id
AND
(
ship_header_id IS NOT NULL
OR ship_line_id IS NOT NULL
) ;
SELECT ship_line_int_id
FROM inl_ship_lines_int
WHERE ship_header_int_id = p_ship_header_int_id
AND
(
ship_header_id IS NOT NULL
OR ship_line_id IS NOT NULL
)
)
LOOP
Handle_InterfError(
p_parent_table_name => 'INL_SHIP_LINES_INT',
p_parent_table_id => c_lin_err.ship_line_int_id,
p_column_name => 'TRANSACTION_TYPE',
p_column_value => p_transaction_type,
p_error_message_name => 'INL_ERR_OI_CHK_TRX_TP_ID_NNULL',
p_token1_name => 'ID_NAME',
p_token1_value => 'SHIP_HEADER_INT_ID',
p_token2_name => 'ID_VAL',
p_token2_value => p_ship_header_int_id,
x_return_status => l_return_status
) ;
ELSIF p_transaction_type = 'UPDATE' THEN
-- p_ship_header_id cannot be null
IF p_ship_header_id IS NULL THEN
l_response := FALSE;
SELECT COUNT( *)
INTO l_aux
FROM inl_ship_lines_int a
WHERE a.ship_header_int_id = p_ship_header_int_id
AND a.ship_line_id IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM inl_ship_lines_all l,
inl_ship_line_groups g
WHERE l.ship_line_id = a.ship_line_id
AND l.ship_line_group_id = g.ship_line_group_id
AND NVL(a.ship_line_group_reference, FND_API.G_MISS_CHAR) = NVL(g.ship_line_group_reference, FND_API.G_MISS_CHAR)
AND a.ship_line_src_type_code = g.src_type_code
AND a.party_id = g.party_id
AND a.party_site_id = g.party_site_id
AND NVL(a.source_organization_id, FND_API.G_MISS_NUM) = NVL(g.source_organization_id, FND_API.G_MISS_NUM)
AND NVL(a.attribute_category_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute_category, FND_API.G_MISS_CHAR)
AND NVL(a.attribute1_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute1, FND_API.G_MISS_CHAR)
AND NVL(a.attribute2_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute2, FND_API.G_MISS_CHAR)
AND NVL(a.attribute3_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute3, FND_API.G_MISS_CHAR)
AND NVL(a.attribute4_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute4, FND_API.G_MISS_CHAR)
AND NVL(a.attribute5_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute5, FND_API.G_MISS_CHAR)
AND NVL(a.attribute6_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute6, FND_API.G_MISS_CHAR)
AND NVL(a.attribute7_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute7, FND_API.G_MISS_CHAR)
AND NVL(a.attribute8_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute8, FND_API.G_MISS_CHAR)
AND NVL(a.attribute9_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute9, FND_API.G_MISS_CHAR)
AND NVL(a.attribute10_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute10, FND_API.G_MISS_CHAR)
AND NVL(a.attribute11_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute11, FND_API.G_MISS_CHAR)
AND NVL(a.attribute12_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute12, FND_API.G_MISS_CHAR)
AND NVL(a.attribute13_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute13, FND_API.G_MISS_CHAR)
AND NVL(a.attribute14_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute14, FND_API.G_MISS_CHAR)
AND NVL(a.attribute15_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute15, FND_API.G_MISS_CHAR)
) ;
SELECT ship_line_int_id
FROM inl_ship_lines_int a
WHERE a.ship_header_int_id = p_ship_header_int_id
AND a.ship_line_id IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM inl_ship_lines_all l,
inl_ship_line_groups g
WHERE l.ship_line_id = a.ship_line_id
AND l.ship_line_group_id = g.ship_line_group_id
AND NVL(a.ship_line_group_reference, FND_API.G_MISS_CHAR) = NVL(g.ship_line_group_reference, FND_API.G_MISS_CHAR)
AND a.ship_line_src_type_code = g.src_type_code
AND a.party_id = g.party_id
AND a.party_site_id = g.party_site_id
AND NVL(a.source_organization_id, FND_API.G_MISS_NUM) = NVL(g.source_organization_id, FND_API.G_MISS_NUM)
AND NVL(a.attribute_category_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute_category, FND_API.G_MISS_CHAR)
AND NVL(a.attribute1_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute1, FND_API.G_MISS_CHAR)
AND NVL(a.attribute2_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute2, FND_API.G_MISS_CHAR)
AND NVL(a.attribute3_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute3, FND_API.G_MISS_CHAR)
AND NVL(a.attribute4_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute4, FND_API.G_MISS_CHAR)
AND NVL(a.attribute5_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute5, FND_API.G_MISS_CHAR)
AND NVL(a.attribute6_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute6, FND_API.G_MISS_CHAR)
AND NVL(a.attribute7_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute7, FND_API.G_MISS_CHAR)
AND NVL(a.attribute8_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute8, FND_API.G_MISS_CHAR)
AND NVL(a.attribute9_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute9, FND_API.G_MISS_CHAR)
AND NVL(a.attribute10_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute10, FND_API.G_MISS_CHAR)
AND NVL(a.attribute11_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute11, FND_API.G_MISS_CHAR)
AND NVL(a.attribute12_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute12, FND_API.G_MISS_CHAR)
AND NVL(a.attribute13_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute13, FND_API.G_MISS_CHAR)
AND NVL(a.attribute14_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute14, FND_API.G_MISS_CHAR)
AND NVL(a.attribute15_lg, FND_API.G_MISS_CHAR) = NVL(g.attribute15, FND_API.G_MISS_CHAR)
)
) LOOP
Handle_InterfError(
p_parent_table_name => 'INL_SHIP_LINES_INT',
p_parent_table_id => c_lin_err.ship_line_int_id,
p_column_name => 'TRANSACTION_TYPE',
p_column_value => p_transaction_type,
p_error_message_name => 'INL_ERR_OI_CHK_TRX_TP_UPD_GRP',
x_return_status => l_return_status
) ;
ELSIF p_transaction_type = 'DELETE' THEN
-- p_ship_header_id cannot be null
IF p_ship_header_id IS NULL THEN
l_response := FALSE;
SELECT COUNT( *)
INTO l_aux
FROM inl_ship_lines_int
WHERE ship_header_int_id = p_ship_header_int_id
AND (ship_header_id IS NULL
OR ship_line_id IS NULL
)
;
SELECT COUNT( *)
INTO l_aux
FROM inl_ship_lines_int
WHERE ship_header_int_id = p_ship_header_int_id;
SELECT COUNT( *)
INTO l_aux
FROM inl_ship_lines_all a
WHERE a.ship_header_id = p_ship_header_id
AND NOT EXISTS
(
SELECT 1
FROM inl_ship_lines_int b
WHERE b.ship_header_int_id = p_ship_header_int_id
AND b.ship_header_id = a.ship_header_id
AND b.ship_line_id = a.ship_line_id
)
;
IF p_transaction_type IN('UPDATE', 'DELETE')
AND p_ship_header_id IS NOT NULL
THEN
SELECT SHIP_STATUS_CODE
INTO l_status
FROM inl_ship_headers_all a
WHERE a.ship_header_id = p_ship_header_id;
PROCEDURE Delete_Ship (
p_ship_header_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_proc_name CONSTANT VARCHAR2(100) := 'Delete_Ship';
l_debug_info := 'Delete from inl_ship_holds';
DELETE FROM inl.inl_ship_holds
WHERE ship_header_id = p_ship_header_id;
l_debug_info := 'Delete from inl_allocations';
DELETE FROM inl.inl_allocations
WHERE ship_header_id = p_ship_header_id;
l_debug_info := 'Delete from inl_tax_lines';
DELETE FROM inl.inl_tax_lines
WHERE ship_header_id = p_ship_header_id;
l_debug_info := 'Delete from inl_charge_lines';
DELETE
FROM inl.inl_charge_lines cl
WHERE cl.charge_line_id
IN (
SELECT assoc.from_parent_table_id
FROM inl_associations assoc
WHERE assoc.from_parent_table_name = 'INL_CHARGE_LINES'
AND assoc.ship_header_id = p_ship_header_id
)
;
l_debug_info := 'Delete from inl_associations';
DELETE FROM inl.inl_associations
WHERE ship_header_id = p_ship_header_id;
l_debug_info := 'Delete from inl_ship_lines_all';
DELETE FROM inl.inl_ship_lines_all
WHERE ship_header_id = p_ship_header_id;
l_debug_info := 'Delete from inl_ship_line_groups';
DELETE FROM inl.inl_ship_line_groups
WHERE ship_header_id = p_ship_header_id;
l_debug_info := 'Delete from inl_ship_headers_all';
DELETE FROM inl.inl_ship_headers_all WHERE ship_header_id = p_ship_header_id;
END Delete_Ship;
SELECT h.ship_header_int_id ,
h.group_id ,
h.processing_status_code ,
h.interface_source_code ,
h.interface_source_table ,
h.interface_source_line_id,
h.validation_flag ,
h.ship_num ,
h.ship_date ,
h.ship_type_id ,
h.legal_entity_id ,
h.organization_id ,
h.location_id ,
h.org_id ,
h.taxation_country ,
h.document_sub_type ,
h.transaction_type ,
h.attribute_category ,
h.attribute1 ,
h.attribute2 ,
h.attribute3 ,
h.attribute4 ,
h.attribute5 ,
h.attribute6 ,
h.attribute7 ,
h.attribute8 ,
h.attribute9 ,
h.attribute10 ,
h.attribute11 ,
h.attribute12 ,
h.attribute13 ,
h.attribute14 ,
h.attribute15 ,
h.ship_header_id
FROM inl_ship_headers_int h
WHERE (p_group_id IS NULL
OR h.group_id = p_group_id
)
AND ( p_org_id IS NULL
OR h.org_id = p_org_id
)
AND h.last_task_code >= '10'
AND h.processing_status_code = 'RUNNING'
AND (h.transaction_type = 'DELETE'
OR EXISTS (
SELECT 1
FROM inl_ship_lines_int l
WHERE l.ship_header_int_id = h.ship_header_int_id
AND ROWNUM < 2
)
)
;
IF validHeaders_List(iHead) .transaction_type = 'DELETE' THEN
Delete_Ship(
p_ship_header_id => validHeaders_List(iHead).ship_header_id,
x_return_status => l_return_status);
UPDATE inl_ship_lines_int
SET processing_status_code = 'COMPLETED' ,
request_id = fnd_global.conc_request_id,
last_updated_by = fnd_global.user_id ,
last_update_date = SYSDATE ,
last_update_login = fnd_global.login_id ,
program_id = fnd_global.conc_program_id,
program_update_date = SYSDATE ,
program_application_id = fnd_global.prog_appl_id
WHERE ship_header_int_id = validHeaders_List(iHead) .ship_header_int_id;
SELECT user_defined_ship_num_code,
next_ship_num
INTO l_user_defined_ship_num_code,
l_next_ship_num
FROM inl_parameters
WHERE organization_id = validHeaders_List(iHead) .organization_id;
IF validHeaders_List(iHead) .transaction_type = 'UPDATE' THEN
l_debug_info := 'Updating the Shipment Header';
UPDATE inl_ship_headers
SET ship_num = DECODE(l_user_defined_ship_num_code, 'AUTOMATIC', ship_num, validHeaders_List(iHead) .ship_num), -- 02
ship_date = validHeaders_List(iHead) .ship_date , -- 03
ship_type_id = validHeaders_List(iHead) .ship_type_id , -- 04
legal_entity_id = validHeaders_List(iHead) .legal_entity_id , -- 08
organization_id = validHeaders_List(iHead) .organization_id , -- 09
location_id = validHeaders_List(iHead) .location_id , -- 10
org_id = validHeaders_List(iHead) .org_id , -- 11
taxation_country = validHeaders_List(iHead) .taxation_country , -- 12
document_sub_type = validHeaders_List(iHead) .document_sub_type , -- 13
ship_header_int_id = validHeaders_List(iHead) .ship_header_int_id , -- 14
interface_source_code = validHeaders_List(iHead) .interface_source_code , -- 15
interface_source_table = validHeaders_List(iHead) .interface_source_table , -- 16
interface_source_line_id = validHeaders_List(iHead) .interface_source_line_id , -- 17
last_updated_by = fnd_global.user_id , -- 20
last_update_date = SYSDATE , -- 21
last_update_login = fnd_global.login_id , -- 22
program_id = fnd_global.conc_program_id , -- 23
program_update_date = SYSDATE , -- 24
program_application_id = fnd_global.prog_appl_id , -- 25
request_id = fnd_global.conc_request_id , -- 26
attribute_category = validHeaders_List(iHead) .attribute_category , -- 27
attribute1 = validHeaders_List(iHead) .attribute1 , -- 28
attribute2 = validHeaders_List(iHead) .attribute2 , -- 29
attribute3 = validHeaders_List(iHead) .attribute3 , -- 30
attribute4 = validHeaders_List(iHead) .attribute4 , -- 31
attribute5 = validHeaders_List(iHead) .attribute5 , -- 32
attribute6 = validHeaders_List(iHead) .attribute6 , -- 33
attribute7 = validHeaders_List(iHead) .attribute7 , -- 34
attribute8 = validHeaders_List(iHead) .attribute8 , -- 35
attribute9 = validHeaders_List(iHead) .attribute9 , -- 36
attribute10 = validHeaders_List(iHead) .attribute10 , -- 37
attribute11 = validHeaders_List(iHead) .attribute11 , -- 38
attribute12 = validHeaders_List(iHead) .attribute12 , -- 39
attribute13 = validHeaders_List(iHead) .attribute13 , -- 40
attribute14 = validHeaders_List(iHead) .attribute14 , -- 41
attribute15 = validHeaders_List(iHead) .attribute15 -- 42
WHERE ship_header_id = validHeaders_List(iHead) .ship_header_id;
SELECT inl_ship_headers_all_s.NEXTVAL
INTO validHeaders_List(iHead) .ship_header_id
FROM dual;
UPDATE inl_parameters
SET next_ship_num = l_next_ship_num + 1,
last_updated_by = fnd_global.user_id ,
last_update_date = SYSDATE ,
last_update_login = fnd_global.login_id
WHERE organization_id = validHeaders_List(iHead).organization_id;
l_debug_info := 'Insert into Shipment Headers table';
INSERT
INTO inl_ship_headers (
ship_header_id , -- 01
ship_num , -- 02
ship_date , -- 03
ship_type_id , -- 04
ship_status_code , -- 05
pending_matching_flag , -- 06
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
adjustment_num , -- 17a
created_by , -- 18
creation_date , -- 19
last_updated_by , -- 20
last_update_date , -- 21
last_update_login , -- 22
program_id , -- 23
program_update_date , -- 24
program_application_id , -- 25
request_id , -- 26
attribute_category , -- 27
attribute1 , -- 28
attribute2 , -- 29
attribute3 , -- 30
attribute4 , -- 31
attribute5 , -- 32
attribute6 , -- 33
attribute7 , -- 34
attribute8 , -- 35
attribute9 , -- 36
attribute10 , -- 37
attribute11 , -- 38
attribute12 , -- 39
attribute13 , -- 40
attribute14 , -- 41
attribute15 -- 42
) VALUES (
validHeaders_List(iHead) .ship_header_id , -- 01
l_ship_num , -- 02
validHeaders_List(iHead) .ship_date , -- 03
validHeaders_List(iHead) .ship_type_id , -- 04
'INCOMPLETE' , -- 05
NULL , -- 06
validHeaders_List(iHead) .legal_entity_id , -- 08
validHeaders_List(iHead) .organization_id , -- 09
validHeaders_List(iHead) .location_id , -- 10
validHeaders_List(iHead) .org_id , -- 11
validHeaders_List(iHead) .taxation_country , -- 12
validHeaders_List(iHead) .document_sub_type , -- 13
validHeaders_List(iHead) .ship_header_int_id , -- 14
validHeaders_List(iHead) .interface_source_code , -- 15
validHeaders_List(iHead) .interface_source_table , -- 16
validHeaders_List(iHead) .interface_source_line_id , -- 17
0 , -- 17a
fnd_global.user_id , -- 18
SYSDATE , -- 19
fnd_global.user_id , -- 20
SYSDATE , -- 21
fnd_global.login_id , -- 22
fnd_global.conc_program_id , -- 23
SYSDATE , -- 24
fnd_global.prog_appl_id , -- 25
fnd_global.conc_request_id , -- 26
validHeaders_List(iHead) .attribute_category , -- 27
validHeaders_List(iHead) .attribute1 , -- 28
validHeaders_List(iHead) .attribute2 , -- 29
validHeaders_List(iHead) .attribute3 , -- 30
validHeaders_List(iHead) .attribute4 , -- 31
validHeaders_List(iHead) .attribute5 , -- 32
validHeaders_List(iHead) .attribute6 , -- 33
validHeaders_List(iHead) .attribute7 , -- 34
validHeaders_List(iHead) .attribute8 , -- 35
validHeaders_List(iHead) .attribute9 , -- 36
validHeaders_List(iHead) .attribute10 , -- 37
validHeaders_List(iHead) .attribute11 , -- 38
validHeaders_List(iHead) .attribute12 , -- 39
validHeaders_List(iHead) .attribute13 , -- 40
validHeaders_List(iHead) .attribute14 , -- 41
validHeaders_List(iHead) .attribute15 -- 42
) ;
UPDATE inl_ship_headers_int
SET processing_status_code = 'COMPLETED' ,
ship_header_id = validHeaders_List(iHead).ship_header_id,
request_id = fnd_global.conc_request_id ,
last_updated_by = fnd_global.user_id ,
last_update_date = SYSDATE ,
last_update_login = fnd_global.login_id ,
program_id = fnd_global.conc_program_id ,
program_update_date = SYSDATE ,
program_application_id = fnd_global.prog_appl_id
WHERE ship_header_int_id = validHeaders_List(iHead).ship_header_int_id;
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 */
attribute_category, /* 09 */
attribute1, /* 10 */
attribute2, /* 11 */
attribute3, /* 12 */
attribute4, /* 13 */
attribute5, /* 14 */
attribute6, /* 15 */
attribute7, /* 16 */
attribute8, /* 17 */
attribute9, /* 18 */
attribute10, /* 19 */
attribute11, /* 20 */
attribute12, /* 21 */
attribute13, /* 22 */
attribute14, /* 23 */
attribute15 /* 24 */
INTO
rec_ship_line_groups.ship_line_group_id, /* 01 */
rec_ship_line_groups.ship_line_group_reference,/* 02 */
rec_ship_line_groups.ship_header_id, /* 03 */
rec_ship_line_groups.ship_line_group_num, /* 04 */
rec_ship_line_groups.src_type_code, /* 05 */
rec_ship_line_groups.party_id, /* 06 */
rec_ship_line_groups.party_site_id, /* 07 */
rec_ship_line_groups.source_organization_id, /* 08 */
rec_ship_line_groups.attribute_category, /* 09 */
rec_ship_line_groups.attribute1, /* 10 */
rec_ship_line_groups.attribute2, /* 11 */
rec_ship_line_groups.attribute3, /* 12 */
rec_ship_line_groups.attribute4, /* 13 */
rec_ship_line_groups.attribute5, /* 14 */
rec_ship_line_groups.attribute6, /* 15 */
rec_ship_line_groups.attribute7, /* 16 */
rec_ship_line_groups.attribute8, /* 17 */
rec_ship_line_groups.attribute9, /* 18 */
rec_ship_line_groups.attribute10, /* 19 */
rec_ship_line_groups.attribute11, /* 20 */
rec_ship_line_groups.attribute12, /* 21 */
rec_ship_line_groups.attribute13, /* 22 */
rec_ship_line_groups.attribute14, /* 23 */
rec_ship_line_groups.attribute15 /* 24 */
FROM inl_ship_line_groups
WHERE ship_header_id = p_ship_header_id
AND NVL(src_type_code, FND_API.G_MISS_CHAR)= NVL(validLines_List(p_line_index).ship_line_src_type_code, FND_API.G_MISS_CHAR)
AND party_id = validLines_List(p_line_index).party_id
AND party_site_id = validLines_List(p_line_index).party_site_id
AND source_organization_id = validLines_List(p_line_index).source_organization_id
AND NVL(ship_line_group_reference, FND_API.G_MISS_NUM)= NVL(validLines_List(p_line_index).ship_line_group_reference, FND_API.G_MISS_NUM) ;
SELECT inl_ship_line_groups_s.NEXTVAL
INTO rec_ship_line_groups.ship_line_group_id
FROM dual;
SELECT MAX(ship_line_group_num)
INTO rec_ship_line_groups.ship_line_group_num
FROM inl_ship_line_groups
WHERE ship_header_id = p_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
(
rec_ship_line_groups.ship_line_group_id, /* 01 */
rec_ship_line_groups.ship_line_group_reference, /* 02 */
p_ship_header_id, /* 03 */
rec_ship_line_groups.ship_line_group_num, /* 04 */
rec_ship_line_groups.src_type_code, /* 05 */
rec_ship_line_groups.party_id, /* 06 */
rec_ship_line_groups.party_site_id, /* 07 */
rec_ship_line_groups.source_organization_id, /* 08 */
validLines_List(p_line_index) .ship_line_int_id, /* 09 */
validLines_List(p_line_index) .interface_source_table, /* 10 */
validLines_List(p_line_index) .interface_source_line_id,/* 11 */
fnd_global.user_id, /* 12 */
SYSDATE, /* 13 */
fnd_global.user_id, /* 14 */
SYSDATE, /* 15 */
fnd_global.login_id, /* 16 */
fnd_global.conc_program_id, /* 17 */
SYSDATE, /* 18 */
fnd_global.prog_appl_id, /* 19 */
fnd_global.conc_request_id, /* 20 */
validLines_List(p_line_index) .attribute_category_lg, /* 21 */
validLines_List(p_line_index) .attribute1_lg, /* 22 */
validLines_List(p_line_index) .attribute2_lg, /* 23 */
validLines_List(p_line_index) .attribute3_lg, /* 24 */
validLines_List(p_line_index) .attribute4_lg, /* 25 */
validLines_List(p_line_index) .attribute5_lg, /* 26 */
validLines_List(p_line_index) .attribute6_lg, /* 27 */
validLines_List(p_line_index) .attribute7_lg, /* 28 */
validLines_List(p_line_index) .attribute8_lg, /* 29 */
validLines_List(p_line_index) .attribute9_lg, /* 30 */
validLines_List(p_line_index) .attribute10_lg, /* 31 */
validLines_List(p_line_index) .attribute11_lg, /* 32 */
validLines_List(p_line_index) .attribute12_lg, /* 33 */
validLines_List(p_line_index) .attribute13_lg, /* 34 */
validLines_List(p_line_index) .attribute14_lg, /* 35 */
validLines_List(p_line_index) .attribute15_lg /* 36 */
) ;
SELECT h.ship_header_int_id,
h.ship_header_id ,
h.transaction_type
FROM inl_ship_headers_int h
WHERE (p_group_id IS NULL
OR group_id = p_group_id)
AND (p_org_id IS NULL
OR h.org_id = p_org_id)
AND h.processing_status_code = 'COMPLETED'
AND h.transaction_type <> 'DELETE'
AND h.last_task_code >= '10'
AND EXISTS (
SELECT 1
FROM inl_ship_lines_int l
WHERE l.ship_header_int_id = h.ship_header_int_id
AND l.processing_status_code = 'RUNNING'
AND ROWNUM < 2
);
OR (headersToProcess_List(iHead) .transaction_type = 'UPDATE'
AND validLines_List(iLine).ship_Line_id IS NULL
)
THEN
-- Check if the Line Group has already been imported
Handle_LineGroups(p_ship_header_id => headersToProcess_List(iHead) .ship_header_id, p_line_index => iLine, x_return_status => l_return_status) ;
SELECT MAX(ship_line_num)
INTO validLines_List(iLine).ship_line_num
FROM inl_ship_lines_all
WHERE ship_header_id = headersToProcess_List(iHead) .ship_header_id
AND ship_line_group_id = rec_ship_line_groups.ship_line_group_id;
SELECT inl_ship_Lines_all_s.NEXTVAL
INTO l_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
(
headersToProcess_List(iHead) .ship_header_id, /* 01 */
rec_ship_line_groups.ship_line_group_id, /* 02 */
l_ship_line_id, /* 03 */
validLines_List(iLine).ship_line_num, /* 04 */
validLines_List(iLine).ship_line_type_id, /* 05 */
validLines_List(iLine).ship_line_src_type_code, /* 06 */
validLines_List(iLine).ship_line_source_id, /* 07 */
NULL, /* 08 */
0, /* 09 */
NULL, /* 10 */
validLines_List(iLine).currency_code, /* 12 */
validLines_List(iLine).currency_conversion_type, /* 13 */
validLines_List(iLine).currency_conversion_date, /* 14 */
validLines_List(iLine).currency_conversion_rate, /* 15 */
validLines_List(iLine).inventory_item_id, /* 16 */
validLines_List(iLine).txn_qty, /* 17 */
validLines_List(iLine).txn_uom_code, /* 18 */
validLines_List(iLine).txn_unit_price, /* 19 */
validLines_List(iLine).primary_qty, /* 20 */
validLines_List(iLine).primary_uom_code, /* 21 */
validLines_List(iLine).primary_unit_price, /* 22 */
validLines_List(iLine).secondary_qty, /* 23 */
validLines_List(iLine).secondary_uom_code, /* 24 */
validLines_List(iLine).secondary_unit_price, /* 25 */
validLines_List(iLine).landed_cost_flag, /* 30 */
validLines_List(iLine).allocation_enabled_flag, /* 31 */
validLines_List(iLine).trx_business_category, /* 32 */
validLines_List(iLine).intended_use, /* 33 */
validLines_List(iLine).product_fiscal_class, /* 34 */
validLines_List(iLine).product_category, /* 35 */
validLines_List(iLine).product_type, /* 36 */
validLines_List(iLine).user_def_fiscal_class, /* 37 */
validLines_List(iLine).tax_classification_code, /* 38 */
validLines_List(iLine).assessable_value, /* 39 */
'N', /* 40 */
validLines_List(iLine).ship_from_party_id, /* 41 */
validLines_List(iLine).ship_from_party_site_id, /* 42 */
validLines_List(iLine).ship_to_organization_id, /* 43 */
validLines_List(iLine).ship_to_location_id, /* 44 */
validLines_List(iLine).bill_from_party_id, /* 45 */
validLines_List(iLine).bill_from_party_site_id, /* 46 */
validLines_List(iLine).bill_to_organization_id, /* 47 */
validLines_List(iLine).bill_to_location_id, /* 48 */
validLines_List(iLine).poa_party_id, /* 49 */
validLines_List(iLine).poa_party_site_id, /* 50 */
validLines_List(iLine).poo_organization_id, /* 51 */
validLines_List(iLine).poo_location_id, /* 52 */
validLines_List(iLine).org_id, /* 53 */
validLines_List(iLine).ship_line_int_id, /* 54 */
validLines_List(iLine).interface_source_table, /* 55 */
validLines_List(iLine).interface_source_line_id, /* 56 */
fnd_global.user_id, /* 57 */
SYSDATE, /* 58 */
fnd_global.user_id, /* 59 */
SYSDATE, /* 60 */
fnd_global.login_id, /* 61 */
fnd_global.conc_program_id, /* 62 */
SYSDATE, /* 63 */
fnd_global.prog_appl_id, /* 64 */
fnd_global.conc_request_id, /* 65 */
validLines_List(iLine).attribute_category_sl, /* 66 */
validLines_List(iLine).attribute1_sl, /* 67 */
validLines_List(iLine).attribute2_sl, /* 68 */
validLines_List(iLine).attribute3_sl, /* 69 */
validLines_List(iLine).attribute4_sl, /* 70 */
validLines_List(iLine).attribute5_sl, /* 71 */
validLines_List(iLine).attribute6_sl, /* 72 */
validLines_List(iLine).attribute7_sl, /* 73 */
validLines_List(iLine).attribute8_sl, /* 74 */
validLines_List(iLine).attribute9_sl, /* 75 */
validLines_List(iLine).attribute10_sl, /* 76 */
validLines_List(iLine).attribute11_sl, /* 77 */
validLines_List(iLine).attribute12_sl, /* 78 */
validLines_List(iLine).attribute13_sl, /* 79 */
validLines_List(iLine).attribute14_sl, /* 80 */
validLines_List(iLine).attribute15_sl /* 81 */
) ;
l_debug_info := 'Update Shipment Lines table';
UPDATE inl_ship_lines_all
SET ship_line_num = NVL(validLines_List(iLine).ship_line_num, ship_line_num),/* 01 */
ship_line_type_id = validLines_List(iLine).ship_line_type_id, /* 02 */
ship_line_src_type_code = validLines_List(iLine).ship_line_src_type_code, /* 03 */
ship_line_source_id = validLines_List(iLine).ship_line_source_id, /* 04 */
adjustment_num = 0, /* 05 */
match_id = NULL, /* 06 */
currency_code = validLines_List(iLine).currency_code, /* 07 */
currency_conversion_type = validLines_List(iLine).currency_conversion_type, /* 08 */
currency_conversion_date = validLines_List(iLine).currency_conversion_date, /* 09 */
currency_conversion_rate = validLines_List(iLine).currency_conversion_rate, /* 20 */
inventory_item_id = validLines_List(iLine).inventory_item_id, /* 21 */
txn_qty = validLines_List(iLine).txn_qty, /* 22 */
txn_uom_code = validLines_List(iLine).txn_uom_code, /* 23 */
txn_unit_price = validLines_List(iLine).txn_unit_price, /* 24 */
primary_qty = validLines_List(iLine).primary_qty, /* 25 */
primary_uom_code = validLines_List(iLine).primary_uom_code, /* 26 */
primary_unit_price = validLines_List(iLine).primary_unit_price, /* 27 */
secondary_qty = validLines_List(iLine).secondary_qty, /* 28 */
secondary_uom_code = validLines_List(iLine).secondary_uom_code, /* 29 */
secondary_unit_price = validLines_List(iLine).secondary_unit_price, /* 30 */
landed_cost_flag = validLines_List(iLine).landed_cost_flag, /* 31 */
allocation_enabled_flag = validLines_List(iLine).allocation_enabled_flag, /* 32 */
trx_business_category = validLines_List(iLine).trx_business_category, /* 33 */
intended_use = validLines_List(iLine).intended_use, /* 34 */
product_fiscal_class = validLines_List(iLine).product_fiscal_class, /* 35 */
product_category = validLines_List(iLine).product_category, /* 36 */
product_type = validLines_List(iLine).product_type, /* 37 */
user_def_fiscal_class = validLines_List(iLine).user_def_fiscal_class, /* 38 */
tax_classification_code = validLines_List(iLine).tax_classification_code, /* 39 */
assessable_value = validLines_List(iLine).assessable_value, /* 40 */
ship_from_party_id = validLines_List(iLine).ship_from_party_id, /* 41 */
ship_from_party_site_id = validLines_List(iLine).ship_from_party_site_id, /* 42 */
ship_to_organization_id = validLines_List(iLine).ship_to_organization_id, /* 43 */
ship_to_location_id = validLines_List(iLine).ship_to_location_id, /* 44 */
bill_from_party_id = validLines_List(iLine).bill_from_party_id, /* 45 */
bill_from_party_site_id = validLines_List(iLine).bill_from_party_site_id, /* 46 */
bill_to_organization_id = validLines_List(iLine).bill_to_organization_id, /* 47 */
bill_to_location_id = validLines_List(iLine).bill_to_location_id, /* 48 */
poa_party_id = validLines_List(iLine).poa_party_id, /* 49 */
poa_party_site_id = validLines_List(iLine).poa_party_site_id, /* 50 */
poo_organization_id = validLines_List(iLine).poo_organization_id, /* 51 */
poo_location_id = validLines_List(iLine).poo_location_id, /* 52 */
org_id = validLines_List(iLine).org_id, /* 53 */
ship_line_int_id = validLines_List(iLine).ship_line_int_id, /* 54 */
interface_source_table = validLines_List(iLine).interface_source_table, /* 55 */
interface_source_line_id = validLines_List(iLine).interface_source_line_id, /* 56 */
last_updated_by = fnd_global.user_id, /* 57 */
last_update_date = SYSDATE, /* 58 */
last_update_login = fnd_global.login_id, /* 59 */
program_id = fnd_global.conc_program_id, /* 60 */
program_update_date = SYSDATE, /* 61 */
program_application_id = fnd_global.prog_appl_id, /* 62 */
request_id = fnd_global.conc_request_id, /* 63 */
attribute_category = validLines_List(iLine).attribute_category_sl, /* 64 */
attribute1 = validLines_List(iLine).attribute1_sl, /* 65 */
attribute2 = validLines_List(iLine).attribute2_sl, /* 66 */
attribute3 = validLines_List(iLine).attribute3_sl, /* 67 */
attribute4 = validLines_List(iLine).attribute4_sl, /* 68 */
attribute5 = validLines_List(iLine).attribute5_sl, /* 69 */
attribute6 = validLines_List(iLine).attribute6_sl, /* 70 */
attribute7 = validLines_List(iLine).attribute7_sl, /* 71 */
attribute8 = validLines_List(iLine).attribute8_sl, /* 72 */
attribute9 = validLines_List(iLine).attribute9_sl, /* 73 */
attribute10 = validLines_List(iLine).attribute10_sl, /* 74 */
attribute11 = validLines_List(iLine).attribute11_sl, /* 75 */
attribute12 = validLines_List(iLine).attribute12_sl, /* 76 */
attribute13 = validLines_List(iLine).attribute13_sl, /* 77 */
attribute14 = validLines_List(iLine).attribute14_sl, /* 78 */
attribute15 = validLines_List(iLine).attribute15_sl /* 79 */
WHERE ship_line_id = l_ship_line_id;
UPDATE inl_ship_lines_int
SET processing_status_code = 'COMPLETED' ,
ship_header_id = headersToProcess_List(iHead).ship_header_id,
ship_line_id = l_ship_line_id ,
request_id = fnd_global.conc_request_id ,
last_updated_by = fnd_global.user_id ,
last_update_date = SYSDATE ,
last_update_login = fnd_global.login_id ,
program_id = fnd_global.conc_program_id ,
program_update_date = SYSDATE ,
program_application_id = fnd_global.prog_appl_id
WHERE ship_line_int_id = validLines_List(iLine).ship_line_int_id;
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,
l_org_id,
l_inventory_item_id,
l_match_type
FROM ap_invoice_distributions d,
ap_invoice_lines l
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
WHERE ship_header_id = x_new_to_parent_table_id;
SELECT ship_header_id
INTO x_ship_header_id
FROM inl_ship_lines
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 inl_match_Amounts_s.NEXTVAL new_match_Amount_id,
matched_amt ,
matched_curr_code ,
matched_curr_conversion_type,
matched_curr_conversion_date,
matched_curr_conversion_rate,
match_amount_int_id,
transaction_type
FROM inl_match_amounts_int
WHERE processing_status_code = 'PENDING'
AND (p_group_id IS NULL
OR p_group_id = group_id)
;
SELECT inl_matches_s.NEXTVAL new_match_id,
match_type_code ,
from_parent_table_name,
from_parent_table_id ,
to_parent_table_name ,
to_parent_table_id ,
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 ,
charge_line_type_id ,
party_id ,
party_site_id ,
tax_code ,
nrec_tax_amt ,
tax_amt_included_flag,
match_amount_int_id ,
match_int_id ,
transaction_type
FROM inl_matches_int
WHERE 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 = group_id)
;
INSERT INTO inl_match_Amounts(
match_amount_id ,
matched_amt ,
matched_curr_code ,
matched_curr_conversion_type,
matched_curr_conversion_date,
matched_curr_conversion_rate,
match_amount_int_id ,
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 ,
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).match_amount_int_id ,
fnd_global.conc_program_id ,
SYSDATE ,
fnd_global.prog_appl_id ,
fnd_global.conc_request_id ,
fnd_global.user_id ,
SYSDATE ,
fnd_global.user_id ,
SYSDATE ,
fnd_global.login_id
);
UPDATE inl_match_Amounts_int m
SET m.processing_status_code = 'COMPLETED' ,
m.request_id = fnd_global.conc_request_id,
m.last_updated_by = fnd_global.user_id ,
m.last_update_date = SYSDATE ,
m.last_update_login = fnd_global.login_id ,
m.program_id = fnd_global.conc_program_id,
m.program_update_date = SYSDATE ,
m.program_application_id = fnd_global.prog_appl_id
WHERE m.match_Amount_int_id = matchAmountsToProcess_List(iMatch).match_Amount_int_id;
INSERT INTO inl_matches(
match_id , /* 01 */
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).match_type_code , /* 02 */
l_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_amount_int_id,
NULL, NULL,
(SELECT ima.match_amount_id
FROM inl_match_amounts ima
WHERE ima.match_amount_int_id =
matchesToProcess_List(iMatch).match_amount_int_id)
), /* 25 */
matchesToProcess_List(iMatch).match_int_id , /* 26 */
fnd_global.conc_program_id , /* 27 */
SYSDATE , /* 28 */
fnd_global.prog_appl_id , /* 29 */
fnd_global.conc_request_id , /* 30 */
fnd_global.user_id , /* 31 */
SYSDATE , /* 32 */
fnd_global.user_id , /* 33 */
SYSDATE , /* 34 */
fnd_global.login_id /* 35 */
);
UPDATE inl_ship_headers sH
SET sH.pending_matching_flag = 'Y',
sH.request_id = fnd_global.conc_request_id,
sH.last_updated_by = fnd_global.user_id,
sH.last_update_date = SYSDATE,
sH.last_update_login = fnd_global.login_id,
sH.program_id = fnd_global.conc_program_id,
sH.program_update_date = SYSDATE,
sH.program_application_id = fnd_global.prog_appl_id
WHERE sH.ship_header_id = l_ship_header_id
;
UPDATE inl_matches_int m
SET m.processing_status_code = 'COMPLETED' ,
m.request_id = fnd_global.conc_request_id,
m.last_updated_by = fnd_global.user_id ,
m.last_update_date = SYSDATE ,
m.last_update_login = fnd_global.login_id ,
m.program_id = fnd_global.conc_program_id,
m.program_update_date = SYSDATE ,
m.program_application_id = fnd_global.prog_appl_id
WHERE m.match_int_id = matchesToProcess_List(iMatch) .match_int_id;
l_updated_flag VARCHAR2(1) ;
UPDATE inl_ship_headers_int sh
SET sh.location_id = (
SELECT hou.location_id
FROM hr_organization_units hou,
hr_locations hl
WHERE hl.location_id = hou.location_id
AND hl.receiving_site_flag = 'Y'
AND hou.organization_id = sh.organization_id
)
WHERE sh.ship_header_int_id = p_ship_header_int_id
AND sh.location_id IS NULL;
l_updated_flag VARCHAR2(1) ;
SELECT ship_line_int_id ,
party_id ,
party_number ,
party_site_id ,
party_site_number ,
source_organization_id ,
source_organization_code ,
ship_line_type_id ,
ship_line_type_code ,
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 ,
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
FROM inl_ship_lines_int
WHERE ship_header_int_id = P_ship_header_int_id
AND processing_status_code = 'PENDING'; --'ERROR' IN CASE OF FAIL
l_updated_flag := 'N';
l_updated_flag := 'Y';
l_updated_flag := 'Y';
IF l_updated_flag = 'Y' THEN
UPDATE inl_ship_lines_int
SET party_id = toDeriveLines_list(i) .party_id ,
party_site_id = toDeriveLines_list(i) .party_site_id ,
source_organization_id = toDeriveLines_list(i) .source_organization_id ,
ship_line_type_id = toDeriveLines_list(i) .ship_line_type_id ,
inventory_item_id = toDeriveLines_list(i) .inventory_item_id ,
primary_qty = toDeriveLines_list(i) .primary_qty ,
primary_uom_code = toDeriveLines_list(i) .primary_uom_code ,
primary_unit_price = toDeriveLines_list(i) .primary_unit_price ,
secondary_qty = toDeriveLines_list(i) .secondary_qty ,
secondary_uom_code = toDeriveLines_list(i) .secondary_uom_code ,
secondary_unit_price = toDeriveLines_list(i) .secondary_unit_price ,
ship_from_party_id = toDeriveLines_list(i) .ship_from_party_id ,
ship_from_party_site_id = toDeriveLines_list(i) .ship_from_party_site_id,
ship_to_organization_id = toDeriveLines_list(i) .ship_to_organization_id,
ship_to_location_id = toDeriveLines_list(i) .ship_to_location_id ,
bill_from_party_id = toDeriveLines_list(i) .bill_from_party_id ,
bill_from_party_site_id = toDeriveLines_list(i) .bill_from_party_site_id,
bill_to_organization_id = toDeriveLines_list(i) .bill_to_organization_id,
bill_to_location_id = toDeriveLines_list(i) .bill_to_location_id ,
poa_party_id = toDeriveLines_list(i) .poa_party_id ,
poa_party_site_id = toDeriveLines_list(i) .poa_party_site_id ,
poo_organization_id = toDeriveLines_list(i) .poo_organization_id ,
poo_location_id = toDeriveLines_list(i) .poo_location_id ,
request_id = fnd_global.conc_request_id ,
last_updated_by = fnd_global.user_id ,
last_update_date = SYSDATE ,
last_update_login = fnd_global.login_id ,
program_id = fnd_global.conc_program_id ,
program_update_date = SYSDATE ,
program_application_id = fnd_global.prog_appl_id
WHERE ship_line_int_id = toDeriveLines_list(i) .ship_line_int_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
;
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_func_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 ship_header_id,
ship_line_num ,
inventory_item_id ,
ship_line_group_id
INTO l_ship_header_id,
l_ship_line_num ,
l_inventory_item_id ,
l_ship_line_group_id
FROM inl_ship_lines sl
WHERE ship_line_id = p_ship_line_id;
SELECT organization_id
INTO l_organization_id
FROM inl_ship_headers
WHERE ship_header_id = l_ship_header_id;
SELECT txn_qty,
txn_uom_code
INTO l_qty,
l_uom_code
FROM inl_adj_ship_lines_v sl
WHERE sl.ship_header_id = l_ship_header_id
AND sl.ship_line_group_id = l_ship_line_group_id
AND sl.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;
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
WHERE ship_header_id = p_to_parent_table_id;
SELECT ship_header_id
INTO l_ship_header_id
FROM inl_ship_lines
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 ship_header_id
INTO l_ship_header_id
FROM inl_ship_lines_int
WHERE ship_line_int_id = p_to_parent_table_id;
SELECT ship_header_id
INTO l_ship_header_id
FROM inl_ship_lines
WHERE ship_line_int_id = p_to_parent_table_id;
SELECT ship_header_id
INTO l_ship_header_id
FROM inl_ship_headers_int
WHERE ship_header_int_id = p_to_parent_table_id;
SELECT ship_header_id
INTO l_ship_header_id
FROM inl_ship_headers
WHERE ship_header_int_id = p_to_parent_table_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 ship_header_id
INTO l_ship_header_id
FROM inl_ship_lines
WHERE 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 := FND_API.G_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) ;
SELECT match_amount_int_id ,
group_id ,
transaction_type ,
matched_amt ,
matched_curr_code ,
matched_curr_conversion_type,
matched_curr_conversion_date,
matched_curr_conversion_rate
FROM inl_matches_int m
WHERE (p_group_id IS NULL
OR m.group_id = p_group_id
)
AND m.processing_status_code = 'PENDING';
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_amount_int_id,
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';
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 = fnd_global.conc_request_id ,
last_updated_by = fnd_global.user_id ,
last_update_date = SYSDATE ,
last_update_login = fnd_global.login_id ,
program_id = fnd_global.conc_program_id ,
program_update_date = SYSDATE ,
program_application_id = fnd_global.prog_appl_id
WHERE match_int_id = match_int_list(i).match_int_id;
l_debug_info := 'Delete errors from previous analysis performed on the current line. Call Reset_InterfError';
SELECT SUM(matched_amt)
INTO l_amt_sum
FROM inl_matches_int m
WHERE m.match_amount_int_id = match_amounts_int_list(i).match_amount_int_id
AND m.matched_curr_code = match_amounts_int_list(i).matched_curr_code
;
UPDATE inl_matches_int m
SET processing_status_code = 'ERROR'
WHERE m.match_amount_int_id = match_amounts_int_list(i).match_amount_int_id
;
UPDATE inl_matches_int
SET processing_status_code = l_processing_status_code ,
party_id = match_int_list(i) .party_id ,
party_site_id = match_int_list(i) .party_site_id,
request_id = fnd_global.conc_request_id ,
last_updated_by = fnd_global.user_id ,
last_update_date = SYSDATE ,
last_update_login = fnd_global.login_id ,
program_id = fnd_global.conc_program_id ,
program_update_date = SYSDATE ,
program_application_id = fnd_global.prog_appl_id
WHERE match_int_id = match_int_list(i) .match_int_id;
SELECT ship_header_int_id,
validation_flag ,
last_task_code ,
organization_id ,
org_id ,
transaction_type ,
ship_header_id ,
ship_num ,
ship_type_id
FROM inl_ship_headers_int h
WHERE
(
p_group_id IS NULL
OR h.group_id = p_group_id
)
AND (
p_org_id IS NULL
OR h.org_id = p_org_id
)
AND h.processing_status_code = 'PENDING'
AND
(
h.transaction_type = 'DELETE'
OR EXISTS
(
SELECT 1
FROM inl_ship_lines_int l
WHERE l.ship_header_int_id = h.ship_header_int_id
AND ROWNUM < 2
)
) ;
SELECT
ship_header_int_id ,
ship_line_int_id ,
ship_line_type_id
FROM inl_ship_lines_int l
WHERE l.processing_status_code = 'PENDING'
AND l.ship_header_int_id = p_ship_header_int_id;
l_debug_info := 'Delete errors from previous analysis. Call Reset_InterfError';
UPDATE inl_ship_headers_int
SET processing_status_code = 'RUNNING' ,
request_id = fnd_global.conc_request_id,
last_updated_by = fnd_global.user_id ,
last_update_date = SYSDATE ,
last_update_login = fnd_global.login_id ,
program_id = fnd_global.conc_program_id,
program_update_date = SYSDATE ,
program_application_id = fnd_global.prog_appl_id
WHERE ship_header_int_id = l_ship_header_int_id;
UPDATE inl_ship_lines_int
SET processing_status_code = 'RUNNING' ,
request_id = fnd_global.conc_request_id,
last_updated_by = fnd_global.user_id ,
last_update_date = SYSDATE ,
last_update_login = fnd_global.login_id ,
program_id = fnd_global.conc_program_id,
program_update_date = SYSDATE ,
program_application_id = fnd_global.prog_appl_id
WHERE ship_header_int_id = l_ship_header_int_id
AND processing_status_code = 'PENDING';
UPDATE inl_ship_headers_int
SET processing_status_code = 'COMPLETED' ,
request_id = fnd_global.conc_request_id,
last_updated_by = fnd_global.user_id ,
last_update_date = SYSDATE ,
last_update_login = fnd_global.login_id ,
program_id = fnd_global.conc_program_id,
program_update_date = SYSDATE ,
program_application_id = fnd_global.prog_appl_id
WHERE ship_header_int_id = l_ship_header_int_id;
UPDATE inl_ship_lines_int
SET processing_status_code = 'COMPLETED' ,
request_id = fnd_global.conc_request_id,
last_updated_by = fnd_global.user_id ,
last_update_date = SYSDATE ,
last_update_login = fnd_global.login_id ,
program_id = fnd_global.conc_program_id,
program_update_date = SYSDATE ,
program_application_id = fnd_global.prog_appl_id
WHERE ship_header_int_id = l_ship_header_int_id
AND processing_status_code = 'PENDING';
SELECT h.ship_header_id,
h.ship_header_int_id,
h.last_task_code ,
h.transaction_type
FROM inl_ship_headers_int h
WHERE
(
p_group_id IS NULL
OR h.group_id = p_group_id
)
AND (
p_org_id IS NULL
OR h.org_id = p_org_id
)
AND h.processing_status_code = 'COMPLETED'
AND h.transaction_type <> 'DELETE'
AND h.last_task_code >= '20'
AND EXISTS
(
SELECT 1
FROM inl_ship_lines_int l
WHERE l.ship_header_int_id = h.ship_header_int_id
AND ROWNUM < 2
)
AND h.request_id = fnd_global.conc_request_id;
SELECT MAX(last_task_code)
INTO l_mx_last_task_code
FROM inl_ship_headers_int h
WHERE (p_group_id IS NULL
OR h.group_id = p_group_id)
AND (p_org_id IS NULL
OR h.org_id = p_org_id)
AND h.processing_status_code = 'RUNNING'
AND (h.transaction_type = 'DELETE'
OR EXISTS (SELECT 1
FROM inl_ship_lines_int l
WHERE l.ship_header_int_id = h.ship_header_int_id
AND ROWNUM < 2
)
)
;