[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login
,program_application_id ,program_id,program_update_date
,process_activity ,notification_flag ,type
,message_source_code ,language
,message_text, transaction_id
)
SELECT
request_id ,'HEADER' ,NULL ,NULL ,NULL ,NULL
,order_source_id ,orig_sys_document_ref
,NULL, NULL ,change_sequence
,NULL, sysdate, FND_GLOBAL.USER_ID ,sysdate
,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
,660 ,NULL ,NULL
,NULL ,NULL ,NULL
,'C' ,USERENV('LANG')
,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id
AND (order_source_id IS NULL OR orig_sys_document_ref IS NULL);
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login
,program_application_id ,program_id,program_update_date
,process_activity ,notification_flag ,type
,message_source_code ,language
,message_text, transaction_id
)
SELECT
request_id ,'HEADER' ,NULL ,NULL ,NULL ,NULL
,order_source_id ,orig_sys_document_ref
,NULL, NULL ,change_sequence
,NULL, sysdate, FND_GLOBAL.USER_ID ,sysdate
,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
,660 ,NULL ,NULL
,NULL ,NULL ,NULL
,'C' ,USERENV('LANG')
,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL h
WHERE batch_id = p_batch_id
AND EXISTS (SELECT 'Y'
FROM OE_ORDER_HEADERS oh
WHERE oh.order_source_id = h.order_source_id
AND oh.orig_sys_document_ref = h.orig_sys_document_ref
);
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login
,program_application_id ,program_id,program_update_date
,process_activity ,notification_flag ,type
,message_source_code ,language
,message_text, transaction_id
)
SELECT
h.request_id ,'HEADER' ,NULL ,NULL ,NULL ,NULL
,order_source_id ,orig_sys_document_ref
,NULL, NULL ,change_sequence
,NULL, sysdate, FND_GLOBAL.USER_ID ,sysdate
,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
,660 ,NULL ,NULL
,NULL ,NULL ,NULL
,'C' ,USERENV('LANG')
,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL h
WHERE h.batch_id = p_batch_id
AND ( h.payment_type_code = 'CREDIT_CARD'
OR h.order_source_id = 10 -- 'Internal' Orders
OR h.customer_preference_set_code IS NOT NULL -- Value here requires lines to be in sets!
OR h.return_reason_code IS NOT NULL -- for RETURN orders
-- Bug 3355762
-- Import of closed orders is not supported with HVOP
OR h.closed_flag = 'Y'
);
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login
,program_application_id ,program_id,program_update_date
,process_activity ,notification_flag ,type
,message_source_code ,language
,message_text, transaction_id
)
SELECT
h.request_id ,'LINE' ,NULL ,NULL ,NULL ,NULL
,l.order_source_id ,l.orig_sys_document_ref
,l.orig_sys_line_ref, l.orig_sys_shipment_ref, l.change_sequence
,NULL, sysdate, FND_GLOBAL.USER_ID ,sysdate
,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
,660 ,NULL ,NULL
,NULL ,NULL ,NULL
,'C' ,USERENV('LANG')
,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL h, OE_LINES_IFACE_ALL l
WHERE h.batch_id = p_batch_id
AND h.order_source_id = l.order_source_id
AND h.orig_sys_document_ref = l.orig_sys_document_ref
AND l.orig_sys_line_ref IS NULL
AND l.orig_sys_shipment_ref IS NULL;
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login
,program_application_id ,program_id,program_update_date
,process_activity ,notification_flag ,type
,message_source_code ,language
,message_text, transaction_id
)
SELECT
h.request_id ,'LINE' ,NULL ,NULL ,NULL ,NULL
,l.order_source_id ,l.orig_sys_document_ref
,l.orig_sys_line_ref, l.orig_sys_shipment_ref, l.change_sequence
,NULL, sysdate, FND_GLOBAL.USER_ID ,sysdate
,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
,660 ,NULL ,NULL
,NULL ,NULL ,NULL
,'C' ,USERENV('LANG')
,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL h, OE_LINES_IFACE_ALL l
WHERE h.batch_id = p_batch_id
AND h.order_source_id = l.order_source_id
AND h.orig_sys_document_ref = l.orig_sys_document_ref
AND ( nvl(l.source_type_code,'INTERNAL') <> 'INTERNAL' -- Drop ships
OR (l.arrival_set_name IS NOT NULL OR l.ship_set_name IS NOT NULL)
OR l.commitment_id IS NOT NULL
OR l.return_reason_code IS NOT NULL -- for RETURN lines
OR l.override_atp_date_code IS NOT NULL
);
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login
,program_application_id ,program_id,program_update_date
,process_activity ,notification_flag ,type
,message_source_code ,language
,message_text, transaction_id
)
SELECT
h.request_id ,decode(l.orig_sys_line_ref||l.orig_sys_shipment_ref,NULL,'HEADER_ADJ','LINE_ADJ')
,NULL ,NULL ,NULL ,NULL
,l.order_source_id ,l.orig_sys_document_ref
,l.orig_sys_line_ref, l.orig_sys_shipment_ref, l.change_sequence
,NULL, sysdate, FND_GLOBAL.USER_ID ,sysdate
,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
,660 ,NULL ,NULL
,NULL ,NULL ,NULL
,'C' ,USERENV('LANG')
,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL h, OE_PRICE_ADJS_INTERFACE l
WHERE h.batch_id = p_batch_id
AND h.order_source_id = l.order_source_id
AND h.orig_sys_document_ref = l.orig_sys_document_ref
AND l.orig_sys_discount_ref IS NULL;
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id , 'HEADER',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,NULL
,NULL ,change_sequence,NULL,NULL,NULL,'ACCOUNTING_RULE_ID'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL b
WHERE batch_id = p_batch_id and
b.accounting_rule_id IS NOT NULL and
NOT EXISTS (SELECT RULE_ID
FROM OE_RA_RULES_V
WHERE RULE_ID = b.accounting_rule_id
AND STATUS = 'A'
AND TYPE in ('A', 'ACC_DUR','PP_DR_ALL','PP_DR_PP'));--bug 4893057
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id ,'LINE',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'ACCOUNTING_RULE_ID'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_LINES_IFACE_ALL b
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND b.accounting_rule_id IS NOT NULL and
NOT EXISTS (SELECT RULE_ID
FROM OE_RA_RULES_V
WHERE RULE_ID = b.accounting_rule_id
AND STATUS = 'A'
AND TYPE in ('A', 'ACC_DUR','PP_DR_ALL','PP_DR_PP'));--bug 4893057
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id , 'HEADER' ,NULL ,NULL ,NULL ,NULL
,order_source_id ,orig_sys_document_ref ,NULL ,NULL
,change_sequence ,NULL ,NULL ,NULL ,'CONVERSION_TYPE'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL b
WHERE batch_id = p_batch_id and
b.CONVERSION_TYPE_CODE IS NOT NULL and
NOT EXISTS (SELECT CONVERSION_TYPE
FROM OE_GL_DAILY_CONVERSION_TYPES_V a
WHERE CONVERSION_TYPE = b.CONVERSION_TYPE_CODE);
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id , 'HEADER',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,NULL
,NULL ,change_sequence,NULL,NULL,NULL,'DEMAND_CLASS_CODE'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL b
WHERE batch_id = p_batch_id and
b.demand_class_code IS NOT NULL and
NOT EXISTS (SELECT FLV.LOOKUP_CODE
from FND_LOOKUP_TYPES FLT, FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE = FLT.LOOKUP_TYPE
and FLV.SECURITY_GROUP_ID = FLT.SECURITY_GROUP_ID
and FLV.VIEW_APPLICATION_ID = FLT.VIEW_APPLICATION_ID
and FLV.LANGUAGE = userenv('LANG')
and FLV.VIEW_APPLICATION_ID = 3
and FLV.SECURITY_GROUP_ID = fnd_global.lookup_security_group(FLV.LOOKUP_TYPE, FLV.VIEW_APPLICATION_ID)
AND FLV.LOOKUP_CODE = b.demand_class_code
AND FLV.LOOKUP_TYPE = 'DEMAND_CLASS'
AND FLT.APPLICATION_ID = 700
AND FLV.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(FLV.START_DATE_ACTIVE, SYSDATE)
AND NVL(FLV.END_DATE_ACTIVE, SYSDATE)
);
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id ,'LINE',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'DEMAND_CLASS_CODE'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_LINES_IFACE_ALL b
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND b.demand_class_code IS NOT NULL and
NOT EXISTS (SELECT FLV.LOOKUP_CODE
from FND_LOOKUP_TYPES FLT, FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE = FLT.LOOKUP_TYPE
and FLV.SECURITY_GROUP_ID = FLT.SECURITY_GROUP_ID
and FLV.VIEW_APPLICATION_ID = FLT.VIEW_APPLICATION_ID
and FLV.LANGUAGE = userenv('LANG')
and FLV.VIEW_APPLICATION_ID = 3
and FLV.SECURITY_GROUP_ID = fnd_global.lookup_security_group(FLV.LOOKUP_TYPE, FLV.VIEW_APPLICATION_ID)
AND FLV.LOOKUP_CODE = b.demand_class_code
AND FLV.LOOKUP_TYPE = 'DEMAND_CLASS'
AND FLT.APPLICATION_ID = 700
AND FLV.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(FLV.START_DATE_ACTIVE, SYSDATE)
AND NVL(FLV.END_DATE_ACTIVE, SYSDATE)
);
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id , 'HEADER',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,NULL
,NULL ,change_sequence,NULL,NULL,NULL,'FOB_POINT'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL b
WHERE batch_id = p_batch_id and
b.fob_point_code IS NOT NULL and
NOT EXISTS (SELECT LOOKUP_CODE
FROM OE_AR_LOOKUPS_V
WHERE LOOKUP_CODE = b.fob_point_code
AND LOOKUP_TYPE = 'FOB'
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE))
;
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id ,'LINE',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'FOB_POINT_CODE'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_LINES_IFACE_ALL b
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND b.fob_point_code IS NOT NULL and
NOT EXISTS (SELECT LOOKUP_CODE
FROM OE_AR_LOOKUPS_V
WHERE LOOKUP_CODE = b.fob_point_code
AND LOOKUP_TYPE = 'FOB'
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE));
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id , 'HEADER',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,NULL
,NULL ,change_sequence,NULL,NULL,NULL,'FREIGHT_TERMS_CODE'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL b
WHERE batch_id = p_batch_id and
b.freight_terms_code IS NOT NULL and
NOT EXISTS (SELECT LOOKUP_CODE
FROM OE_LOOKUPS
WHERE LOOKUP_CODE = b.freight_terms_code
AND LOOKUP_TYPE = 'FREIGHT_TERMS'
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE))
;
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id ,'LINE',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'FREIGHT_TERMS_CODE'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_LINES_IFACE_ALL b
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND b.freight_terms_code IS NOT NULL and
NOT EXISTS (SELECT LOOKUP_CODE
FROM OE_LOOKUPS
WHERE LOOKUP_CODE = b.freight_terms_code
AND LOOKUP_TYPE = 'FREIGHT_TERMS'
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE));
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id , 'HEADER',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,NULL
,NULL ,change_sequence,NULL,NULL,NULL,'INVOICING_RULE_ID'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL b
WHERE batch_id = p_batch_id and
b.invoicing_rule_id IS NOT NULL and
NOT EXISTS (SELECT RULE_ID
FROM OE_RA_RULES_V
WHERE RULE_ID = b.invoicing_rule_id
AND STATUS = 'A'
AND TYPE = 'I')
;
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id ,'LINE',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'INVOICING_RULE_ID'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_LINES_IFACE_ALL b
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND b.invoicing_rule_id IS NOT NULL and
NOT EXISTS (SELECT RULE_ID
FROM OE_RA_RULES_V
WHERE RULE_ID = b.invoicing_rule_id
AND STATUS = 'A'
AND TYPE = 'I');
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id , 'HEADER' ,NULL ,NULL ,NULL ,NULL
,order_source_id ,orig_sys_document_ref ,NULL ,NULL
,change_sequence ,NULL ,NULL ,NULL ,'ORDER_DATE_TYPE_CODE'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL b
WHERE batch_id = p_batch_id
AND b.order_date_type_code IS NOT NULL
AND NOT EXISTS (SELECT LOOKUP_CODE
FROM OE_LOOKUPS a
WHERE LOOKUP_TYPE = 'REQUEST_DATE_TYPE'
AND LOOKUP_CODE = b.order_date_type_code
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE));
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id , 'HEADER',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,NULL
,NULL ,change_sequence,NULL,NULL,NULL,'PAYMENT_TERM_ID'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL b
WHERE batch_id = p_batch_id and
b.payment_term_id IS NOT NULL and
NOT EXISTS (select a.TERM_ID
FROM OE_RA_TERMS_V a
WHERE TERM_ID = b.payment_term_id
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE))
;
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id ,'LINE',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'PAYMENT_TERM_ID'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_LINES_IFACE_ALL b
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND b.payment_term_id IS NOT NULL and
NOT EXISTS (select a.TERM_ID
FROM OE_RA_TERMS_V a
WHERE TERM_ID = b.payment_term_id
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE));
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id , 'HEADER',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,NULL
,NULL ,change_sequence,NULL,NULL,NULL,'PRICE_LIST_ID'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL b
WHERE batch_id = p_batch_id and
b.price_list_id IS NOT NULL and
NOT EXISTS (select list_header_id
FROM qp_list_headers_vl
WHERE list_header_id = b.price_list_id
and list_type_code in ('PRL', 'AGR') and
nvl(active_flag,'Y') ='Y')
;
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id ,'LINE',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'PRICE_LIST_ID'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_LINES_IFACE_ALL b
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND b.price_list_id IS NOT NULL and
NOT EXISTS (select list_header_id
FROM qp_list_headers_vl
WHERE list_header_id = b.price_list_id
and list_type_code in ('PRL', 'AGR') and
nvl(active_flag,'Y') ='Y');
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id , 'HEADER',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,NULL
,NULL ,change_sequence,NULL,NULL,NULL,'SHIPMENT_PRIORITY_CODE'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL b
WHERE batch_id = p_batch_id and
b.shipment_priority_code IS NOT NULL and
NOT EXISTS (select LOOKUP_CODE
FROM OE_LOOKUPS
WHERE LOOKUP_CODE = b.shipment_priority_code
AND LOOKUP_TYPE = 'SHIPMENT_PRIORITY'
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE))
;
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id ,'LINE',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'SHIPMENT_PRIORITY_CODE'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_LINES_IFACE_ALL b
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND b.shipment_priority_code IS NOT NULL and
NOT EXISTS (select LOOKUP_CODE
FROM OE_LOOKUPS
WHERE LOOKUP_CODE = b.shipment_priority_code
AND LOOKUP_TYPE = 'SHIPMENT_PRIORITY'
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE));
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id , 'HEADER',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,NULL
,NULL ,change_sequence,NULL,NULL,NULL,'SHIPPING_METHOD'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL b
WHERE batch_id = p_batch_id and
b.shipping_method_code IS NOT NULL and
NOT EXISTS (select LOOKUP_CODE
FROM OE_SHIP_METHODS_V
WHERE lookup_code = b.shipping_method_code
AND SYSDATE <= NVL(END_DATE_ACTIVE, SYSDATE))
;
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id ,'LINE',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'SHIPPING_METHOD_CODE'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_LINES_IFACE_ALL b
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND b.shipping_method_code IS NOT NULL and
NOT EXISTS (select LOOKUP_CODE
FROM OE_SHIP_METHODS_V
WHERE lookup_code = b.shipping_method_code
AND SYSDATE <= NVL(END_DATE_ACTIVE, SYSDATE)) ;
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id , 'HEADER',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,NULL
,NULL ,change_sequence,NULL,NULL,NULL,'SOLD_TO_ORG'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL b
WHERE batch_id = p_batch_id and
b.sold_to_org_id IS NOT NULL and
NOT EXISTS (select ORGANIZATION_ID
FROM OE_SOLD_TO_ORGS_V
WHERE ORGANIZATION_ID = b.sold_to_org_id
AND STATUS = 'A'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE))
;
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id ,'LINE',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'SOLD_TO_ORG_ID'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_LINES_IFACE_ALL b
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND b.sold_to_org_id IS NOT NULL and
NOT EXISTS (select ORGANIZATION_ID
FROM OE_SOLD_TO_ORGS_V
WHERE ORGANIZATION_ID = b.sold_to_org_id
AND STATUS = 'A'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE));
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id , 'HEADER',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,NULL
,NULL ,change_sequence,NULL,NULL,NULL,'TAX_EXEMPT_FLAG'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL b
WHERE batch_id = p_batch_id and
b.tax_exempt_flag IS NOT NULL and
NOT EXISTS (SELECT LOOKUP_CODE
FROM OE_AR_LOOKUPS_V
WHERE LOOKUP_CODE = b.tax_exempt_flag
AND LOOKUP_TYPE = 'TAX_CONTROL_FLAG'
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE))
;
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id ,'LINE',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'TAX_EXEMPT_FLAG'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_LINES_IFACE_ALL b
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND b.tax_exempt_flag IS NOT NULL and
NOT EXISTS (SELECT LOOKUP_CODE
FROM OE_AR_LOOKUPS_V
WHERE LOOKUP_CODE = b.tax_exempt_flag
AND LOOKUP_TYPE = 'TAX_CONTROL_FLAG'
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE));
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id , 'HEADER' ,NULL ,NULL ,NULL ,NULL
,order_source_id ,orig_sys_document_ref ,NULL ,NULL
,change_sequence ,NULL ,NULL ,NULL ,'TRANSACTIONAL_CURR_CODE'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL b
WHERE batch_id = p_batch_id and
b.transactional_curr_code IS NOT NULL and
NOT EXISTS (SELECT CURRENCY_CODE
FROM OE_FND_CURRENCIES_V
WHERE CURRENCY_CODE = b.transactional_curr_code
AND CURRENCY_FLAG = 'Y'
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE));
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id , 'HEADER' ,NULL ,NULL ,NULL ,NULL
,order_source_id ,orig_sys_document_ref ,NULL ,NULL
,change_sequence ,NULL ,NULL ,NULL ,'PAYMENT_TYPE_CODE'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL b
WHERE batch_id = p_batch_id and
b.payment_type_code IS NOT NULL and
NOT EXISTS (SELECT LOOKUP_CODE
FROM OE_LOOKUPS
WHERE LOOKUP_CODE = b.payment_type_code
AND LOOKUP_TYPE = 'PAYMENT TYPE'
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE));
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT /* MOAC_SQL_CHANGE */
request_id , 'HEADER',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,NULL
,NULL ,change_sequence,NULL,NULL,NULL,'SALESREP_ID'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL b
WHERE batch_id = p_batch_id
AND b.salesrep_id IS NOT NULL
AND NOT EXISTS (SELECT a.salesrep_id
from ra_salesreps_all a
where salesrep_id =b.salesrep_id
and sysdate between NVL(start_date_active,sysdate)
and NVL(end_date_active,sysdate))
AND NOT EXISTS (SELECT jrs.salesrep_id
from jtf_rs_salesreps jrs,
jtf_rs_resource_extns jre
where jrs.salesrep_id = b.salesrep_id
and jrs.resource_id = jre.resource_id
and jre.category in ('EMPLOYEE','OTHER','PARTY','PARTNER','SUPPLIER_CONTACT')
and jrs.org_id = l_org_id
/* and nvl(jrs.ORG_ID,nvl(to_number(decode(substrb(userenv('CLIENT_INFO'),1,1),' ',
null,substrb(userenv('CLIENT_INFO'),1,10))),-99)) =
nvl(to_number(decode(substrb(USERENV('CLIENT_INFO'),1,1),' ',null,
substrb(userenv('CLIENT_INFO'),1,10))),-99) */
and sysdate between nvl(jrs.start_date_active,sysdate)
and nvl(jrs.end_date_active,sysdate))
;
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT /* MOAC_SQL_CHANGE */
request_id ,'LINE',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'SALESREP_ID'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_LINES_IFACE_ALL b
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND b.salesrep_id IS NOT NULL
AND NOT EXISTS (SELECT a.salesrep_id
from ra_salesreps_all a
where salesrep_id =b.salesrep_id
and sysdate between NVL(start_date_active,sysdate)
and NVL(end_date_active,sysdate))
AND NOT EXISTS (SELECT jrs.salesrep_id
from jtf_rs_salesreps jrs,
jtf_rs_resource_extns jre
where jrs.salesrep_id = b.salesrep_id
and jrs.resource_id = jre.resource_id
and jre.category in ('EMPLOYEE','OTHER','PARTY','PARTNER','SUPPLIER_CONTACT')
and jrs.org_id = l_org_id
/* and nvl(jrs.ORG_ID,nvl(to_number(decode(substrb(userenv('CLIENT_INFO'),1,1),' ',
null,substrb(userenv('CLIENT_INFO'),1,10))),-99)) =
nvl(to_number(decode(substrb(USERENV('CLIENT_INFO'),1,1),' ',null,
substrb(userenv('CLIENT_INFO'),1,10))),-99) */
and sysdate between nvl(jrs.start_date_active,sysdate)
and nvl(jrs.end_date_active,sysdate))
;
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id , 'HEADER' ,NULL ,NULL ,NULL ,NULL
,order_source_id ,orig_sys_document_ref ,NULL ,NULL
,change_sequence ,NULL ,NULL ,NULL ,'SALES_CHANNEL_CODE'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL b
WHERE batch_id = p_batch_id and
b.sales_channel_code IS NOT NULL and
NOT EXISTS (SELECT LOOKUP_CODE
FROM OE_LOOKUPS
WHERE LOOKUP_CODE = b.sales_channel_code
AND LOOKUP_TYPE = 'SALES_CHANNEL'
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE));
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id ,'LINE',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'END_ITEM_UNIT_NUMBER'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_LINES_IFACE_ALL b
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND b.end_item_unit_number IS NOT NULL and
NOT EXISTS (SELECT unit_number
FROM pjm_unit_numbers_lov_v
WHERE unit_number = b.end_item_unit_number);
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id ,'LINE',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'END_ITEM_UNIT_NUMBER'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_LINES_IFACE_ALL b
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND NVL(b.calculate_price_flag,'Y') NOT IN ('Y','P','N');
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id ,'LINE',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'ORDERED_QUANTITY_UOM2'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_LINES_IFACE_ALL b
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
-- Added to fix bug 5394064. Validate this ID only if populated
AND b.inventory_item_id IS NOT NULL -- added to fix bug 5394064
AND NOT EXISTS (
SELECT tracking_quantity_ind
FROM mtl_system_items
WHERE organization_id = b.ship_from_org_id
AND inventory_item_id = b.inventory_item_id
);
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id ,'LINE',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'ORDERED_QUANTITY_UOM2'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_LINES_IFACE_ALL b
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND ( b.ordered_quantity_uom2 IS NOT NULL
OR b.ordered_quantity2 IS NOT NULL )
AND EXISTS (
SELECT tracking_quantity_ind
FROM mtl_system_items
WHERE organization_id = b.ship_from_org_id
AND inventory_item_id = b.inventory_item_id
and tracking_quantity_ind = 'P' );
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id ,'LINE',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'ORDERED_QUANTITY_UOM2'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_LINES_IFACE_ALL b
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND ( b.ordered_quantity_uom2 IS NOT NULL )
AND NOT EXISTS (
SELECT tracking_quantity_ind
FROM mtl_system_items
WHERE organization_id = b.ship_from_org_id
AND inventory_item_id = b.inventory_item_id
and ( tracking_quantity_ind = 'PS' or
ont_pricing_qty_source = 'S')
and secondary_uom_code = b.ordered_quantity_uom2 );
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id ,'LINE',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'ORDERED_QUANTITY_UOM2'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_LINES_IFACE_ALL b
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND ( b.ordered_quantity_uom2 IS NULL)
AND EXISTS (
SELECT tracking_quantity_ind
FROM mtl_system_items
WHERE organization_id = b.ship_from_org_id
AND inventory_item_id = b.inventory_item_id
and ( tracking_quantity_ind = 'PS' or
ont_pricing_qty_source = 'S')
);
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id ,'LINE',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'PREFERRED_GRADE'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_LINES_IFACE_ALL b
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND b.preferred_grade IS NOT NULL
AND b.ship_from_org_id IS NOT NULL
AND b.inventory_item_id IS NOT NULL
AND ( NOT EXISTS (
SELECT grade_control_flag
FROM mtl_system_items
WHERE organization_id = b.ship_from_org_id
AND inventory_item_id = b.inventory_item_id
and grade_control_flag = 'Y' )
OR NOT EXISTS (SELECT preferred_grade
FROM mtl_grades_b
WHERE grade_code = b.preferred_grade
) );
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id , 'HEADER',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,NULL
,NULL ,change_sequence,NULL,NULL,NULL,'TRANSACTION_PHASE_CODE'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL b
WHERE batch_id = p_batch_id and
b.transaction_phase_code IS NOT NULL
-- Only fulfillment orders are supported for HVOP
AND nvl(b.transaction_phase_code,'F') <> 'F'
;
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id , 'HEADER',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,NULL
,NULL ,change_sequence,NULL,NULL,NULL,'VERSION_NUMBER'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL b
WHERE batch_id = p_batch_id and
b.version_number IS NOT NULL
-- Version number cannot be negative or in decimals
AND (b.version_number < 0
OR mod(b.version_number,1) <> 0
)
;
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
h.request_id,decode(orig_sys_line_ref,NULL,'HEADER_ADJ','LINE_ADJ'),NULL ,NULL ,NULL
,NULL, a.order_source_id ,a.orig_sys_document_ref, a.orig_sys_line_ref ,NULL
,a.change_sequence ,NULL ,NULL ,NULL ,'APPLIED_FLAG'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_PRICE_ADJS_INTERFACE a, OE_HEADERS_IFACE_ALL h
WHERE h.batch_id = p_batch_id
AND a.order_source_id = h.order_source_id
AND a.orig_sys_document_ref = h.orig_sys_document_ref
AND a.APPLIED_FLAG IS NOT NULL
AND a.applied_flag NOT IN ('Y','N');
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
h.request_id,decode(orig_sys_line_ref,NULL,'HEADER_ADJ','LINE_ADJ'),NULL ,NULL ,NULL
,NULL, a.order_source_id ,a.orig_sys_document_ref, a.orig_sys_line_ref ,NULL
,a.change_sequence ,NULL ,NULL ,NULL ,'INCLUDE_ON_RETURNS_FLAG'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_PRICE_ADJS_INTERFACE a, OE_HEADERS_IFACE_ALL h
WHERE h.batch_id = p_batch_id
AND a.order_source_id = h.order_source_id
AND a.orig_sys_document_ref = h.orig_sys_document_ref
AND a.INCLUDE_ON_RETURNS_FLAG IS NOT NULL
AND a.include_on_returns_flag NOT IN ('Y','N');
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
h.request_id,decode(orig_sys_line_ref,NULL,'HEADER_ADJ','LINE_ADJ'),NULL ,NULL ,NULL
,NULL, a.order_source_id ,a.orig_sys_document_ref, a.orig_sys_line_ref ,NULL
,a.change_sequence ,NULL ,NULL ,NULL ,'CREDIT_OR_CHARGE_FLAG'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_PRICE_ADJS_INTERFACE a, OE_HEADERS_IFACE_ALL h
WHERE h.batch_id = p_batch_id
AND a.order_source_id = h.order_source_id
AND a.orig_sys_document_ref = h.orig_sys_document_ref
AND a.CREDIT_OR_CHARGE_FLAG IS NOT NULL
AND NOT EXISTS (SELECT 'Y'
FROM OE_LOOKUPS l
WHERE LOOKUP_TYPE = 'CREDIT_OR_CHARGE_FLAG'
AND LOOKUP_CODE = a.credit_or_charge_flag);
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
h.request_id,decode(orig_sys_line_ref,NULL,'HEADER_ADJ','LINE_ADJ'),NULL ,NULL ,NULL
,NULL, a.order_source_id ,a.orig_sys_document_ref, a.orig_sys_line_ref ,NULL
,a.change_sequence ,NULL ,NULL ,NULL ,'CHARGE_TYPE_CODE'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_PRICE_ADJS_INTERFACE a, OE_HEADERS_IFACE_ALL h
WHERE h.batch_id = p_batch_id
AND a.order_source_id = h.order_source_id
AND a.orig_sys_document_ref = h.orig_sys_document_ref
AND a.CHARGE_TYPE_CODE IS NOT NULL
AND NOT EXISTS (SELECT 'Y'FROM
fnd_lookup_values
WHERE LOOKUP_CODE = a.charge_type_code and
LANGUAGE = userenv('LANG') and
VIEW_APPLICATION_ID = 665 and
SECURITY_GROUP_ID = 0 and
LOOKUP_TYPE = 'FREIGHT_COST_TYPE'
);
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
h.request_id,decode(orig_sys_line_ref,NULL,'HEADER_ADJ','LINE_ADJ'),NULL ,NULL ,NULL
,NULL, a.order_source_id ,a.orig_sys_document_ref, a.orig_sys_line_ref ,NULL
,a.change_sequence ,NULL ,NULL ,NULL ,'LIST_HEADER_ID'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_PRICE_ADJS_INTERFACE a, OE_HEADERS_IFACE_ALL h
WHERE h.batch_id = p_batch_id
AND a.order_source_id = h.order_source_id
AND a.orig_sys_document_ref = h.orig_sys_document_ref
AND a.list_header_id IS NOT NULL
AND NOT EXISTS (SELECT 'Y'
FROM QP_LIST_HEADERS
WHERE LIST_HEADER_ID = a.list_header_id
);
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
h.request_id,decode(orig_sys_line_ref,NULL,'HEADER_ADJ','LINE_ADJ'),NULL ,NULL ,NULL
,NULL, a.order_source_id ,a.orig_sys_document_ref, a.orig_sys_line_ref ,NULL
,a.change_sequence ,NULL ,NULL ,NULL ,'LIST_LINE_ID'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660 ,NULL ,NULL ,NULL ,NULL ,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_PRICE_ADJS_INTERFACE a, OE_HEADERS_IFACE_ALL h
WHERE h.batch_id = p_batch_id
AND a.order_source_id = h.order_source_id
AND a.orig_sys_document_ref = h.orig_sys_document_ref
AND a.list_line_id IS NOT NULL
AND NOT EXISTS (SELECT 'Y'
FROM QP_LIST_LINES
WHERE LIST_LINE_ID = a.list_line_id
AND LIST_HEADER_ID = a.list_header_id
);
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id , 'HEADER',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,NULL
,NULL ,change_sequence,NULL,NULL,NULL,'END_CUSTOMER'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_HEADERS_IFACE_ALL b
WHERE batch_id = p_batch_id and
b.sold_to_org_id IS NOT NULL and
NOT EXISTS (select ORGANIZATION_ID
FROM OE_SOLD_TO_ORGS_V
WHERE ORGANIZATION_ID = b.sold_to_org_id
AND STATUS = 'A'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE))
;
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id
,line_id ,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,source_document_type_id ,source_document_id ,source_document_line_id
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login ,program_application_id ,program_id
,program_update_date ,process_activity ,notification_flag ,type
,message_source_code ,language ,message_text, transaction_id
)
SELECT
request_id ,'LINE',NULL,NULL,NULL,NULL
,order_source_id ,orig_sys_document_ref ,ORIG_SYS_LINE_REF
,orig_sys_shipment_ref ,change_sequence,NULL,NULL,NULL,'END_CUSTOMER_ID'
,sysdate ,FND_GLOBAL.USER_ID ,sysdate ,FND_GLOBAL.USER_ID
,FND_GLOBAL.CONC_LOGIN_ID ,660,NULL,NULL,NULL,NULL,NULL
,'C' ,USERENV('LANG') ,l_msg_text, OE_MSG_ID_S.NEXTVAL
FROM OE_LINES_IFACE_ALL b
WHERE (order_source_id, orig_sys_document_ref) IN
( SELECT order_source_id, orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL
WHERE batch_id = p_batch_id)
AND b.sold_to_org_id IS NOT NULL and
NOT EXISTS (select ORGANIZATION_ID
FROM OE_SOLD_TO_ORGS_V
WHERE ORGANIZATION_ID = b.sold_to_org_id
AND STATUS = 'A'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE));
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login
,program_application_id ,program_id,program_update_date
,process_activity ,notification_flag ,type
,message_source_code ,language
,message_text, transaction_id
)
SELECT
OE_BULK_ORDER_PVT.G_REQUEST_ID,'LINE' ,NULL,NULL ,NULL ,l.line_id
,l.order_source_id ,l.orig_sys_document_ref
,l.orig_sys_line_ref, l.orig_sys_shipment_ref, NULL
,NULL, sysdate, FND_GLOBAL.USER_ID ,sysdate
,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
,660 ,NULL ,NULL
,NULL ,NULL ,NULL
,'C' ,USERENV('LANG')
,l_msg_text || ' '|| l.ordered_item,
OE_MSG_ID_S.NEXTVAL
FROM oe_config_details_tmp L
WHERE NVL(L.item_type_code, 'XXX') <> 'MODEL'
AND NOT EXISTS (select L2.line_id
From oe_config_details_tmp L2
WHERE L2.component_code =
substr(L.component_code,1,instr(L.component_code,'-',-1,1)-1)
AND L2.top_model_line_id = L.top_model_line_id);
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login
,program_application_id ,program_id,program_update_date
,process_activity ,notification_flag ,type
,message_source_code ,language
,message_text, transaction_id
)
SELECT
OE_BULK_ORDER_PVT.G_REQUEST_ID ,'LINE' ,NULL,NULL ,NULL ,l.line_id
,l.order_source_id ,l.orig_sys_document_ref
,l.orig_sys_line_ref, l.orig_sys_shipment_ref, NULL
,NULL, sysdate, FND_GLOBAL.USER_ID ,sysdate
,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
,660 ,NULL ,NULL
,NULL ,NULL ,NULL
,'C' ,USERENV('LANG')
,l_msg_text ||' '||l.ordered_item || '( '||
l.low_quantity*L2.ordered_quantity
|| ' -> '|| l.high_quantity *L2.ordered_quantity || ')',
OE_MSG_ID_S.NEXTVAL
FROM oe_config_details_tmp L,
oe_config_details_tmp L2
WHERE L2.item_type_code = 'MODEL'
AND L.top_model_line_id = L2.line_id
AND L.ordered_quantity > 0
AND L2.ordered_quantity > 0
AND ( TRUNC(L.ordered_quantity/L2.ordered_quantity) < L.low_quantity OR
TRUNC(L.ordered_quantity/L2.ordered_quantity) > L.high_quantity );
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login
,program_application_id ,program_id,program_update_date
,process_activity ,notification_flag ,type
,message_source_code ,language
,message_text, transaction_id
)
SELECT
OE_BULK_ORDER_PVT.G_REQUEST_ID, 'LINE' ,NULL,NULL ,NULL ,l.line_id
,l.order_source_id ,l.orig_sys_document_ref
,l.orig_sys_line_ref, l.orig_sys_shipment_ref, NULL
,NULL, sysdate, FND_GLOBAL.USER_ID ,sysdate
,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
,660 ,NULL ,NULL
,NULL ,NULL ,NULL
,'C' ,USERENV('LANG')
,l_msg_text || ' '||l.ordered_item
,OE_MSG_ID_S.NEXTVAL
FROM oe_config_details_tmp L
WHERE L.BOM_ITEM_TYPE = 2
AND L.ordered_quantity > 0
AND NOT EXISTS ( -- fix bug 5687771
select 1
from oe_config_details_tmp L2
where L2.top_model_line_id = L.top_model_line_id
and substr(L2.component_code, 1, instr(l2.component_code,'-',-1)-1) =
L.component_code );
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login
,program_application_id ,program_id,program_update_date
,process_activity ,notification_flag ,type
,message_source_code ,language
,message_text, transaction_id
)
SELECT
OE_BULK_ORDER_PVT.G_REQUEST_ID,'LINE' ,NULL,NULL ,NULL ,l.line_id
,l.order_source_id ,l.orig_sys_document_ref
,l.orig_sys_line_ref, l.orig_sys_shipment_ref, NULL
,NULL, sysdate, FND_GLOBAL.USER_ID , sysdate
,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
,660 ,NULL ,NULL
,NULL ,NULL ,NULL
,'C' ,USERENV('LANG')
,l_msg_text || ' '||l.ordered_item
,OE_MSG_ID_S.NEXTVAL
FROM oe_config_details_tmp L
WHERE L.ITEM_TYPE_CODE in ('CLASS','MODEL')
AND L.mutually_exclusive_options = 1
AND 2 <= ( select count(line_id)
From oe_config_details_tmp L2
where --BUG 4586356 L2.item_type_code = 'OPTION'
L.component_code = substr(L2.component_code, 1,
instr(l2.component_code,'-',-1)-1)
and L2.top_model_line_id = L.top_model_line_id);
INSERT INTO OE_PROCESSING_MSGS
( request_id ,entity_code ,entity_ref ,entity_id ,header_id, line_id
,order_source_id ,original_sys_document_ref
,original_sys_document_line_ref ,orig_sys_shipment_ref ,change_sequence
,attribute_code ,creation_date ,created_by ,last_update_date
,last_updated_by ,last_update_login
,program_application_id ,program_id,program_update_date
,process_activity ,notification_flag ,type
,message_source_code ,language
,message_text, transaction_id
)
SELECT
OE_BULK_ORDER_PVT.G_REQUEST_ID,'LINE' ,NULL,NULL ,NULL ,l.line_id
,l.order_source_id ,l.orig_sys_document_ref
,l.orig_sys_line_ref, l.orig_sys_shipment_ref, NULL
,NULL, sysdate, FND_GLOBAL.USER_ID , sysdate
,FND_GLOBAL.USER_ID ,FND_GLOBAL.CONC_LOGIN_ID
,660 ,NULL ,NULL
,NULL ,NULL ,NULL
,'C' ,USERENV('LANG')
,l_msg_text || ' '||l.ordered_item
, OE_MSG_ID_S.NEXTVAL
FROM oe_config_details_tmp L
WHERE L.ITEM_TYPE_CODE = 'MODEL'
AND EXISTS (select b2.component_code
From bom_explosions b2
Where b2.explosion_type = 'OPTIONAL'
AND b2.top_bill_sequence_id = L.top_bill_sequence_id
AND b2.plan_level >= 0
AND b2.effectivity_date <= sysdate
AND b2.disable_date > sysdate
AND b2.bom_item_type IN ( 1, 2 ) -- Model, Class
AND b2.optional = 2
AND b2.component_code NOT IN (
select component_code
From oe_config_details_tmp L2
Where L.top_model_line_id =
L2.top_model_line_id)
AND
SUBSTR(B2.COMPONENT_CODE,1,INSTR(B2.COMPONENT_CODE,'-',-1,1)-1)
IN ( select component_code
From oe_config_details_tmp L3
Where L.top_model_line_id =
L3.top_model_line_id) );
UPDATE OE_LINES_IFACE_ALL
SET ERROR_FLAG = 'Y'
,ATTRIBUTE_STATUS = NULL
WHERE (order_source_id, orig_sys_document_ref,orig_sys_line_ref) in
(SELECT a.order_source_id, a.orig_sys_document_ref,t.orig_sys_line_ref
FROM OE_HEADERS_IFACE_ALL a,OE_CONFIG_DETAILS_TMP t
where a.batch_id=p_batch_id
AND a.orig_sys_document_ref=t.orig_sys_document_ref
AND a.order_source_id=t.order_source_id
AND t.orig_sys_line_ref is not NULL
AND nvl(t.lock_control, 0) = -99
AND a.request_id = oe_bulk_order_pvt.g_request_id);
UPDATE OE_HEADERS_IFACE_ALL h
SET ERROR_FLAG = 'Y'
,ATTRIBUTE_STATUS = NULL
WHERE batch_id = p_batch_id
AND nvl(ERROR_FLAG, 'N') = 'N'
AND EXISTS
(SELECT 'Y'
FROM OE_CONFIG_DETAILS_TMP t
WHERE t.orig_sys_document_ref = h.ORIG_SYS_DOCUMENT_REF
AND t.order_source_id = h.order_source_id
AND nvl(t.lock_control, 0) = -99);
UPDATE oe_lines_iface_all a
SET error_flag = 'Y'
,attribute_status = NULL
where nvl(error_flag, 'N') = 'N'
AND EXISTS
(SELECT original_sys_document_line_ref
FROM oe_processing_msgs b
WHERE b.original_sys_document_ref = a.orig_sys_document_ref
AND b.original_sys_document_line_ref = a.orig_sys_line_ref
AND b.order_source_id = a.order_source_id
AND b.request_id = oe_bulk_order_pvt.g_request_id);
UPDATE oe_lines_iface_all a
SET error_flag = 'Y',
attribute_status = NULL
WHERE nvl(error_flag, 'N') = 'N'
AND (order_source_id,orig_sys_document_ref,top_model_line_ref) IN
(select order_source_id,orig_sys_document_ref,top_model_line_ref
from oe_lines_iface_all
where nvl(error_flag, 'N') = 'Y'
AND top_model_line_ref is not null);
UPDATE oe_headers_iface_all a
SET error_flag = 'Y'
,attribute_status = NULL
WHERE batch_id = p_batch_id
AND nvl(error_flag,'N') = 'N'
AND EXISTS
(SELECT original_sys_document_ref
from oe_processing_msgs b
WHERE b.original_sys_document_ref = a.orig_sys_document_ref
AND b.order_source_id = a.order_source_id
and b.original_sys_document_line_ref is NULL
AND b.request_id = oe_bulk_order_pvt.g_request_id);
UPDATE oe_headers_iface_all a
SET error_flag = 'Y'
,attribute_status = NULL
WHERE batch_id = p_batch_id
AND nvl(error_flag,'N') = 'N'
AND NOT EXISTS
(SELECT 1
FROM oe_lines_iface_all b
WHERE b.orig_sys_document_ref = a.orig_sys_document_ref
AND b.order_source_id = a.order_source_id
AND nvl(error_flag, 'N') = 'N'
AND b.request_id = oe_bulk_order_pvt.g_request_id);
UPDATE OE_HEADERS_IFACE_ALL a
SET ERROR_FLAG = 'Y'
,ATTRIBUTE_STATUS = NULL
WHERE batch_id = p_batch_id
AND nvl(ERROR_FLAG, 'N') = 'N'
AND EXISTS
(SELECT original_sys_document_line_ref
FROM OE_PROCESSING_MSGS b
WHERE b.original_sys_document_ref = a.ORIG_SYS_DOCUMENT_REF
AND b.order_source_id = a.order_source_id
AND b.request_id = OE_BULK_ORDER_PVT.G_REQUEST_ID); -- Added for Bug 6671781
UPDATE OE_LINES_IFACE_ALL c
SET ATTRIBUTE_STATUS = NULL
WHERE (order_source_id, orig_sys_document_ref) IN
(SELECT a.order_source_id, a.orig_sys_document_ref
FROM OE_HEADERS_IFACE_ALL a, OE_PROCESSING_MSGS b
WHERE a.batch_id = p_batch_id
AND b.original_sys_document_ref = a.ORIG_SYS_DOCUMENT_REF
AND b.order_source_id = a.order_source_id
AND b.request_id = OE_BULK_ORDER_PVT.G_REQUEST_ID);--Added for bug 6830039