[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, p_last_update_date IN VARCHAR2
, x_canonical_cic_payload OUT NOCOPY XMLTYPE
, x_bundles_processed_count OUT NOCOPY NUMBER
, x_remaining_bundles_count OUT NOCOPY NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_header_tag XMLTYPE;
SELECT
XMLELEMENT( "ItemConfirmation",
XMLELEMENT( "ItemIdentification",
XMLELEMENT( "EBOID" ),
XMLELEMENT( "GTIN",
( SELECT eue.GTIN
FROM EGO_UCCNET_EVENTS eue
WHERE eue.CLN_ID = euaci.CLN_ID
AND eue.SOURCE_SYSTEM_ID = euaci.SOURCE_SYSTEM_ID
AND eue.SOURCE_SYSTEM_REFERENCE = euaci.SOURCE_SYSTEM_REFERENCE )
)
),
XMLELEMENT( "ProcessingError",
XMLELEMENT( "Problem",
XMLELEMENT( "Code", euaci.CODE ),
XMLELEMENT( "Description", XMLATTRIBUTES( c_language_code AS "languageID" ),
status_lkup.DESCRIPTION
),
DECODE ( euaci.DESCRIPTION,
NULL, NULL,
XMLELEMENT( "Note", XMLATTRIBUTES( c_language_code AS "languageID" ),
euaci.DESCRIPTION
)
)
),
DECODE ( euaci.ACTION_NEEDED,
NULL, NULL,
XMLELEMENT( "Resolution",
XMLELEMENT( "Code", euaci.ACTION_NEEDED ),
XMLELEMENT( "Description", XMLATTRIBUTES( c_language_code AS "languageID" ),
corrective_action_lkup.DESCRIPTION
)
)
)
)
) AS ITEM_RESPONSE_TAG
FROM
FND_LOOKUP_VALUES corrective_action_lkup,
FND_LOOKUP_VALUES status_lkup,
EGO_UCCNET_ADD_CIC_INFO euaci,
MTL_ITEM_BULKLOAD_RECS mibr
WHERE
corrective_action_lkup.LANGUAGE (+) = c_language_code
AND corrective_action_lkup.ENABLED_FLAG (+) = 'Y'
AND ( corrective_action_lkup.END_DATE_ACTIVE IS NULL OR corrective_action_lkup.END_DATE_ACTIVE > SYSDATE )
AND corrective_action_lkup.LOOKUP_CODE (+) = euaci.ACTION_NEEDED
AND corrective_action_lkup.LOOKUP_TYPE (+) = 'EGO_ORCH_CORR_ACTION_CODE'
AND status_lkup.LANGUAGE = c_language_code
AND status_lkup.ENABLED_FLAG = 'Y'
AND ( status_lkup.END_DATE_ACTIVE IS NULL OR status_lkup.END_DATE_ACTIVE > SYSDATE )
AND status_lkup.LOOKUP_CODE = euaci.CODE
AND status_lkup.LOOKUP_TYPE = 'EGO_ORCH_STATUS_CODE'
AND euaci.LAST_UPDATE_DATE = TO_DATE ( p_last_update_date, EGO_POST_PROCESS_MESSAGE_PVT.G_DATE_FORMAT )
AND ( euaci.CODE IS NOT NULL )
AND euaci.MESSAGE_SENT_FLAG = 'N'
AND euaci.SOURCE_SYSTEM_REFERENCE = mibr.SOURCE_SYSTEM_REFERENCE
AND euaci.SOURCE_SYSTEM_ID = mibr.SOURCE_SYSTEM_ID
AND euaci.CLN_ID = mibr.BUNDLE_ID
AND mibr.BUNDLE_ID = c_bundle_id
AND mibr.BUNDLE_COLLECTION_ID = c_bundle_collection_id;
SELECT
XMLELEMENT( "ItemPublicationLineConfirmation",
XMLELEMENT( "ItemPublicationLineIdentification",
XMLELEMENT( "EBOID" ),
XMLELEMENT( "AlternateIdentification" ,
XMLELEMENT( "ID" , eue.EXT_COMPLEX_ITEM_REFERENCE )
)
),
XMLELEMENT( "ProcessingStatus",
XMLELEMENT( "Code", c_message_status )
),
c_item_response_tags
) AS ITEM_LINE_RESPONSE_TAG
FROM EGO_UCCNET_EVENTS eue
WHERE
eue.CLN_ID = c_bundle_id
AND ROWNUM = 1;
SELECT *
FROM
( SELECT ROWNUM RN,
MESSAGE_ID,
BUNDLE_ID
FROM
( SELECT eue.MESSAGE_ID MESSAGE_ID,
mibr.BUNDLE_ID BUNDLE_ID
FROM EGO_UCCNET_EVENTS eue,
MTL_ITEM_BULKLOAD_RECS mibr
WHERE
eue.CLN_ID = mibr.BUNDLE_ID
AND mibr.BUNDLE_COLLECTION_ID = c_bundle_collection_id
GROUP BY eue.MESSAGE_ID, mibr.BUNDLE_ID
ORDER BY eue.MESSAGE_ID, mibr.BUNDLE_ID )
WHERE ROWNUM < ( c_start_index + c_bundles_window_size ) )
WHERE RN BETWEEN c_start_index AND ( c_start_index + c_bundles_window_size - 1 );
SELECT
XMLELEMENT( "EBMHeader",
XMLELEMENT( "VerbCode" ),
XMLELEMENT( "Sender",
XMLELEMENT( "ID" )
)
)
INTO l_header_tag
FROM DUAL;
SELECT XMLCONCAT( l_item_response_tags, l_item_response_tag_rec.item_response_tag )
INTO l_item_response_tags
FROM DUAL;
SELECT XMLCONCAT( l_item_line_response_tags, l_item_line_response_tag )
INTO l_item_line_response_tags
FROM DUAL;
SELECT
XMLCONCAT ( XMLELEMENT( "DataArea",
XMLELEMENT( "Sync" ),
XMLELEMENT( "SyncItemPublicationConfirmation",
XMLELEMENT( "ItemPublicationIdentification",
XMLELEMENT( "EBOID" ),
XMLELEMENT( "AlternateIdentification" ,
XMLELEMENT( "ID" , l_previous_message_id )
)
),
l_item_line_response_tags
)
),
l_data_area_tags
)
INTO l_data_area_tags
FROM DUAL;
SELECT XMLCONCAT( l_item_line_response_tags, l_item_line_response_tag )
INTO l_item_line_response_tags
FROM DUAL;
SELECT
XMLCONCAT ( XMLELEMENT( "DataArea",
XMLELEMENT( "Sync" ),
XMLELEMENT( "SyncItemPublicationConfirmation",
XMLELEMENT( "ItemPublicationIdentification",
XMLELEMENT( "EBOID" ),
XMLELEMENT( "AlternateIdentification" ,
XMLELEMENT( "ID" , l_previous_message_id )
)
),
l_item_line_response_tags
)
),
l_data_area_tags
)
INTO l_data_area_tags
FROM DUAL;
SELECT
XMLELEMENT( "SyncItemPublicationConfirmationEBM",
l_header_tag,
l_data_area_tags
)
INTO l_confirmation_message_tag
FROM
DUAL;
SELECT COUNT(1)
INTO l_remaining_bundles_count
FROM
( SELECT ROWNUM RN,
MESSAGE_ID,
BUNDLE_ID
FROM
( SELECT eue.MESSAGE_ID MESSAGE_ID,
mibr.BUNDLE_ID BUNDLE_ID
FROM EGO_UCCNET_EVENTS eue,
MTL_ITEM_BULKLOAD_RECS mibr
WHERE
eue.CLN_ID = mibr.BUNDLE_ID
AND mibr.BUNDLE_COLLECTION_ID = TO_NUMBER ( p_pk1_value )
GROUP BY eue.MESSAGE_ID, mibr.BUNDLE_ID
ORDER BY eue.MESSAGE_ID, mibr.BUNDLE_ID ) )
WHERE RN >= ( p_start_index + p_bundles_window_size );
PROCEDURE Update_Message_Sent_Info_Multi
(
p_version IN VARCHAR2
,p_entity_name IN VARCHAR2
,p_pk1_value IN VARCHAR2
,p_pk2_value IN VARCHAR2
,p_pk3_value IN VARCHAR2
,p_pk4_value IN VARCHAR2
,p_pk5_value IN VARCHAR2
, p_message_status IN VARCHAR2
, p_start_index IN NUMBER
, p_bundles_window_size IN NUMBER
, p_commit_flag IN VARCHAR2
, p_last_update_date IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_sysdate DATE;
SELECT SYSDATE
INTO l_sysdate
FROM DUAL;
UPDATE EGO_UCCNET_EVENTS
SET DISPOSITION_DATE = l_sysdate
WHERE ( CLN_ID ) IN
( SELECT mibr.BUNDLE_ID
FROM
( SELECT ROWNUM RN,
MESSAGE_ID,
BUNDLE_ID
FROM
( SELECT eue.MESSAGE_ID MESSAGE_ID,
mibr.BUNDLE_ID BUNDLE_ID
FROM EGO_UCCNET_EVENTS eue,
MTL_ITEM_BULKLOAD_RECS mibr
WHERE
eue.CLN_ID = mibr.BUNDLE_ID
AND mibr.BUNDLE_COLLECTION_ID = TO_NUMBER ( p_pk1_value )
GROUP BY eue.MESSAGE_ID, mibr.BUNDLE_ID
ORDER BY eue.MESSAGE_ID, mibr.BUNDLE_ID )
WHERE ROWNUM < ( p_start_index + p_bundles_window_size ) ) selected_bundles,
MTL_ITEM_BULKLOAD_RECS mibr
WHERE
mibr.BUNDLE_ID = selected_bundles.BUNDLE_ID
AND selected_bundles.RN BETWEEN p_start_index AND ( p_start_index + p_bundles_window_size - 1 )
AND mibr.BUNDLE_COLLECTION_ID = TO_NUMBER ( p_pk1_value ) );
UPDATE EGO_UCCNET_ADD_CIC_INFO
SET MESSAGE_SENT_FLAG = 'Y'
WHERE MESSAGE_SENT_FLAG = 'N'
AND LAST_UPDATE_DATE = TO_DATE ( p_last_update_date, EGO_POST_PROCESS_MESSAGE_PVT.G_DATE_FORMAT )
AND ( CLN_ID ) IN
( SELECT mibr_outer.BUNDLE_ID
FROM
( SELECT ROWNUM RN,
MESSAGE_ID,
BUNDLE_ID
FROM
( SELECT eue.MESSAGE_ID MESSAGE_ID,
mibr.BUNDLE_ID BUNDLE_ID
FROM EGO_UCCNET_EVENTS eue,
MTL_ITEM_BULKLOAD_RECS mibr
WHERE
eue.CLN_ID = mibr.BUNDLE_ID
AND mibr.BUNDLE_COLLECTION_ID = TO_NUMBER ( p_pk1_value )
GROUP BY eue.MESSAGE_ID, mibr.BUNDLE_ID
ORDER BY eue.MESSAGE_ID, mibr.BUNDLE_ID )
WHERE ROWNUM < ( p_start_index + p_bundles_window_size ) ) selected_bundles,
MTL_ITEM_BULKLOAD_RECS mibr_outer
WHERE
mibr_outer.BUNDLE_ID = selected_bundles.BUNDLE_ID
AND selected_bundles.RN BETWEEN p_start_index AND ( p_start_index + p_bundles_window_size - 1 )
AND mibr_outer.BUNDLE_COLLECTION_ID = TO_NUMBER ( p_pk1_value ) );
UPDATE EGO_UCCNET_EVENTS
SET DISPOSITION_DATE = l_sysdate
WHERE ( CLN_ID, SOURCE_SYSTEM_ID, SOURCE_SYSTEM_REFERENCE ) IN
( SELECT mibr.BUNDLE_ID, mibr.SOURCE_SYSTEM_ID, mibr.SOURCE_SYSTEM_REFERENCE
FROM
( SELECT ROWNUM RN,
MESSAGE_ID,
BUNDLE_ID
FROM
( SELECT eue.MESSAGE_ID MESSAGE_ID,
mibr.BUNDLE_ID BUNDLE_ID
FROM EGO_UCCNET_EVENTS eue,
MTL_ITEM_BULKLOAD_RECS mibr
WHERE
eue.CLN_ID = mibr.BUNDLE_ID
AND mibr.BUNDLE_COLLECTION_ID = TO_NUMBER ( p_pk1_value )
GROUP BY eue.MESSAGE_ID, mibr.BUNDLE_ID
ORDER BY eue.MESSAGE_ID, mibr.BUNDLE_ID )
WHERE ROWNUM < ( p_start_index + p_bundles_window_size ) ) selected_bundles,
MTL_ITEM_BULKLOAD_RECS mibr
WHERE
mibr.BUNDLE_ID = selected_bundles.BUNDLE_ID
AND selected_bundles.RN BETWEEN p_start_index AND ( p_start_index + p_bundles_window_size - 1 )
AND mibr.BUNDLE_COLLECTION_ID = TO_NUMBER ( p_pk1_value ) );
UPDATE EGO_UCCNET_ADD_CIC_INFO
SET MESSAGE_SENT_FLAG = 'Y'
WHERE MESSAGE_SENT_FLAG = 'N'
AND LAST_UPDATE_DATE = TO_DATE ( p_last_update_date, EGO_POST_PROCESS_MESSAGE_PVT.G_DATE_FORMAT )
AND ( CLN_ID, SOURCE_SYSTEM_ID, SOURCE_SYSTEM_REFERENCE ) IN
( SELECT mibr_outer.BUNDLE_ID, mibr_outer.SOURCE_SYSTEM_ID, mibr_outer.SOURCE_SYSTEM_REFERENCE
FROM
( SELECT ROWNUM RN,
MESSAGE_ID,
BUNDLE_ID
FROM
( SELECT eue.MESSAGE_ID MESSAGE_ID,
mibr.BUNDLE_ID BUNDLE_ID
FROM EGO_UCCNET_EVENTS eue,
MTL_ITEM_BULKLOAD_RECS mibr
WHERE
eue.CLN_ID = mibr.BUNDLE_ID
AND mibr.BUNDLE_COLLECTION_ID = TO_NUMBER ( p_pk1_value )
GROUP BY eue.MESSAGE_ID, mibr.BUNDLE_ID
ORDER BY eue.MESSAGE_ID, mibr.BUNDLE_ID )
WHERE ROWNUM < ( p_start_index + p_bundles_window_size ) ) selected_bundles,
MTL_ITEM_BULKLOAD_RECS mibr_outer
WHERE
mibr_outer.BUNDLE_ID = selected_bundles.BUNDLE_ID
AND selected_bundles.RN BETWEEN p_start_index AND ( p_start_index + p_bundles_window_size - 1 )
AND mibr_outer.BUNDLE_COLLECTION_ID = TO_NUMBER ( p_pk1_value ) );
END Update_Message_Sent_Info_Multi;
PROCEDURE Update_Corrective_Info
(
p_bundle_id_tbl IN EGO_VARCHAR_TBL_TYPE
, p_source_system_id_tbl IN EGO_VARCHAR_TBL_TYPE
, p_source_system_ref_tbl IN EGO_VARCHAR_TBL_TYPE
, p_message_type_code IN VARCHAR2
, p_status_code IN VARCHAR2
, p_corrective_action_code IN VARCHAR2
, p_additional_info IN VARCHAR2
, p_last_update_date IN VARCHAR2
, x_last_update_date OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_last_update_login NUMBER;
l_last_updated_by NUMBER;
l_last_update_date VARCHAR2(50);
l_last_update_login := FND_GLOBAL.LOGIN_ID;
l_last_updated_by := FND_GLOBAL.USER_ID;
IF ( p_last_update_date IS NULL )
THEN
SELECT SYSDATE, TO_CHAR ( SYSDATE, EGO_POST_PROCESS_MESSAGE_PVT.G_DATE_FORMAT )
INTO l_sysdate, l_last_update_date
FROM DUAL;
l_sysdate := TO_DATE ( p_last_update_date, EGO_POST_PROCESS_MESSAGE_PVT.G_DATE_FORMAT );
l_last_update_date := p_last_update_date;
UPDATE EGO_UCCNET_EVENTS
SET DISPOSITION_CODE = p_message_type_code
, LAST_UPDATED_BY = l_last_updated_by
, LAST_UPDATE_DATE = l_sysdate
, LAST_UPDATE_LOGIN = l_last_update_login
WHERE
CLN_ID = TO_NUMBER( p_bundle_id_tbl(i) ) ;
INSERT INTO EGO_UCCNET_ADD_CIC_INFO
(
CLN_ID
, SOURCE_SYSTEM_ID
, SOURCE_SYSTEM_REFERENCE
, CODE
, DESCRIPTION
, ACTION_NEEDED
, MESSAGE_SENT_FLAG
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
)
SELECT
CLN_ID
, SOURCE_SYSTEM_ID
, SOURCE_SYSTEM_REFERENCE
, p_status_code
, p_additional_info
, p_corrective_action_code
, 'N'
, l_last_updated_by
, l_sysdate
, l_last_updated_by
, l_sysdate
, l_last_update_login
FROM EGO_UCCNET_EVENTS
WHERE
CLN_ID = TO_NUMBER( p_bundle_id_tbl(i) ) ;
UPDATE EGO_UCCNET_EVENTS
SET DISPOSITION_CODE = p_message_type_code
, LAST_UPDATED_BY = l_last_updated_by
, LAST_UPDATE_DATE = l_sysdate
, LAST_UPDATE_LOGIN = l_last_update_login
WHERE
SOURCE_SYSTEM_REFERENCE = p_source_system_ref_tbl(i)
AND SOURCE_SYSTEM_ID = TO_NUMBER( p_source_system_id_tbl(i) )
AND CLN_ID = TO_NUMBER( p_bundle_id_tbl(i) );
INSERT INTO EGO_UCCNET_ADD_CIC_INFO
(
CLN_ID
, SOURCE_SYSTEM_ID
, SOURCE_SYSTEM_REFERENCE
, CODE
, DESCRIPTION
, ACTION_NEEDED
, MESSAGE_SENT_FLAG
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
)
VALUES
(
TO_NUMBER( p_bundle_id_tbl(i) )
, TO_NUMBER( p_source_system_id_tbl(i) )
, p_source_system_ref_tbl(i)
, p_status_code
, p_additional_info
, p_corrective_action_code
, 'N'
, l_last_updated_by
, l_sysdate
, l_last_updated_by
, l_sysdate
, l_last_update_login
);
x_last_update_date := l_last_update_date;
END Update_Corrective_Info;
l_last_update_date VARCHAR2(50);
SELECT NVL ( eios.ENABLED_FOR_DATA_POOL, 'N' ) AS ENABLED_FOR_DATA_POOL
FROM EGO_IMPORT_OPTION_SETS eios
WHERE eios.BATCH_ID = c_batch_id;
SELECT msii.BUNDLE_ID, msii.SOURCE_SYSTEM_ID, msii.SOURCE_SYSTEM_REFERENCE
FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
( SELECT DISTINCT msii_inner1.BUNDLE_ID BUNDLE_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE msii_inner1
WHERE msii_inner1.REQUEST_ID = c_request_id
AND msii_inner1.SET_PROCESS_ID = c_batch_id
) selected_bundles
WHERE
'S' = COALESCE (
( SELECT 'F'
FROM MTL_SYSTEM_ITEMS_INTERFACE msii_inner2
WHERE
msii_inner2.PROCESS_FLAG <> 7
AND msii_inner2.BUNDLE_ID = selected_bundles.BUNDLE_ID
AND msii_inner2.SET_PROCESS_ID = c_batch_id
AND ROWNUM = 1 )
,( SELECT 'F'
FROM EGO_ITM_USR_ATTR_INTRFC eiuai
WHERE
( ( eiuai.PROCESS_STATUS <> 4 ) AND ( eiuai.PROCESS_STATUS <> 7 ) )
AND eiuai.BUNDLE_ID = selected_bundles.BUNDLE_ID
AND eiuai.DATA_SET_ID = c_batch_id
AND ROWNUM = 1 )
,( SELECT 'F'
FROM EGO_ITEM_ASSOCIATIONS_INTF eiai
WHERE
eiai.PROCESS_FLAG <> 7
AND eiai.BUNDLE_ID = selected_bundles.BUNDLE_ID
AND eiai.BATCH_ID = c_batch_id
AND ROWNUM = 1 )
,( SELECT 'F'
FROM MTL_ITEM_CATEGORIES_INTERFACE mici
WHERE
mici.PROCESS_FLAG <> 7
AND mici.BUNDLE_ID = selected_bundles.BUNDLE_ID
AND mici.SET_PROCESS_ID = c_batch_id
AND ROWNUM = 1 )
,( SELECT 'F'
FROM BOM_BILL_OF_MTLS_INTERFACE bbmi
WHERE
bbmi.PROCESS_FLAG <> 7
AND bbmi.BUNDLE_ID = selected_bundles.BUNDLE_ID
AND bbmi.BATCH_ID = c_batch_id
AND ROWNUM = 1 )
,( SELECT 'F'
FROM BOM_INVENTORY_COMPS_INTERFACE bici
WHERE
bici.PROCESS_FLAG <> 7
AND bici.BUNDLE_ID = selected_bundles.BUNDLE_ID
AND bici.BATCH_ID = c_batch_id
AND ROWNUM = 1 )
/* -- Not checking the TL table as PROCESS_STATUS is not updated by import
,( SELECT 'F'
FROM EGO_INTERFACE_TL eit
WHERE
eit.PROCESS_STATUS <> 7
AND eit.BUNDLE_ID = selected_bundles.BUNDLE_ID
AND eit.SET_PROCESS_ID = c_batch_id
AND ROWNUM = 1 )
*/
,( SELECT 'S'
FROM DUAL )
)
AND msii.TOP_ITEM_FLAG = 'Y'
AND msii.BUNDLE_ID = selected_bundles.BUNDLE_ID
AND msii.SET_PROCESS_ID = c_batch_id;
l_last_update_date := NULL;
l_bundle_id_tbl.DELETE;
l_source_system_id_tbl.DELETE;
l_source_system_ref_tbl.DELETE;
EGO_POST_PROCESS_MESSAGE_PVT.Update_Corrective_Info(
p_bundle_id_tbl => l_bundle_id_tbl,
p_source_system_id_tbl => l_source_system_id_tbl,
p_source_system_ref_tbl => l_source_system_ref_tbl,
p_message_type_code => EGO_POST_PROCESS_MESSAGE_PVT.G_CIC_SYNC_MESSAGE_TYPE,
p_status_code => NULL,
p_corrective_action_code => NULL,
p_additional_info => NULL,
p_last_update_date => l_last_update_date,
x_last_update_date => l_last_update_date,
x_return_status => l_return_status,
x_msg_data => l_msg_data
);
SELECT XMLCONCAT ( l_item_bundle_tags,
XMLELEMENT ( "ItemBundle",
XMLELEMENT ( "BundleId", l_bundle_id_tbl(i) ),
XMLELEMENT ( "ItemSourceSystemId", l_source_system_id_tbl(i) ),
XMLELEMENT ( "ItemSourceSystemReference", l_source_system_ref_tbl(i) )
)
)
INTO l_item_bundle_tags
FROM DUAL;
SELECT XMLELEMENT ( "ItemBundles",
l_item_bundle_tags
)
INTO l_item_bundles_tag
FROM DUAL;
p_last_update_date => l_last_update_date,
x_msg_data => l_msg_data,
x_return_status => l_return_status
);