[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_program_update_date DATE;
l_last_update_date DATE;
l_last_update_login NUMBER;
l_last_updated_by NUMBER;
l_last_updated_by_name VARCHAR2(100);
SELECT INTERFACE_REFERENCE_ID
,TRANSACTION_ID
,ROW_IDENTIFIER
,ATTR_GROUP_ID
,ATTR_GROUP_APP_ID
,ATTR_GROUP_NAME
,DATA_LEVEL_1
,DATA_LEVEL_2
,DATA_LEVEL_3
,TRANSACTION_TYPE
,ATTR_NAME
,ATTR_VALUE_STR
,ATTR_VALUE_NUM
,ATTR_VALUE_DATE
,ATTR_DISP_VALUE
,ATTR_UNIT_OF_MEASURE
,USER_ROW_IDENTIFIER
,UDA_TEMPLATE_ID
,PROCESS_STATUS
,PROCESS_PHASE
,REQUEST_ID
,PROGRAM_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_UPDATE_DATE
,LAST_UPDATE_DATE
,CREATION_DATE
,LAST_UPDATE_LOGIN
,CREATED_BY
,LAST_UPDATED_BY
,CREATED_BY_NAME
,LAST_UPDATED_BY_NAME
,INTERFACE_SOURCE_CODE
,INTERFACE_SOURCE_LINE_ID
FROM PO_UDA_INTERFACE
WHERE PROCESS_STATUS = PO_UDA_IMPORT_PKG.IN_PROGRESS
AND NVL(PK1_VALUE, -999) = NVL(p_pk1_value, -999)
AND NVL(PK2_VALUE, -999) = NVL(p_pk2_value, -999)
AND NVL(PK3_VALUE, -999) = NVL(p_pk3_value, -999)
AND NVL(PK4_VALUE, -999) = NVL(p_pk4_value, -999)
AND NVL(PK5_VALUE, -999) = NVL(p_pk5_value, -999)
AND Nvl(transaction_id, -999) = Nvl(p_transaction_id, -999) --PAR Project
AND ATTR_GROUP_TYPE = p_attr_grp_type
ORDER BY ATTR_GROUP_ID;
SELECT INTERFACE_REFERENCE_ID
INTO g_interface_reference_id
FROM PO_UDA_INTERFACE
WHERE NVL(PK1_VALUE, -999) = NVL(p_pk1_value, -999)
AND NVL(PK2_VALUE, -999) = NVL(p_pk2_value, -999)
AND NVL(PK3_VALUE, -999) = NVL(p_pk3_value, -999)
AND NVL(PK4_VALUE, -999) = NVL(p_pk4_value, -999)
AND NVL(PK5_VALUE, -999) = NVL(p_pk5_value, -999)
AND ROWNUM < 2;
,l_program_update_date
,l_last_update_date
,l_creation_date
,l_last_update_login
,l_created_by
,l_last_updated_by
,l_created_by_name
,l_last_updated_by_name
,l_interface_source_code
,l_interface_source_line_id;
PO_UDA_DEFAULTING_PKG.DEBUG('l_program_update_date : ' || l_program_update_date, d_progress);
PO_UDA_DEFAULTING_PKG.DEBUG('l_last_update_date : ' || l_last_update_date, d_progress);
PO_UDA_DEFAULTING_PKG.DEBUG('l_last_updated_by : ' || l_last_updated_by, d_progress);
PO_UDA_DEFAULTING_PKG.DEBUG('l_last_updated_by_name : ' || l_last_updated_by_name, d_progress);
SELECT END_USER_COLUMN_NAME
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_attr_grp_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_name;
SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
INTO l_user_row_identifier
FROM DUAL;
SELECT END_USER_COLUMN_NAME, APPLICATION_COLUMN_NAME
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_attr_grp_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_name;
SELECT DISTINCT LOOKUP_CODE , TAG
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = p_address_lookup_type
AND (p_use_mod_addr_type = 'N'
OR (p_use_mod_addr_type = 'Y' AND
LOOKUP_CODE IN ('MOD_ADMIN_OFFICE', 'MOD_ISSUING_OFFICE')))
ORDER BY TAG;
SELECT DESCRIPTIVE_FLEXFIELD_NAME, DESCRIPTIVE_FLEX_CONTEXT_CODE
INTO l_attr_group_type, l_attr_group_name
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE ATTR_GROUP_ID = l_attr_group_id;
l_sql_query := 'SELECT 1 FROM ' || l_base_table;
SELECT DATA_LEVEL_NAME
INTO l_data_level
FROM EGO_DATA_LEVEL_B
WHERE APPLICATION_ID = 201
AND ATTR_GROUP_TYPE = l_attr_group_type
AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE'
AND ATTR_GROUP_TYPE NOT LIKE '%DUMMY%';
SELECT Count(*)
INTO l_xd_present
FROM EGO_ACTION_DISPLAYS_B, EGO_ACTIONS_B
WHERE classification_code = p_template_id
AND attr_group_id = l_attr_group_id
AND execution_method = 'XD'
AND EGO_ACTION_DISPLAYS_B.action_id = EGO_ACTIONS_B.action_id;
SELECT PO_UDA_INTERFACE_ROW_ID_S.NEXTVAL
INTO l_row_identifier
FROM DUAL;
SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
INTO l_user_row_identifier
FROM DUAL;
SELECT PO_UDA_INTERFACE_ROW_ID_S.NEXTVAL
INTO l_row_identifier
FROM DUAL;
SELECT PO_UDA_INTERFACE_USER_ROW_ID_S.NEXTVAL
INTO l_user_row_identifier
FROM DUAL;
SELECT EGO_ACTION_DISPLAYS_B.action_id FROM EGO_ACTION_DISPLAYS_B, EGO_ACTIONS_B
WHERE classification_code = p_template_id
AND attr_group_id = p_attribute_group_id
AND execution_method = p_event
AND EGO_ACTION_DISPLAYS_B.action_id = EGO_ACTIONS_B.action_id
ORDER BY SEQUENCE;
SELECT DATA_LEVEL_NAME, DATA_LEVEL_ID
INTO l_data_level_name, l_data_level_id
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE = l_attr_row_obj.ATTR_GROUP_TYPE
AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE';
x_attr_name_value_pairs.DELETE(l_attr_id_index);
x_attributes_row_table.DELETE(l_attr_group_id_index);
PO_UDA_DEFAULTING_PKG.DEBUG('Before PO_UDA_IMPORT_PKG.UPDATE_STATUS to change to IN_PROGRESS from PENDING', d_progress);
PO_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
,p_pre_process_status => PO_UDA_IMPORT_PKG.PENDING
,p_process_phase => PO_UDA_IMPORT_PKG.PRE_DEFAULTING
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PO_UDA_DEFAULTING_PKG.DEBUG('After PO_UDA_IMPORT_PKG.UPDATE_STATUS to change to PENDING from IN_PROGRESS', d_progress);
PO_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PO_UDA_IMPORT_PKG.UDA_ERROR
,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PO_UDA_IMPORT_PKG.PRE_DEFAULTING
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PO_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PO_UDA_IMPORT_PKG.UDA_ERROR
,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PO_UDA_IMPORT_PKG.PRE_DEFAULTING
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PO_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PO_UDA_IMPORT_PKG.DEFAULTING
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PO_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PO_UDA_IMPORT_PKG.UDA_ERROR
,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PO_UDA_IMPORT_PKG.PRE_DEFAULTING
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PO_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PO_UDA_IMPORT_PKG.UDA_ERROR
,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PO_UDA_IMPORT_PKG.DEFAULTING
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PO_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PO_UDA_IMPORT_PKG.EGO_VALIDATE
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PO_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PO_UDA_IMPORT_PKG.UDA_ERROR
,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PO_UDA_IMPORT_PKG.DEFAULTING
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PO_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PO_UDA_IMPORT_PKG.CUSTOM_VALIDATE
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PO_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PO_UDA_IMPORT_PKG.UDA_ERROR
,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PO_UDA_IMPORT_PKG.EGO_VALIDATE
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PO_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PO_UDA_IMPORT_PKG.LOAD
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PO_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PO_UDA_IMPORT_PKG.UDA_ERROR
,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PO_UDA_IMPORT_PKG.CUSTOM_VALIDATE
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PO_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PO_UDA_IMPORT_PKG.UDA_ERROR
,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PO_UDA_IMPORT_PKG.LOAD
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PO_UDA_IMPORT_PKG.UPDATE_STATUS
(
p_process_status => PO_UDA_IMPORT_PKG.COMPLETED
,p_pre_process_status => PO_UDA_IMPORT_PKG.IN_PROGRESS
,p_process_phase => PO_UDA_IMPORT_PKG.LOAD
,p_pk1_value => p_pk1_value
,p_pk2_value => p_pk2_value
,p_pk3_value => p_pk3_value
,p_pk4_value => p_pk4_value
,p_pk5_value => p_pk5_value
);
PO_INTERFACE_ERRORS_SV1.insert_po_interface_errors_msg
(
X_interface_type => g_attr_grp_type,
X_Interface_Header_ID => g_interface_reference_id,
X_Interface_Line_Id => NULL,
X_Interface_Dist_Id => NULL,
X_error_message_text => p_msg,
X_error_message_name => p_msg_code,
X_column_name => NULL,
X_table_name => NULL,
X_batch_id => p_attr_grp_id
);
PROCEDURE UPDATE_STATUS
(
p_process_status IN NUMBER
,p_pre_process_status IN NUMBER
,p_process_phase IN VARCHAR2
,p_pk1_value IN NUMBER
,p_pk2_value IN NUMBER
,p_pk3_value IN NUMBER
,p_pk4_value IN NUMBER
,p_pk5_value IN NUMBER
)
IS
BEGIN
IF p_process_status IN (PO_UDA_IMPORT_PKG.PENDING, PO_UDA_IMPORT_PKG.IN_PROGRESS, PO_UDA_IMPORT_PKG.COMPLETED) THEN
UPDATE PO_UDA_INTERFACE
SET PROCESS_STATUS = p_process_status
,PROCESS_PHASE = p_process_phase
WHERE NVL(PK1_VALUE, -999) = NVL(p_pk1_value, -999)
AND NVL(PK2_VALUE, -999) = NVL(p_pk2_value, -999)
AND NVL(PK3_VALUE, -999) = NVL(p_pk3_value, -999)
AND NVL(PK4_VALUE, -999) = NVL(p_pk4_value, -999)
AND NVL(PK5_VALUE, -999) = NVL(p_pk5_value, -999)
AND PROCESS_STATUS = p_pre_process_status;
SELECT pk1_value, pk2_value, attr_group_type
FROM po_uda_interface pui
WHERE transaction_id = p_transaction_id
AND attr_group_type IN ('PO_HEADER_EXT_ATTRS', 'PO_LINE_EXT_ATTRS','PO_SHIPMENTS_EXT_ATTRS' ) ;
SELECT org_id
INTO l_org_id
FROM po_headers_draft_all
WHERE po_header_id = p_header_id
AND draft_id = p_draft_id;
SELECT DISTINCT attr_group_id, pk1_value , pk2_value , uda_template_id
BULK collect INTO l_attr_group_id_tbl , l_po_lines_tbl, l_draft_id_tbl, l_template_id_tbl
FROM po_uda_interface
WHERE ATTR_GROUP_TYPE = 'PO_LINE_EXT_ATTRS'
AND transaction_id = p_transaction_id;
SELECT attribute_category
into l_attribute_category
from po_uda_ag_template_usages
WHERE attribute_group_id = l_attr_group_id_tbl(i) and template_id = l_template_id_tbl(i) ;
SELECT matching_basis,quantity
INTO l_matching_basis,l_quant
FROM po_lines_draft_all plda
WHERE plda.draft_id = p_draft_id
AND plda.po_line_id = l_po_lines_tbl(i);
UPDATE po_lines_draft_all plda
SET (plda.CLM_UDA_PRICING_TOTAL , plda.amount) =
(SELECT plaeb.N_EXT_ATTR20,(plaeb.N_EXT_ATTR20 + Nvl(plda.CLM_APPROVED_UNDEF_AMOUNT,0))-- + plu.undef_amount)
FROM po_lines_all_ext_b plaeb
WHERE plaeb.draft_id = Decode(l_draft_id_tbl(i),p_draft_id,p_draft_id,-1)
AND plaeb.attr_group_id = l_attr_group_id_tbl(i)
AND plaeb.po_line_id = l_po_lines_tbl(i) )
WHERE plda.draft_id = p_draft_id
AND plda.po_line_id = l_po_lines_tbl(i);
UPDATE po_line_locations_draft_all pllda
SET pllda.amount =
( SELECT amount
FROM po_lines_draft_all plda
WHERE plda.draft_id = p_draft_id
AND plda.po_line_id = l_po_lines_tbl(i)
)
WHERE pllda.draft_id = p_draft_id
AND pllda.po_line_id = l_po_lines_tbl(i)
AND EXISTS (SELECT 'there should be only one shipment with quantity equals to 1'
FROM po_line_locations_draft_all pllda
WHERE pllda.draft_id = p_draft_id
AND pllda.po_line_id = l_po_lines_tbl(i)
AND pllda.amount = 1
HAVING Count(*) = 1);
UPDATE po_distributions_draft_all pdda
SET pdda.amount_ordered =
( SELECT amount
FROM po_lines_draft_all plda
WHERE plda.draft_id = p_draft_id
AND plda.po_line_id = l_po_lines_tbl(i)
)
WHERE pdda.draft_id = p_draft_id
AND pdda.po_line_id = l_po_lines_tbl(i)
AND EXISTS (SELECT 'there should be only one distribution with quantity equals to 1'
FROM po_distributions_draft_all pdda
WHERE pdda.draft_id = p_draft_id
AND pdda.po_line_id = l_po_lines_tbl(i)
AND pdda.amount_ordered = 1
HAVING Count(*) = 1);
UPDATE po_lines_draft_all plda
SET (plda.CLM_UDA_PRICING_TOTAL,plda.quantity) =
(SELECT plaeb.N_EXT_ATTR20,(plaeb.N_EXT_ATTR20 + Nvl(plda.CLM_APPROVED_UNDEF_AMOUNT,0))-- + plu.undef_amount)
FROM po_lines_all_ext_b plaeb
WHERE plaeb.draft_id = Decode(l_draft_id_tbl(i),p_draft_id,p_draft_id,-1)
AND plaeb.attr_group_id = l_attr_group_id_tbl(i)
AND plaeb.po_line_id = l_po_lines_tbl(i) )
WHERE plda.draft_id = p_draft_id
AND plda.po_line_id = l_po_lines_tbl(i);
UPDATE po_line_locations_draft_all pllda
SET pllda.quantity =
( SELECT quantity
FROM po_lines_draft_all plda
WHERE plda.draft_id = p_draft_id
AND plda.po_line_id = l_po_lines_tbl(i)
)
WHERE pllda.draft_id = p_draft_id
AND pllda.po_line_id = l_po_lines_tbl(i)
AND EXISTS (SELECT 'there should be only one shipment with quantity equlas to 1'
FROM po_line_locations_draft_all pllda
WHERE pllda.draft_id = p_draft_id
AND pllda.po_line_id = l_po_lines_tbl(i)
AND pllda.quantity = 1
HAVING Count(*) = 1);
UPDATE po_distributions_draft_all pdda
SET pdda.quantity_ordered =
( SELECT quantity
FROM po_lines_draft_all plda
WHERE plda.draft_id = p_draft_id
AND plda.po_line_id = l_po_lines_tbl(i)
)
WHERE pdda.draft_id = p_draft_id
AND pdda.po_line_id = l_po_lines_tbl(i)
AND EXISTS (SELECT 'there should be only one distribution with quantity equlas to 1'
FROM po_distributions_draft_all pdda
WHERE pdda.draft_id = p_draft_id
AND pdda.po_line_id = l_po_lines_tbl(i)
AND pdda.quantity_ordered = 1
HAVING Count(*) = 1);
SELECT 'Y'
INTO l_records_exist
FROM dual
WHERE EXISTS ( SELECT 1
FROM
po_lines_all_ext_b
WHERE draft_id = l_draft_id_tbl(i)
AND attr_group_id = l_attr_group_id_tbl(i)
AND po_line_id = l_po_lines_tbl(i)
AND data_level_id = (SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE_CP_DELTA_ARCHIVE'));
SELECT language_code
BULK COLLECT INTO l_language_code_tbl
FROM fnd_languages
WHERE INSTALLED_FLAG in ('I', 'B');
UPDATE po_lines_all_ext_b
SET (EXTENSION_ID
,ATTR_GROUP_ID
,PO_LINE_ID
,DATA_LEVEL_ID
,PK1_VALUE
,PK2_VALUE
,PK3_VALUE
,PK4_VALUE
,PK5_VALUE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,C_EXT_ATTR1
,C_EXT_ATTR2
,C_EXT_ATTR3
,C_EXT_ATTR4
,C_EXT_ATTR5
,C_EXT_ATTR6
,C_EXT_ATTR7
,C_EXT_ATTR8
,C_EXT_ATTR9
,C_EXT_ATTR10
,C_EXT_ATTR11
,C_EXT_ATTR12
,C_EXT_ATTR13
,C_EXT_ATTR14
,C_EXT_ATTR15
,C_EXT_ATTR16
,C_EXT_ATTR17
,C_EXT_ATTR18
,C_EXT_ATTR19
,C_EXT_ATTR20
,C_EXT_ATTR21
,C_EXT_ATTR22
,C_EXT_ATTR23
,C_EXT_ATTR24
,C_EXT_ATTR25
,C_EXT_ATTR26
,C_EXT_ATTR27
,C_EXT_ATTR28
,C_EXT_ATTR29
,C_EXT_ATTR30
,C_EXT_ATTR31
,C_EXT_ATTR32
,C_EXT_ATTR33
,C_EXT_ATTR34
,C_EXT_ATTR35
,C_EXT_ATTR36
,C_EXT_ATTR37
,C_EXT_ATTR38
,C_EXT_ATTR39
,C_EXT_ATTR40
,N_EXT_ATTR1
,N_EXT_ATTR2
,N_EXT_ATTR3
,N_EXT_ATTR4
,N_EXT_ATTR5
,N_EXT_ATTR6
,N_EXT_ATTR7
,N_EXT_ATTR8
,N_EXT_ATTR9
,N_EXT_ATTR10
,N_EXT_ATTR11
,N_EXT_ATTR12
,N_EXT_ATTR13
,N_EXT_ATTR14
,N_EXT_ATTR15
,N_EXT_ATTR16
,N_EXT_ATTR17
,N_EXT_ATTR18
,N_EXT_ATTR19
,N_EXT_ATTR20
,UOM_EXT_ATTR1
,UOM_EXT_ATTR2
,UOM_EXT_ATTR3
,UOM_EXT_ATTR4
,UOM_EXT_ATTR5
,UOM_EXT_ATTR6
,UOM_EXT_ATTR7
,UOM_EXT_ATTR8
,UOM_EXT_ATTR9
,UOM_EXT_ATTR10
,UOM_EXT_ATTR11
,UOM_EXT_ATTR12
,UOM_EXT_ATTR13
,UOM_EXT_ATTR14
,UOM_EXT_ATTR15
,UOM_EXT_ATTR16
,UOM_EXT_ATTR17
,UOM_EXT_ATTR18
,UOM_EXT_ATTR19
,UOM_EXT_ATTR20
,D_EXT_ATTR1
,D_EXT_ATTR2
,D_EXT_ATTR3
,D_EXT_ATTR4
,D_EXT_ATTR5
,D_EXT_ATTR6
,D_EXT_ATTR7
,D_EXT_ATTR8
,D_EXT_ATTR9
,D_EXT_ATTR10
,UDA_TEMPLATE_ID
,DRAFT_ID
) =
(SELECT
pl_new.extension_id
,pl_old.ATTR_GROUP_ID
,(pl_old.PO_LINE_ID * -1 )
,(SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE_CP_DELTA_ARCHIVE')
,l_draft_id_tbl(i) * -1
,pl_new.PK2_VALUE
,pl_new.PK3_VALUE
,pl_new.PK4_VALUE
,pl_new.PK5_VALUE
,pl_new.LAST_UPDATE_DATE
,pl_new.LAST_UPDATED_BY
,pl_new.LAST_UPDATE_LOGIN
,pl_new.CREATED_BY
,pl_new.CREATION_DATE
,pl_new.C_EXT_ATTR1
,pl_new.C_EXT_ATTR2
,pl_new.C_EXT_ATTR3
,pl_new.C_EXT_ATTR4
,pl_new.C_EXT_ATTR5
,pl_new.C_EXT_ATTR6
,pl_new.C_EXT_ATTR7
,pl_new.C_EXT_ATTR8
,pl_new.C_EXT_ATTR9
,pl_new.C_EXT_ATTR10
,pl_new.C_EXT_ATTR11
,pl_new.C_EXT_ATTR12
,pl_new.C_EXT_ATTR13
,pl_new.C_EXT_ATTR14
,pl_new.C_EXT_ATTR15
,pl_new.C_EXT_ATTR16
,pl_new.C_EXT_ATTR17
,pl_new.C_EXT_ATTR18
,pl_new.C_EXT_ATTR19
,pl_new.C_EXT_ATTR20
,pl_new.C_EXT_ATTR21
,pl_new.C_EXT_ATTR22
,pl_new.C_EXT_ATTR23
,pl_new.C_EXT_ATTR24
,pl_new.C_EXT_ATTR25
,pl_new.C_EXT_ATTR26
,pl_new.C_EXT_ATTR27
,pl_new.C_EXT_ATTR28
,pl_new.C_EXT_ATTR29
,pl_new.C_EXT_ATTR30
,pl_new.C_EXT_ATTR31
,pl_new.C_EXT_ATTR32
,pl_new.C_EXT_ATTR33
,pl_new.C_EXT_ATTR34
,pl_new.C_EXT_ATTR35
,pl_new.C_EXT_ATTR36
,pl_new.C_EXT_ATTR37
,pl_new.C_EXT_ATTR38
,pl_new.C_EXT_ATTR39
,pl_new.C_EXT_ATTR40
,pl_new.N_EXT_ATTR1 - pl_old.N_EXT_ATTR1
,pl_new.N_EXT_ATTR2 - pl_old.N_EXT_ATTR2
,pl_new.N_EXT_ATTR3 - pl_old.N_EXT_ATTR3
,pl_new.N_EXT_ATTR4 - pl_old.N_EXT_ATTR4
,pl_new.N_EXT_ATTR5 - pl_old.N_EXT_ATTR5
,pl_new.N_EXT_ATTR6 - pl_old.N_EXT_ATTR6
,pl_new.N_EXT_ATTR7 - pl_old.N_EXT_ATTR7
,pl_new.N_EXT_ATTR8 - pl_old.N_EXT_ATTR8
,pl_new.N_EXT_ATTR9 - pl_old.N_EXT_ATTR9
,pl_new.N_EXT_ATTR10 - pl_old.N_EXT_ATTR10
,pl_new.N_EXT_ATTR11 - pl_old.N_EXT_ATTR11
,pl_new.N_EXT_ATTR12 - pl_old.N_EXT_ATTR12
,pl_new.N_EXT_ATTR13 - pl_old.N_EXT_ATTR13
,pl_new.N_EXT_ATTR14 - pl_old.N_EXT_ATTR14
,pl_new.N_EXT_ATTR15 - pl_old.N_EXT_ATTR15
,pl_new.N_EXT_ATTR16 - pl_old.N_EXT_ATTR16
,pl_new.N_EXT_ATTR17 - pl_old.N_EXT_ATTR17
,pl_new.N_EXT_ATTR18 - pl_old.N_EXT_ATTR18
,pl_new.N_EXT_ATTR19 - pl_old.N_EXT_ATTR19
,pl_new.N_EXT_ATTR20 - pl_old.N_EXT_ATTR20
,pl_new.UOM_EXT_ATTR1
,pl_new.UOM_EXT_ATTR2
,pl_new.UOM_EXT_ATTR3
,pl_new.UOM_EXT_ATTR4
,pl_new.UOM_EXT_ATTR5
,pl_new.UOM_EXT_ATTR6
,pl_new.UOM_EXT_ATTR7
,pl_new.UOM_EXT_ATTR8
,pl_new.UOM_EXT_ATTR9
,pl_new.UOM_EXT_ATTR10
,pl_new.UOM_EXT_ATTR11
,pl_new.UOM_EXT_ATTR12
,pl_new.UOM_EXT_ATTR13
,pl_new.UOM_EXT_ATTR14
,pl_new.UOM_EXT_ATTR15
,pl_new.UOM_EXT_ATTR16
,pl_new.UOM_EXT_ATTR17
,pl_new.UOM_EXT_ATTR18
,pl_new.UOM_EXT_ATTR19
,pl_new.UOM_EXT_ATTR20
,pl_new.D_EXT_ATTR1
,pl_new.D_EXT_ATTR2
,pl_new.D_EXT_ATTR3
,pl_new.D_EXT_ATTR4
,pl_new.D_EXT_ATTR5
,pl_new.D_EXT_ATTR6
,pl_new.D_EXT_ATTR7
,pl_new.D_EXT_ATTR8
,pl_new.D_EXT_ATTR9
,pl_new.D_EXT_ATTR10
,pl_old.UDA_TEMPLATE_ID
,l_draft_id_tbl(i) * -1
FROM PO_LINES_ALL_EXT_B pl_old,PO_LINES_ALL_EXT_B pl_new
WHERE pl_old.po_line_id = pl_new.po_line_id
AND pl_old.ATTR_GROUP_ID = pl_new.ATTR_GROUP_ID
AND pl_old.data_level_id = pl_new.data_level_id
AND pl_old.ATTR_GROUP_ID = l_attr_group_id_tbl(i)
AND pl_old.po_line_id = l_po_lines_tbl(i)
AND pl_old.data_level_id = (SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE')
AND pl_new.draft_id = l_draft_id_tbl(i)
AND pl_old.draft_id = -1);
INSERT INTO
po_lines_all_ext_b
(EXTENSION_ID
,ATTR_GROUP_ID
,PO_LINE_ID
,DATA_LEVEL_ID
,PK1_VALUE
,PK2_VALUE
,PK3_VALUE
,PK4_VALUE
,PK5_VALUE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,C_EXT_ATTR1
,C_EXT_ATTR2
,C_EXT_ATTR3
,C_EXT_ATTR4
,C_EXT_ATTR5
,C_EXT_ATTR6
,C_EXT_ATTR7
,C_EXT_ATTR8
,C_EXT_ATTR9
,C_EXT_ATTR10
,C_EXT_ATTR11
,C_EXT_ATTR12
,C_EXT_ATTR13
,C_EXT_ATTR14
,C_EXT_ATTR15
,C_EXT_ATTR16
,C_EXT_ATTR17
,C_EXT_ATTR18
,C_EXT_ATTR19
,C_EXT_ATTR20
,C_EXT_ATTR21
,C_EXT_ATTR22
,C_EXT_ATTR23
,C_EXT_ATTR24
,C_EXT_ATTR25
,C_EXT_ATTR26
,C_EXT_ATTR27
,C_EXT_ATTR28
,C_EXT_ATTR29
,C_EXT_ATTR30
,C_EXT_ATTR31
,C_EXT_ATTR32
,C_EXT_ATTR33
,C_EXT_ATTR34
,C_EXT_ATTR35
,C_EXT_ATTR36
,C_EXT_ATTR37
,C_EXT_ATTR38
,C_EXT_ATTR39
,C_EXT_ATTR40
,N_EXT_ATTR1
,N_EXT_ATTR2
,N_EXT_ATTR3
,N_EXT_ATTR4
,N_EXT_ATTR5
,N_EXT_ATTR6
,N_EXT_ATTR7
,N_EXT_ATTR8
,N_EXT_ATTR9
,N_EXT_ATTR10
,N_EXT_ATTR11
,N_EXT_ATTR12
,N_EXT_ATTR13
,N_EXT_ATTR14
,N_EXT_ATTR15
,N_EXT_ATTR16
,N_EXT_ATTR17
,N_EXT_ATTR18
,N_EXT_ATTR19
,N_EXT_ATTR20
,UOM_EXT_ATTR1
,UOM_EXT_ATTR2
,UOM_EXT_ATTR3
,UOM_EXT_ATTR4
,UOM_EXT_ATTR5
,UOM_EXT_ATTR6
,UOM_EXT_ATTR7
,UOM_EXT_ATTR8
,UOM_EXT_ATTR9
,UOM_EXT_ATTR10
,UOM_EXT_ATTR11
,UOM_EXT_ATTR12
,UOM_EXT_ATTR13
,UOM_EXT_ATTR14
,UOM_EXT_ATTR15
,UOM_EXT_ATTR16
,UOM_EXT_ATTR17
,UOM_EXT_ATTR18
,UOM_EXT_ATTR19
,UOM_EXT_ATTR20
,D_EXT_ATTR1
,D_EXT_ATTR2
,D_EXT_ATTR3
,D_EXT_ATTR4
,D_EXT_ATTR5
,D_EXT_ATTR6
,D_EXT_ATTR7
,D_EXT_ATTR8
,D_EXT_ATTR9
,D_EXT_ATTR10
,UDA_TEMPLATE_ID
,DRAFT_ID
)
SELECT
EGO_EXTFWK_S.NEXTVAL
,pl_old.ATTR_GROUP_ID
,(pl_old.PO_LINE_ID * -1 )
,(SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE_CP_DELTA_ARCHIVE')
,l_draft_id_tbl(i) * -1
,pl_new.PK2_VALUE
,pl_new.PK3_VALUE
,pl_new.PK4_VALUE
,pl_new.PK5_VALUE
,pl_new.LAST_UPDATE_DATE
,pl_new.LAST_UPDATED_BY
,pl_new.LAST_UPDATE_LOGIN
,pl_new.CREATED_BY
,pl_new.CREATION_DATE
,pl_new.C_EXT_ATTR1
,pl_new.C_EXT_ATTR2
,pl_new.C_EXT_ATTR3
,pl_new.C_EXT_ATTR4
,pl_new.C_EXT_ATTR5
,pl_new.C_EXT_ATTR6
,pl_new.C_EXT_ATTR7
,pl_new.C_EXT_ATTR8
,pl_new.C_EXT_ATTR9
,pl_new.C_EXT_ATTR10
,pl_new.C_EXT_ATTR11
,pl_new.C_EXT_ATTR12
,pl_new.C_EXT_ATTR13
,pl_new.C_EXT_ATTR14
,pl_new.C_EXT_ATTR15
,pl_new.C_EXT_ATTR16
,pl_new.C_EXT_ATTR17
,pl_new.C_EXT_ATTR18
,pl_new.C_EXT_ATTR19
,pl_new.C_EXT_ATTR20
,pl_new.C_EXT_ATTR21
,pl_new.C_EXT_ATTR22
,pl_new.C_EXT_ATTR23
,pl_new.C_EXT_ATTR24
,pl_new.C_EXT_ATTR25
,pl_new.C_EXT_ATTR26
,pl_new.C_EXT_ATTR27
,pl_new.C_EXT_ATTR28
,pl_new.C_EXT_ATTR29
,pl_new.C_EXT_ATTR30
,pl_new.C_EXT_ATTR31
,pl_new.C_EXT_ATTR32
,pl_new.C_EXT_ATTR33
,pl_new.C_EXT_ATTR34
,pl_new.C_EXT_ATTR35
,pl_new.C_EXT_ATTR36
,pl_new.C_EXT_ATTR37
,pl_new.C_EXT_ATTR38
,pl_new.C_EXT_ATTR39
,pl_new.C_EXT_ATTR40
,pl_new.N_EXT_ATTR1 - pl_old.N_EXT_ATTR1
,pl_new.N_EXT_ATTR2 - pl_old.N_EXT_ATTR2
,pl_new.N_EXT_ATTR3 - pl_old.N_EXT_ATTR3
,pl_new.N_EXT_ATTR4 - pl_old.N_EXT_ATTR4
,pl_new.N_EXT_ATTR5 - pl_old.N_EXT_ATTR5
,pl_new.N_EXT_ATTR6 - pl_old.N_EXT_ATTR6
,pl_new.N_EXT_ATTR7 - pl_old.N_EXT_ATTR7
,pl_new.N_EXT_ATTR8 - pl_old.N_EXT_ATTR8
,pl_new.N_EXT_ATTR9 - pl_old.N_EXT_ATTR9
,pl_new.N_EXT_ATTR10 - pl_old.N_EXT_ATTR10
,pl_new.N_EXT_ATTR11 - pl_old.N_EXT_ATTR11
,pl_new.N_EXT_ATTR12 - pl_old.N_EXT_ATTR12
,pl_new.N_EXT_ATTR13 - pl_old.N_EXT_ATTR13
,pl_new.N_EXT_ATTR14 - pl_old.N_EXT_ATTR14
,pl_new.N_EXT_ATTR15 - pl_old.N_EXT_ATTR15
,pl_new.N_EXT_ATTR16 - pl_old.N_EXT_ATTR16
,pl_new.N_EXT_ATTR17 - pl_old.N_EXT_ATTR17
,pl_new.N_EXT_ATTR18 - pl_old.N_EXT_ATTR18
,pl_new.N_EXT_ATTR19 - pl_old.N_EXT_ATTR19
,pl_new.N_EXT_ATTR20 - pl_old.N_EXT_ATTR20
,pl_new.UOM_EXT_ATTR1
,pl_new.UOM_EXT_ATTR2
,pl_new.UOM_EXT_ATTR3
,pl_new.UOM_EXT_ATTR4
,pl_new.UOM_EXT_ATTR5
,pl_new.UOM_EXT_ATTR6
,pl_new.UOM_EXT_ATTR7
,pl_new.UOM_EXT_ATTR8
,pl_new.UOM_EXT_ATTR9
,pl_new.UOM_EXT_ATTR10
,pl_new.UOM_EXT_ATTR11
,pl_new.UOM_EXT_ATTR12
,pl_new.UOM_EXT_ATTR13
,pl_new.UOM_EXT_ATTR14
,pl_new.UOM_EXT_ATTR15
,pl_new.UOM_EXT_ATTR16
,pl_new.UOM_EXT_ATTR17
,pl_new.UOM_EXT_ATTR18
,pl_new.UOM_EXT_ATTR19
,pl_new.UOM_EXT_ATTR20
,pl_new.D_EXT_ATTR1
,pl_new.D_EXT_ATTR2
,pl_new.D_EXT_ATTR3
,pl_new.D_EXT_ATTR4
,pl_new.D_EXT_ATTR5
,pl_new.D_EXT_ATTR6
,pl_new.D_EXT_ATTR7
,pl_new.D_EXT_ATTR8
,pl_new.D_EXT_ATTR9
,pl_new.D_EXT_ATTR10
,pl_old.UDA_TEMPLATE_ID
,l_draft_id_tbl(i) * -1
FROM PO_LINES_ALL_EXT_B pl_old,PO_LINES_ALL_EXT_B pl_new
WHERE pl_old.po_line_id = pl_new.po_line_id
AND pl_old.ATTR_GROUP_ID = pl_new.ATTR_GROUP_ID
AND pl_old.data_level_id = pl_new.data_level_id
AND pl_old.ATTR_GROUP_ID = l_attr_group_id_tbl(i)
AND pl_old.po_line_id = l_po_lines_tbl(i)
AND pl_old.data_level_id = (SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE')
AND pl_new.draft_id = l_draft_id_tbl(i)
AND pl_old.draft_id = -1;
INSERT INTO
po_lines_all_ext_b
(EXTENSION_ID
,ATTR_GROUP_ID
,PO_LINE_ID
,DATA_LEVEL_ID
,PK1_VALUE
,PK2_VALUE
,PK3_VALUE
,PK4_VALUE
,PK5_VALUE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATED_BY
,CREATION_DATE
,C_EXT_ATTR1
,C_EXT_ATTR2
,C_EXT_ATTR3
,C_EXT_ATTR4
,C_EXT_ATTR5
,C_EXT_ATTR6
,C_EXT_ATTR7
,C_EXT_ATTR8
,C_EXT_ATTR9
,C_EXT_ATTR10
,C_EXT_ATTR11
,C_EXT_ATTR12
,C_EXT_ATTR13
,C_EXT_ATTR14
,C_EXT_ATTR15
,C_EXT_ATTR16
,C_EXT_ATTR17
,C_EXT_ATTR18
,C_EXT_ATTR19
,C_EXT_ATTR20
,C_EXT_ATTR21
,C_EXT_ATTR22
,C_EXT_ATTR23
,C_EXT_ATTR24
,C_EXT_ATTR25
,C_EXT_ATTR26
,C_EXT_ATTR27
,C_EXT_ATTR28
,C_EXT_ATTR29
,C_EXT_ATTR30
,C_EXT_ATTR31
,C_EXT_ATTR32
,C_EXT_ATTR33
,C_EXT_ATTR34
,C_EXT_ATTR35
,C_EXT_ATTR36
,C_EXT_ATTR37
,C_EXT_ATTR38
,C_EXT_ATTR39
,C_EXT_ATTR40
,N_EXT_ATTR1
,N_EXT_ATTR2
,N_EXT_ATTR3
,N_EXT_ATTR4
,N_EXT_ATTR5
,N_EXT_ATTR6
,N_EXT_ATTR7
,N_EXT_ATTR8
,N_EXT_ATTR9
,N_EXT_ATTR10
,N_EXT_ATTR11
,N_EXT_ATTR12
,N_EXT_ATTR13
,N_EXT_ATTR14
,N_EXT_ATTR15
,N_EXT_ATTR16
,N_EXT_ATTR17
,N_EXT_ATTR18
,N_EXT_ATTR19
,N_EXT_ATTR20
,UOM_EXT_ATTR1
,UOM_EXT_ATTR2
,UOM_EXT_ATTR3
,UOM_EXT_ATTR4
,UOM_EXT_ATTR5
,UOM_EXT_ATTR6
,UOM_EXT_ATTR7
,UOM_EXT_ATTR8
,UOM_EXT_ATTR9
,UOM_EXT_ATTR10
,UOM_EXT_ATTR11
,UOM_EXT_ATTR12
,UOM_EXT_ATTR13
,UOM_EXT_ATTR14
,UOM_EXT_ATTR15
,UOM_EXT_ATTR16
,UOM_EXT_ATTR17
,UOM_EXT_ATTR18
,UOM_EXT_ATTR19
,UOM_EXT_ATTR20
,D_EXT_ATTR1
,D_EXT_ATTR2
,D_EXT_ATTR3
,D_EXT_ATTR4
,D_EXT_ATTR5
,D_EXT_ATTR6
,D_EXT_ATTR7
,D_EXT_ATTR8
,D_EXT_ATTR9
,D_EXT_ATTR10
,UDA_TEMPLATE_ID
,DRAFT_ID
)
SELECT
EGO_EXTFWK_S.NEXTVAL
,pl_old.ATTR_GROUP_ID
,pl_old.PO_LINE_ID
,(SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE_CP_OLD_ARCHIVE')
,l_draft_id_tbl(i)
,pl_old.PK2_VALUE
,pl_old.PK3_VALUE
,pl_old.PK4_VALUE
,pl_old.PK5_VALUE
,pl_old.LAST_UPDATE_DATE
,pl_old.LAST_UPDATED_BY
,pl_old.LAST_UPDATE_LOGIN
,pl_old.CREATED_BY
,pl_old.CREATION_DATE
,pl_old.C_EXT_ATTR1
,pl_old.C_EXT_ATTR2
,pl_old.C_EXT_ATTR3
,pl_old.C_EXT_ATTR4
,pl_old.C_EXT_ATTR5
,pl_old.C_EXT_ATTR6
,pl_old.C_EXT_ATTR7
,pl_old.C_EXT_ATTR8
,pl_old.C_EXT_ATTR9
,pl_old.C_EXT_ATTR10
,pl_old.C_EXT_ATTR11
,pl_old.C_EXT_ATTR12
,pl_old.C_EXT_ATTR13
,pl_old.C_EXT_ATTR14
,pl_old.C_EXT_ATTR15
,pl_old.C_EXT_ATTR16
,pl_old.C_EXT_ATTR17
,pl_old.C_EXT_ATTR18
,pl_old.C_EXT_ATTR19
,pl_old.C_EXT_ATTR20
,pl_old.C_EXT_ATTR21
,pl_old.C_EXT_ATTR22
,pl_old.C_EXT_ATTR23
,pl_old.C_EXT_ATTR24
,pl_old.C_EXT_ATTR25
,pl_old.C_EXT_ATTR26
,pl_old.C_EXT_ATTR27
,pl_old.C_EXT_ATTR28
,pl_old.C_EXT_ATTR29
,pl_old.C_EXT_ATTR30
,pl_old.C_EXT_ATTR31
,pl_old.C_EXT_ATTR32
,pl_old.C_EXT_ATTR33
,pl_old.C_EXT_ATTR34
,pl_old.C_EXT_ATTR35
,pl_old.C_EXT_ATTR36
,pl_old.C_EXT_ATTR37
,pl_old.C_EXT_ATTR38
,pl_old.C_EXT_ATTR39
,pl_old.C_EXT_ATTR40
,pl_old.N_EXT_ATTR1
,pl_old.N_EXT_ATTR2
,pl_old.N_EXT_ATTR3
,pl_old.N_EXT_ATTR4
,pl_old.N_EXT_ATTR5
,pl_old.N_EXT_ATTR6
,pl_old.N_EXT_ATTR7
,pl_old.N_EXT_ATTR8
,pl_old.N_EXT_ATTR9
,pl_old.N_EXT_ATTR10
,pl_old.N_EXT_ATTR11
,pl_old.N_EXT_ATTR12
,pl_old.N_EXT_ATTR13
,pl_old.N_EXT_ATTR14
,pl_old.N_EXT_ATTR15
,pl_old.N_EXT_ATTR16
,pl_old.N_EXT_ATTR17
,pl_old.N_EXT_ATTR18
,pl_old.N_EXT_ATTR19
,pl_old.N_EXT_ATTR20
,pl_old.UOM_EXT_ATTR1
,pl_old.UOM_EXT_ATTR2
,pl_old.UOM_EXT_ATTR3
,pl_old.UOM_EXT_ATTR4
,pl_old.UOM_EXT_ATTR5
,pl_old.UOM_EXT_ATTR6
,pl_old.UOM_EXT_ATTR7
,pl_old.UOM_EXT_ATTR8
,pl_old.UOM_EXT_ATTR9
,pl_old.UOM_EXT_ATTR10
,pl_old.UOM_EXT_ATTR11
,pl_old.UOM_EXT_ATTR12
,pl_old.UOM_EXT_ATTR13
,pl_old.UOM_EXT_ATTR14
,pl_old.UOM_EXT_ATTR15
,pl_old.UOM_EXT_ATTR16
,pl_old.UOM_EXT_ATTR17
,pl_old.UOM_EXT_ATTR18
,pl_old.UOM_EXT_ATTR19
,pl_old.UOM_EXT_ATTR20
,pl_old.D_EXT_ATTR1
,pl_old.D_EXT_ATTR2
,pl_old.D_EXT_ATTR3
,pl_old.D_EXT_ATTR4
,pl_old.D_EXT_ATTR5
,pl_old.D_EXT_ATTR6
,pl_old.D_EXT_ATTR7
,pl_old.D_EXT_ATTR8
,pl_old.D_EXT_ATTR9
,pl_old.D_EXT_ATTR10
,pl_old.UDA_TEMPLATE_ID
, -1
FROM PO_LINES_ALL_EXT_B pl_old,PO_LINES_ALL_EXT_B pl_new
WHERE pl_old.po_line_id = pl_new.po_line_id
AND pl_old.ATTR_GROUP_ID = pl_new.ATTR_GROUP_ID
AND pl_old.data_level_id = pl_new.data_level_id
AND pl_old.ATTR_GROUP_ID = l_attr_group_id_tbl(i)
AND pl_old.po_line_id = l_po_lines_tbl(i)
AND pl_old.data_level_id = (SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE')
AND pl_new.draft_id = l_draft_id_tbl(i)
AND pl_old.draft_id = -1;
UPDATE po_lines_all_ext_tl
SET ( EXTENSION_ID,
ATTR_GROUP_ID,
PO_LINE_ID,
DATA_LEVEL_ID,
pk1_value,
SOURCE_LANG,
LANGUAGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
UDA_TEMPLATE_ID,
DRAFT_ID )
=
( SELECT
EXTENSION_ID,
attr_group_id,
po_line_id,
(SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE_CP_DELTA_ARCHIVE'),
l_po_lines_tbl(i)*-1 ,
USERENV('LANG'),
l_language_code_tbl(j),
SYSDATE,
0,
0,
0,
SYSDATE,
UDA_TEMPLATE_ID,
draft_id
FROM po_lines_all_ext_b
WHERE ATTR_GROUP_ID = l_attr_group_id_tbl(i)
AND PO_LINE_ID = -1* l_po_lines_tbl(i)
AND draft_id = l_draft_id_tbl(i) * -1
AND data_level_id = (SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE_CP_DELTA_ARCHIVE'));
INSERT INTO po_lines_all_ext_tl
(
EXTENSION_ID,
ATTR_GROUP_ID,
PO_LINE_ID,
DATA_LEVEL_ID,
pk1_value,
SOURCE_LANG,
LANGUAGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
UDA_TEMPLATE_ID,
DRAFT_ID
)
SELECT
EXTENSION_ID,
attr_group_id,
po_line_id,
(SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE_CP_DELTA_ARCHIVE'),
l_po_lines_tbl(i)*-1 ,
USERENV('LANG'),
l_language_code_tbl(j),
SYSDATE,
0,
0,
0,
SYSDATE,
UDA_TEMPLATE_ID,
draft_id
FROM po_lines_all_ext_b
WHERE ATTR_GROUP_ID = l_attr_group_id_tbl(i)
AND PO_LINE_ID = -1* l_po_lines_tbl(i)
AND draft_id = l_draft_id_tbl(i) * -1
AND data_level_id = (SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE_CP_DELTA_ARCHIVE');
INSERT INTO po_lines_all_ext_tl
(
EXTENSION_ID,
ATTR_GROUP_ID,
PO_LINE_ID,
DATA_LEVEL_ID,
pk1_value,
SOURCE_LANG,
LANGUAGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
UDA_TEMPLATE_ID,
DRAFT_ID
)
SELECT
EXTENSION_ID,
attr_group_id,
po_line_id,
(SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE_CP_OLD_ARCHIVE'),
l_po_lines_tbl(i),
USERENV('LANG'),
l_language_code_tbl(j),
SYSDATE,
0,
0,
0,
SYSDATE,
UDA_TEMPLATE_ID,
draft_id
FROM po_lines_all_ext_b
WHERE ATTR_GROUP_ID = l_attr_group_id_tbl(i)
AND PO_LINE_ID = l_po_lines_tbl(i)
AND draft_id = -1
AND data_level_id = (SELECT data_level_id FROM ego_data_level_b WHERE DATA_LEVEL_NAME = 'PO_LINE_CP_OLD_ARCHIVE');
PO_LOG.stmt(d_module, d_position, 'Delta and old records inserted ');
SELECT DISTINCT pk1_value
BULK COLLECT INTO l_pk1_value_tbl
FROM po_uda_interface
WHERE transaction_id = p_transaction_id;
SELECT ATTR_GROUP_NAME
BULK COLLECT INTO l_attr_grp_name_tbl
FROM (SELECT DISTINCT ATTR_GROUP_NAME
FROM po_uda_interface
WHERE ATTR_GROUP_TYPE = l_attr_group_typ_tbl(j)
AND transaction_id = p_transaction_id
AND pk1_value = l_pk1_value_tbl(i));
SELECT 'N'
INTO l_is_valid
FROM dual
WHERE EXISTS ( SELECT attr_name
FROM ego_attrs_v
WHERE ATTR_GROUP_TYPE = l_attr_group_typ_tbl(j)
AND ATTR_GROUP_NAME = l_attr_grp_name_tbl(k)
GROUP BY attr_group_name, attr_name, required_flag
HAVING required_flag = 'Y'
MINUS
SELECT attr_name
FROM po_uda_interface
WHERE transaction_id = p_transaction_id
AND ATTR_GROUP_TYPE = l_attr_group_typ_tbl(j)
AND ATTR_GROUP_NAME = l_attr_grp_name_tbl(k)
AND pk1_value = l_pk1_value_tbl(i)
) ;