[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
update the dependency table with an error for that validationType
a null could be passed in to the validation type and then no dependency check
will be done
===========================================================================*/
-- added grouping info for bug 4198330
PROCEDURE app_error (x_ExceptionLevel IN VARCHAR2,
x_MessageName IN VARCHAR2,
x_ChildMessageName IN VARCHAR2,
x_InterfaceHeaderId IN NUMBER,
x_InterfaceLineId IN NUMBER,
x_ScheduleHeaderId IN NUMBER,
x_ScheduleLineId IN NUMBER,
x_OrderHeaderId IN NUMBER,
x_OrderLineId IN NUMBER,
x_ErrorText IN VARCHAR2,
x_ValidationType IN VARCHAR2,
x_GroupInfo IN BOOLEAN,
x_ShipfromOrgId IN NUMBER,
x_ShipToAddressId IN NUMBER,
x_CustomerItemId IN NUMBER,
x_InventoryItemId IN NUMBER,
x_token1 IN VARCHAR2,
x_value1 IN VARCHAR2,
x_token2 IN VARCHAR2,
x_value2 IN VARCHAR2,
x_token3 IN VARCHAR2,
x_value3 IN VARCHAR2,
x_token4 IN VARCHAR2,
x_value4 IN VARCHAR2,
x_token5 IN VARCHAR2,
x_value5 IN VARCHAR2,
x_token6 IN VARCHAR2,
x_value6 IN VARCHAR2,
x_token7 IN VARCHAR2, -- Bug 4297984
x_value7 IN VARCHAR2,
x_token8 IN VARCHAR2,
x_value8 IN VARCHAR2,
x_token9 IN VARCHAR2,
x_value9 IN VARCHAR2,
x_token10 IN VARCHAR2,
x_value10 IN VARCHAR2)
IS
x_text VARCHAR2(2000) := NULL;
rlm_message_sv.insert_purge_row (x_ExceptionLevel =>v_message_rec.exception_level,
x_MessageName =>v_message_rec.message_name,
x_ErrorText =>v_message_rec.error_text,
x_ScheduleHeaderId =>v_message_rec.schedule_header_id,
x_ScheduleLineId =>v_message_rec.schedule_line_id,
x_OrderHeaderId =>v_message_rec.order_header_id,
x_OrderLineId => v_message_rec.order_line_id,
x_ScheduleLineNum => v_message_rec.Schedule_line_number, --bugfix 6319027
x_conc_req_id =>v_conc_req ,
x_PurgeStatus =>v_purge ,
x_PurgeExp_rec=>v_purge_rec );
PROCEDURE NAME: insert_row
===========================================================================*/
PROCEDURE insert_row (
x_ExceptionLevel IN VARCHAR2,
x_MessageName IN VARCHAR2,
x_ErrorText IN VARCHAR2,
x_InterfaceHeaderId IN NUMBER,
x_InterfaceLineId IN NUMBER,
x_ScheduleHeaderId IN NUMBER,
x_ScheduleLineId IN NUMBER,
x_OrderHeaderId IN NUMBER,
x_OrderLineId IN NUMBER,
x_GroupInfo IN BOOLEAN,
x_user_id IN NUMBER,
x_conc_req_id IN NUMBER,
x_prog_appl_id IN NUMBER,
x_conc_program_id IN NUMBER,
x_PurgeStatus IN VARCHAR2
)
IS
--
v_MessageText VARCHAR2(5000) := NULL;
SELECT ece_tp_location_code
FROM hz_cust_acct_sites_all acct_site,
rlm_interface_lines_all lines
WHERE lines.ship_to_address_id = acct_site.cust_acct_site_id
AND lines.line_id = x_InterfaceLineId;
SELECT ece_tp_location_code
FROM hz_cust_acct_sites_all acct_site,
rlm_interface_lines_all lines
WHERE lines.bill_to_address_id = acct_site.cust_acct_site_id
AND lines.line_id = x_InterfaceLineId;
SELECT ece_tp_location_code
FROM hz_cust_acct_sites_all acct_site,
rlm_interface_lines_all lines
WHERE lines.intrmd_ship_to_id = acct_site.cust_acct_site_id
AND lines.line_id = x_InterfaceLineId;
SELECT PARTY.PARTY_NAME customer_name,
rih.ECE_TP_TRANSLATOR_CODE ,
rih.ECE_TP_LOCATION_CODE_EXT ,
rih.EDI_CONTROL_NUM_3 ,
rih.EDI_TEST_INDICATOR ,
rih.SCHED_GENERATION_DATE ,
rih.SCHEDULE_REFERENCE_NUM ,
rih.SCHEDULE_SOURCE ,
rih.SCHEDULE_TYPE ,
rih.SCHEDULE_PURPOSE ,
rih.SCHED_HORIZON_START_DATE ,
rih.SCHED_HORIZON_END_DATE ,
ril.CUST_SHIP_FROM_ORG_EXT ,
ril.LINE_NUMBER ,
ril.SCHEDULE_ITEM_NUM ,
mtl.customer_item_number ,
ril.ITEM_DESCRIPTION_EXT ,
ril.CUST_UOM_EXT ,
ril.SUPPLIER_ITEM_EXT ,
ril.ITEM_DETAIL_TYPE ,
ril.ITEM_DETAIL_SUBTYPE ,
ril.ITEM_DETAIL_QUANTITY ,
ril.START_DATE_TIME ,
ril.CUSTOMER_JOB ,
ril.CUST_MODEL_SERIAL_NUMBER ,
ril.CUST_PRODUCTION_SEQ_NUM ,
ril.DATE_TYPE_CODE ,
ril.QTY_TYPE_CODE ,
ril.LINE_NUMBER ,
ril.REQUEST_DATE ,
ril.SCHEDULE_DATE ,
ril.CUST_PO_NUMBER ,
ril.INDUSTRY_ATTRIBUTE1 ,
ril.CUST_PRODUCTION_LINE ,
ril.CUSTOMER_DOCK_CODE ,
ril.SCHEDULE_LINE_ID
FROM rlm_interface_headers rih,
rlm_interface_lines_all ril,
HZ_PARTIES PARTY,
HZ_CUST_ACCOUNTS CUST_ACCT,
mtl_customer_items mtl
WHERE rih.ORG_ID = ril.ORG_ID
AND rih.header_id = x_InterfaceHeaderId
AND ril.line_id = x_InterfaceLineId
AND rih.header_id = ril.header_id
And CUST_ACCT.PARTY_ID = PARTY.PARTY_ID (+)
AND rih.customer_id = CUST_ACCT.PARTY_ID (+)
AND ril.customer_item_id = mtl.customer_item_id (+);
SELECT PARTY.PARTY_NAME customer_name,
rih.ECE_TP_TRANSLATOR_CODE ,
rih.ECE_TP_LOCATION_CODE_EXT ,
rih.EDI_CONTROL_NUM_3 ,
rih.EDI_TEST_INDICATOR ,
rih.SCHED_GENERATION_DATE ,
rih.SCHEDULE_REFERENCE_NUM ,
rih.SCHEDULE_SOURCE ,
rih.SCHEDULE_TYPE ,
rih.SCHEDULE_PURPOSE ,
rih.SCHED_HORIZON_START_DATE ,
rih.SCHED_HORIZON_END_DATE ,
NULL,--ril.CUST_SHIP_FROM_ORG_EXT ,
NULL,--ril.LINE_NUMBER ,
NULL,--ril.SCHEDULE_ITEM_NUM ,
NULL,--mtl.customer_item_number ,
NULL,--ril.ITEM_DESCRIPTION_EXT ,
NULL,--ril.CUST_UOM_EXT ,
NULL,--ril.SUPPLIER_ITEM_EXT ,
NULL,--ril.ITEM_DETAIL_TYPE ,
NULL,--ril.ITEM_DETAIL_SUBTYPE ,
NULL,--ril.ITEM_DETAIL_QUANTITY ,
NULL,--ril.START_DATE_TIME ,
NULL,--ril.CUSTOMER_JOB ,
NULL,--ril.CUST_MODEL_SERIAL_NUMBER ,
NULL,--ril.CUST_PRODUCTION_SEQ_NUM ,
NULL,--ril.DATE_TYPE_CODE ,
NULL,--ril.QTY_TYPE_CODE ,
NULL,--ril.LINE_NUMBER ,
NULL,--ril.REQUEST_DATE ,
NULL,--ril.SCHEDULE_DATE ,
NULL,--ril.CUST_PO_NUMBER ,
NULL,--ril.INDUSTRY_ATTRIBUTE1 ,
NULL,--ril.CUST_PRODUCTION_LINE ,
NULL,--ril.CUSTOMER_DOCK_CODE ,
NULL --ril.SCHEDULE_LINE_ID
FROM rlm_interface_headers rih,
HZ_PARTIES PARTY,
HZ_CUST_ACCOUNTS CUST_ACCT
WHERE rih.header_id = x_InterfaceHeaderId
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID (+)
AND rih.customer_id = cust_acct.cust_account_id (+);
SELECT PARTY.PARTY_NAME customer_name,
rih.ECE_TP_TRANSLATOR_CODE ,
rih.ECE_TP_LOCATION_CODE_EXT ,
rih.EDI_CONTROL_NUM_3 ,
rih.EDI_TEST_INDICATOR ,
rih.SCHED_GENERATION_DATE ,
rih.SCHEDULE_REFERENCE_NUM ,
rih.SCHEDULE_SOURCE ,
rih.SCHEDULE_TYPE ,
rih.SCHEDULE_PURPOSE ,
rih.SCHED_HORIZON_START_DATE ,
rih.SCHED_HORIZON_END_DATE ,
ril.CUST_SHIP_FROM_ORG_EXT ,
NULL, --ril.LINE_NUMBER ,
ril.SCHEDULE_ITEM_NUM ,
mtl.customer_item_number ,
ril.ITEM_DESCRIPTION_EXT ,
NULL, --ril.CUST_UOM_EXT ,
ril.SUPPLIER_ITEM_EXT ,
NULL, --ril.ITEM_DETAIL_TYPE ,
NULL, --ril.ITEM_DETAIL_SUBTYPE ,
NULL, --ril.ITEM_DETAIL_QUANTITY ,
NULL, --ril.START_DATE_TIME ,
NULL, --ril.CUSTOMER_JOB ,
NULL, --ril.CUST_MODEL_SERIAL_NUMBER ,
NULL, --ril.CUST_PRODUCTION_SEQ_NUM ,
NULL, --ril.DATE_TYPE_CODE ,
NULL, --ril.QTY_TYPE_CODE ,
NULL, --ril.LINE_NUMBER ,
NULL, --ril.REQUEST_DATE ,
NULL, --ril.SCHEDULE_DATE ,
NULL, --ril.CUST_PO_NUMBER ,
NULL, --ril.INDUSTRY_ATTRIBUTE1 ,
NULL, --ril.CUST_PRODUCTION_LINE ,
NULL, --ril.CUSTOMER_DOCK_CODE ,
NULL --ril.SCHEDULE_LINE_ID
FROM rlm_interface_headers rih,
rlm_interface_lines_all ril,
HZ_PARTIES PARTY,
HZ_CUST_ACCOUNTS CUST_ACCT,
mtl_customer_items mtl
WHERE rih.ORG_ID = ril.ORG_ID
AND rih.header_id = x_InterfaceHeaderId
AND ril.line_id = x_InterfaceLineId
AND rih.header_id = ril.header_id
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID (+)
AND rih.customer_id = CUST_ACCT.PARTY_ID (+)
AND ril.customer_item_id = mtl.customer_item_id (+);
SELECT rlm_demand_exceptions_s.nextval
INTO v_ExceptionId
FROM sys.dual;
** Program update date should be populated
** if called from a concurrent program.
*/
--
IF (fnd_global.conc_request_id IS NOT NULL) THEN
v_ProgramDate := sysdate;
** Select record.
*/
v_progress := '015';
** Insert record.
*/
--
v_progress := '020';
SELECT a.item_number
INTO v_inv_item
FROM mtl_item_flexfields a,
mtl_customer_item_xrefs b
WHERE a.inventory_item_id = b.inventory_item_id
AND a.organization_id = b.master_organization_id
AND b.preference_number =1
AND b.customer_item_id IN (
SELECT customer_item_id
FROM rlm_interface_lines
WHERE line_id = x_InterfaceLineId
);
INSERT INTO RLM_DEMAND_EXCEPTIONS
(
exception_id,
exception_level,
message_name,
message_text,
interface_header_id,
interface_line_id,
schedule_header_id,
schedule_line_id,
order_header_id,
order_line_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
CUST_NAME_EXT,
CUST_SHIP_TO_EXT,
CUST_BILL_TO_EXT,
CUST_INTERMD_SHIPTO_EXT,
ECE_TP_TRANSLATOR_CODE,
ECE_TP_LOCATION_CODE_EXT,
EDI_CONTROL_NUM_3,
EDI_TEST_INDICATOR,
SCHED_GENERATION_DATE,
SCHEDULE_REFERENCE_NUM,
SCHEDULE_SOURCE,
SCHEDULE_TYPE,
SCHEDULE_PURPOSE,
HORIZON_START_DATE,
HORIZON_END_DATE,
CUST_SHIP_FROM_ORG_EXT,
SCHEDULE_LINE_NUMBER,
SCHEDULE_ITEM_NUM,
CUSTOMER_ITEM_EXT,
CUST_ITEM_DESCRIPTION,
CUST_UOM_EXT,
INVENTORY_ITEM,
ITEM_DETAIL_TYPE,
ITEM_DETAIL_SUBTYPE,
ITEM_DETAIL_QUANTITY,
START_DATE_TIME,
CUST_JOB_NUMBER,
CUST_MODEL_SERIAL_NUM,
CUSTOMER_PROD_SEQ_NUM,
DATE_TYPE_CODE,
QTY_TYPE_CODE,
REQUEST_DATE,
SCHEDULE_DATE,
CUST_PO_NUMBER,
INDUSTRY_ATTRIBUTE1,
CUST_PRODUCTION_LINE,
CUSTOMER_DOCK_CODE,
PURGE_STATUS
)
VALUES
(
v_ExceptionId,
x_ExceptionLevel,
x_MessageName,
SUBSTR(v_MessageText,1,2000),
x_InterfaceHeaderId,
x_InterfaceLineId,
x_ScheduleHeaderId,
x_ScheduleLineId,
x_OrderHeaderId,
x_OrderLineId,
sysdate,
nvl(x_user_id,fnd_global.user_id),
sysdate,
fnd_global.user_id,
v_LoginId,
x_conc_req_id,
x_prog_appl_id,
x_conc_program_id,
v_ProgramDate,
v_Exception_rec.CUST_NAME_EXT,
v_shipTo,
v_BillTo,
v_IntrmdShipTo,
v_Exception_rec.ECE_TP_TRANSLATOR_CODE,
v_Exception_rec.ECE_TP_LOCATION_CODE_EXT,
v_Exception_rec.EDI_CONTROL_NUM_3,
v_Exception_rec.EDI_TEST_INDICATOR,
v_Exception_rec.SCHED_GENERATION_DATE,
v_Exception_rec.SCHEDULE_REFERENCE_NUM,
v_Exception_rec.SCHEDULE_SOURCE,
v_Exception_rec.SCHEDULE_TYPE,
v_Exception_rec.SCHEDULE_PURPOSE,
v_Exception_rec.HORIZON_START_DATE,
v_Exception_rec.HORIZON_END_DATE,
v_Exception_rec.CUST_SHIP_FROM_ORG_EXT,
v_Exception_rec.SCHEDULE_LINE_NUMBER,
v_Exception_rec.SCHEDULE_ITEM_NUM,
v_Exception_rec.CUSTOMER_ITEM_EXT,
v_Exception_rec.CUST_ITEM_DESCRIPTION,
v_Exception_rec.CUST_UOM_EXT,
v_inv_item,
v_Exception_rec.ITEM_DETAIL_TYPE,
v_Exception_rec.ITEM_DETAIL_SUBTYPE,
v_Exception_rec.ITEM_DETAIL_QUANTITY,
v_Exception_rec.START_DATE_TIME,
v_Exception_rec.CUST_JOB_NUMBER,
v_Exception_rec.CUST_MODEL_SERIAL_NUM,
v_Exception_rec.CUSTOMER_PROD_SEQ_NUM,
v_Exception_rec.DATE_TYPE_CODE,
v_Exception_rec.QTY_TYPE_CODE,
v_Exception_rec.REQUEST_DATE,
v_Exception_rec.SCHEDULE_DATE,
v_Exception_rec.CUST_PO_NUMBER,
v_Exception_rec.INDUSTRY_ATTRIBUTE1,
v_Exception_rec.CUST_PRODUCTION_LINE,
v_Exception_rec.CUSTOMER_DOCK_CODE,
x_PurgeStatus
);
sql_error ('rlm_message_sv.insert_row', v_progress);
END insert_row;
PROCEDURE NAME: insert_purge_row
===========================================================================*/
PROCEDURE insert_purge_row (
x_ExceptionLevel IN VARCHAR2,
x_MessageName IN VARCHAR2,
x_ErrorText IN VARCHAR2,
x_InterfaceHeaderId IN NUMBER,
x_InterfaceLineId IN NUMBER,
x_ScheduleHeaderId IN NUMBER,
x_ScheduleLineId IN NUMBER,
x_OrderHeaderId IN NUMBER,
x_OrderLineId IN NUMBER,
x_ScheduleLineNum IN NUMBER, --bugfix 6319027
x_user_id IN NUMBER,
x_conc_req_id IN NUMBER,
x_prog_appl_id IN NUMBER,
x_conc_program_id IN NUMBER,
x_PurgeStatus IN VARCHAR2,
x_PurgeExp_rec IN t_PurExp_rec
)
IS
v_MessageText VARCHAR2(5000) := NULL;
SELECT rlm_demand_exceptions_s.nextval
INTO v_ExceptionId
FROM sys.dual;
** Program update date should be populated
** if called from a concurrent program.
*/
IF (fnd_global.conc_request_id IS NOT NULL) THEN
v_ProgramDate := sysdate;
** Select record.
*/
v_progress := '015';
** Insert record.
*/
v_progress := '020';
INSERT INTO RLM_DEMAND_EXCEPTIONS(
exception_id,
exception_level,
message_name,
message_text,
interface_header_id,
interface_line_id,
schedule_header_id,
schedule_line_id,
order_header_id,
order_line_id,
schedule_line_number, --bugfix 6319027
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
ECE_TP_TRANSLATOR_CODE,
SCHEDULE_REFERENCE_NUM,
SCHEDULE_TYPE,
SCHED_GENERATION_DATE,
ORIGIN_TABLE, /*2261812*/
PURGE_STATUS
)
VALUES (
v_ExceptionId,
x_ExceptionLevel,
x_MessageName,
substr(v_MessageText,1,2000),
x_InterfaceHeaderId,
x_InterfaceLineId,
x_ScheduleHeaderId,
x_ScheduleLineId,
x_OrderHeaderId,
x_OrderLineId,
x_ScheduleLineNum, --bugfix 6319027
sysdate,
nvl(x_user_id,fnd_global.user_id),
sysdate,
fnd_global.user_id,
v_LoginId,
x_conc_req_id,
x_prog_appl_id,
x_conc_program_id,
v_ProgramDate,
x_PurgeExp_rec.ECE_TP_TRANSLATOR_CODE,
x_PurgeExp_rec.SCHEDULE_REFERENCE_NUM,
x_PurgeExp_rec.SCHEDULE_TYPE,
x_PurgeExp_rec.SCHED_GENERATION_DATE,
x_PurgeExp_rec.ORIGIN_TABLE, /* 2261812*/
x_PurgeStatus);
sql_error ('rlm_message_sv.insert_purge_row', v_progress);
END insert_purge_row;
g_message_tab.DELETE;
insert_row (g_message_tab(i).exception_level,
NVL(g_message_tab(i).child_message_name,g_message_tab(i).message_name),
g_message_tab(i).error_text,
g_message_tab(i).interface_header_id,
g_message_tab(i).interface_line_id,
g_message_tab(i).schedule_header_id,
g_message_tab(i).schedule_line_id,
g_message_tab(i).order_header_id,
g_message_tab(i).order_line_id,
g_message_tab(i).group_Info,
v_user_id,
get_conc_req_id,
v_prog_appl_id,
v_conc_program_id);
g_message_tab.delete; -- BugFix #4147550
insert_row (g_message_tab(i).exception_level,
NVL(g_message_tab(i).child_message_name,g_message_tab(i).message_name),
g_message_tab(i).error_text,
NVL(g_message_tab(i).interface_header_id,x_header_id),
g_message_tab(i).interface_line_id,
g_message_tab(i).schedule_header_id,
g_message_tab(i).schedule_line_id,
g_message_tab(i).order_header_id,
g_message_tab(i).order_line_id,
g_message_tab(i).group_Info,
v_user_id,
get_conc_req_id,
v_prog_appl_id,
v_conc_program_id);
g_message_tab.delete; -- BugFix #4147550
g_message_tab.DELETE(i);
g_message_tab.DELETE(i);