[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_STATUS_FLAG ( p_msg_id IN NUMBER,
p_hdr_id IN NUMBER,
p_flag IN VARCHAR2,
p_err_msg IN VARCHAR2,
p_group_id IN NUMBER) AS
BEGIN
IF (g_debug_level <= 2) THEN
cln_debug_pub.Add('-------- Entering procedure UPDATE_STATUS_FLAG --------',2);
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag = p_flag, error_message = p_err_msg,group_id = p_group_id
WHERE hdr_id = p_hdr_id
AND msg_id = p_msg_id;
cln_debug_pub.Add('-------- Exiting procedure UPDATE_STATUS_FLAG --------',2);
cln_debug_pub.Add('-------- Exception in procedure UPDATE_STATUS_FLAG --------',5);
END UPDATE_STATUS_FLAG;
SELECT WIP_ENTITY_ID
INTO x_wip_entity_id
FROM WIP_ENTITIES
WHERE WIP_ENTITY_NAME = p_job_name
AND ORGANIZATION_ID = p_org_id;
SELECT PRIMARY_ITEM_ID,WIP_ENTITY_ID,COMMON_BOM_SEQUENCE_ID,COMMON_ROUTING_SEQUENCE_ID,
BOM_REVISION,ROUTING_REVISION,BOM_REVISION_DATE,ALTERNATE_BOM_DESIGNATOR,
ALTERNATE_ROUTING_DESIGNATOR,COMPLETION_SUBINVENTORY,COMPLETION_LOCATOR_ID,ROUTING_REVISION_DATE
INTO x_inventory_item_id,x_wip_entity_id,x_common_bom_seq_id, x_common_rout_seq_id,
x_bom_rev,x_rout_rev,x_bom_rev_date,x_alt_bom,x_alt_rout,x_comp_sub_inventory,
x_comp_locator_id,x_rout_rev_date
FROM WIP_DISCRETE_JOBS
WHERE wip_entity_id = x_wip_entity_id;
SELECT party_id,party_site_id
INTO x_party_id,x_party_site_id
FROM ecx_tp_headers
WHERE tp_header_id = p_tp_hdr_id;
SELECT standard_operation_id,department_id,operation_Sequence_id
INTO x_fm_std_op_id,x_fm_dept_id,x_fm_op_seq_id
FROM BOM_OPERATION_SEQUENCES
WHERE routing_sequence_id = p_rout_seq_id
AND operation_seq_num = p_prev_op_seq_num;
SELECT standard_operation_id,department_id,operation_Sequence_id
INTO x_to_std_op_id,x_to_dept_id,x_to_op_seq_id
FROM BOM_OPERATION_SEQUENCES
WHERE routing_sequence_id = p_rout_seq_id
AND operation_seq_num = p_op_seq_num;
SELECT inventory_item_id
INTO x_prev_inventory_item_id
FROM mtl_system_items_kfv
WHERE concatenated_segments = p_start_lot_item
AND organization_id = p_org_id
AND inventory_item_status_code = 'Active';
SELECT inventory_item_id
INTO x_inventory_item_id
FROM mtl_system_items_kfv
WHERE concatenated_segments = p_prim_lot_item
AND organization_id = p_org_id
AND inventory_item_status_code = 'Active';
x_process_type := 'JOB_UPDATE';
x_process_type := 'STATUS_UPDATE';
SELECT max(operation_seq_num)
INTO x_op_seq_num
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id
AND ((quantity_in_queue <> 0 OR quantity_running <> 0 OR quantity_waiting_to_move <> 0 ) OR
( quantity_in_queue = 0 AND quantity_running = 0 AND quantity_waiting_to_move = 0
AND quantity_scrapped = quantity_completed AND quantity_completed > 0 )
); -- this picks up te max op seq, if only scraps at ops
SELECT quantity_in_queue,quantity_running,quantity_waiting_to_move
INTO l_qty_Q,l_qty_RUN,l_qty_TM
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = x_op_seq_num;
SELECT wip_entity_id
INTO x_prev_wip_entity_id
FROM WIP_ENTITIES
WHERE wip_entity_name = p_lot_number
AND ORGANIZATION_ID = p_org_id;
PROCEDURE UPDATE_COLL_HIST ( p_int_ctrl_num IN NUMBER,
p_coll_hist_msg IN VARCHAR2,
x_resultout OUT NOCOPY VARCHAR2) AS
l_update_cln_parameter_list wf_parameter_list_t;
cln_debug_pub.Add('Entering UPDATE_COLL_HIST procedure with parameters----', 2);
l_update_cln_parameter_list := wf_parameter_list_t();
WF_EVENT.AddParameterToList('MESSAGE_TEXT', p_coll_hist_msg, l_update_cln_parameter_list);
WF_EVENT.AddParameterToList('DOCUMENT_NO',l_doc_number,l_update_cln_parameter_list);
WF_EVENT.AddParameterToList('COLLABORATION_POINT','APPS',l_update_cln_parameter_list);
WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',p_int_ctrl_num,l_update_cln_parameter_list);
cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
SELECT M4R_7B1_OSFM_S1.NEXTVAL
INTO l_event_key
FROM DUAL;
g_exception_tracking_msg := 'Raising oracle.apps.cln.ch.collaboration.update event ';
wf_event.raise(p_event_name => 'oracle.apps.cln.ch.collaboration.update',
p_event_key => '7B1:' || l_event_key,
p_parameters => l_update_cln_parameter_list);
cln_debug_pub.Add('----------- EXITING UPDATE_COLL_HIST ------------', 2);
cln_debug_pub.Add('------- Exception in procedure UPDATE_COLL_HIST --------',5);
END UPDATE_COLL_HIST;
SELECT wsm_lot_move_txn_interface_s.NEXTVAL
INTO l_header_id
FROM DUAL;
SELECT wip_interface_s.NEXTVAL
INTO l_txn_id
FROM DUAL;
SELECT wip_interface_s.NEXTVAL
INTO l_group_id
FROM DUAL;
SELECT to_op_seq_id
INTO l_bon_to_op_seq_id
FROM BOM_OPERATION_NETWORKS
WHERE from_op_seq_id = l_fm_op_seq_id
AND to_op_seq_id = l_to_op_seq_id; -- added to consider the ALTERNATE path in the
SELECT operation_code
INTO l_op_code
FROM wsm_operation_details_v
WHERE standard_operation_id = l_to_std_op_id
AND organization_id = p_org_id;
SAVEPOINT before_insert;
g_exception_tracking_msg := 'Inserting values into WSM_LOT_MOVE_TXN_INTERFACE';
INSERT
INTO WSM_LOT_MOVE_TXN_INTERFACE ( HEADER_ID,
TRANSACTION_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
SOURCE_LINE_ID,
STATUS,
TRANSACTION_TYPE,
ORGANIZATION_ID,
WIP_ENTITY_ID,
WIP_ENTITY_NAME,
ENTITY_TYPE,
PRIMARY_ITEM_ID,
TRANSACTION_DATE,
FM_OPERATION_SEQ_NUM,
FM_DEPARTMENT_ID,
FM_INTRAOPERATION_STEP_TYPE,
TO_OPERATION_SEQ_NUM,
TO_OPERATION_CODE,
TO_DEPARTMENT_ID,
TO_INTRAOPERATION_STEP_TYPE,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
PRIMARY_UOM,
SCRAP_ACCOUNT_ID,
SCRAP_QUANTITY,
SCRAP_AT_OPERATION_FLAG,
REASON_ID,
JUMP_FLAG)
VALUES ( l_header_id,
l_txn_id,
l_group_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
NULL,
NULL,
NULL,
NULL,
1, -- status (PENDING)
decode(p_process_type,'JOB_MOVE',1,'JOB_SCRAP',1,'JOB_COMPLETION',2,'JOB_UNDO',4),
-- trx type 1 IS ACTUALLY "MOVE", '3' is move n return
p_org_id,
l_wip_entity_id,
l_wip_entity_name,
5, -- entity type
l_inventory_item_id,
p_hdr_rec.transaction_date,
decode(p_process_type,'JOB_UNDO',NULL,l_fm_op_seq_num), --FM_OPERATION_SEQ_NUM
decode(p_process_type,'JOB_UNDO',NULL,l_fm_dept_id),
decode(p_process_type,'JOB_UNDO',NULL,l_fm_intra_op_step), -- FM_INTRAOPERATION_STEP_TYPE 1= QUEUE,3 = TO MOVE ;
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'T',NULL,NULL);
SELECT custom_valid_status,error_message
INTO l_custom_valid_pass,l_custom_valid_err_msg
FROM M4R_WSM_DWIP_HDR_STAGING
WHERE msg_id = p_hdr_rec.msg_id
AND hdr_id = p_hdr_rec.hdr_id;
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_custom_valid_err_msg,NULL);
ROLLBACK TO before_insert;
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'R',NULL,NULL);
FOR i IN (SELECT message
INTO l_err_msg1
FROM WSM_INTERFACE_ERRORS
WHERE transaction_id = l_txn_id)
LOOP
IF (g_debug_level <= 1) THEN
cln_debug_pub.Add('Loop error : ' || i.message, 1);
/*SELECT MESSAGE
INTO l_err_msg1
FROM WSM_INTERFACE_ERRORS
WHERE transaction_id = l_txn_id;
SELECT PROCESS_STATUS,ERROR_MSG
INTO l_interface_status,l_err_msg2
FROM WSM_LOT_JOB_INTERFACE
WHERE header_id = l_header_id;
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'E',l_err_msg1 || l_err_msg2,l_group_id);
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'S',NULL,l_group_id);
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,NULL);
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,NULL);
SELECT wsm_lot_sm_ifc_header_s.NEXTVAL
INTO l_header_id
FROM DUAL;
SELECT wsm_lot_job_interface_s.NEXTVAL
INTO l_group_id
FROM DUAL;
ELSE --l_process_type <> 'STATUS_UPDATE'
l_lot_class_code := p_qty_rec.lot_classification_code;
SELECT COMPLETION_SUBINVENTORY,COMPLETION_LOCATOR_ID
INTO l_comp_sub_inventory,l_comp_locator_id
FROM BOM_OPERATIONAL_ROUTINGS
WHERE assembly_item_id = l_prev_inventory_item_id
AND organization_id = p_org_id
AND ((ALTERNATE_ROUTING_DESIGNATOR = p_hdr_rec.alt_routing_designator) OR (ALTERNATE_ROUTING_DESIGNATOR IS NULL));
SELECT wsm_split_merge_transactions_s.NEXTVAL
INTO l_trx_id
FROM DUAL;
SELECT wsm_split_merge_transactions_s.NEXTVAL
INTO l_source_line_id
FROM DUAL;
SAVEPOINT before_insert;
INSERT
INTO WSM_STARTING_LOTS_INTERFACE ( HEADER_ID,
TRANSACTION_ID,
LOT_NUMBER,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
QUANTITY,
SUBINVENTORY_CODE,
LOCATOR_ID,
REVISION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
COMPONENT_ISSUE_QUANTITY)-- Added to fix an issue in Bug #4727381
VALUES ( l_source_line_id,
l_trx_id,
p_hdr_rec.prev_lot_number,
l_prev_inventory_item_id,
p_org_id,
p_hdr_rec.prev_lot_qty,
l_comp_sub_inventory,
l_comp_locator_id,
p_hdr_rec.starting_lot_item_revision,
sysdate,
p_user_id,
sysdate,
p_user_id,p_hdr_rec.prev_lot_qty);
cln_debug_pub.Add('-------- Values successfully inserted into WSM_STARTING_LOTS_INTERFACE --------',1);
INSERT
INTO WSM_LOT_JOB_INTERFACE ( MODE_FLAG,
HEADER_ID,
GROUP_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SOURCE_LINE_ID,
ORGANIZATION_ID,
LOAD_TYPE,
STATUS_TYPE,
LAST_UNIT_COMPLETION_DATE,
PRIMARY_ITEM_ID,
WIP_SUPPLY_TYPE,
LOT_NUMBER,
JOB_NAME,
ALTERNATE_ROUTING_DESIGNATOR,
ALTERNATE_BOM_DESIGNATOR,
START_QUANTITY,
LAST_UPDATED_BY_NAME,
CREATED_BY_NAME,
PROCESS_PHASE,
PROCESS_STATUS,
FIRST_UNIT_START_DATE,
SCHEDULING_METHOD,
ALLOW_EXPLOSION)
VALUES ( decode(p_process_type,'STATUS_UPDATE',1,l_mode),
l_header_id,
l_group_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
l_source_line_id,
p_org_id,
decode(p_process_type,'JOB_CREATION',5,'STATUS_UPDATE',6), --LOAD_TYPE
decode(l_lot_class_code,'HOLD',6,'CANCEL',7,'RELEASE',3,'START',3,'UNRELEASED',1), --STATUS_TYPE
-- Bug 4727381, Issue d : Included 'UNRELEASED' value.
p_hdr_rec.scheduled_completion_date,
l_inventory_item_id,
'3', -- WIP_SUPPLY_TYPE
p_hdr_rec.lot_number,
p_hdr_rec.lot_number,
p_hdr_rec.alt_routing_designator,
NULL, --decode(p_process_type,'JOB_CREATION',NULL,'STATUS_UPDATE',p_alt_bom),
decode(l_lot_class_code,'START',p_qty_rec.lot_qty,'RELEASE',p_qty_rec.lot_qty,'UNRELEASED',p_qty_rec.lot_qty),
p_user_id,
p_user_id,
2,
1,
p_hdr_rec.first_unit_start_date,
l_sch_method,
'Y');
cln_debug_pub.Add('-------- Values successfully inserted into WSM_LOT_JOB_INTERFACE --------',1);
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'T',NULL,l_header_id);
SELECT custom_valid_status,error_message
INTO l_custom_valid_pass,l_custom_valid_err_msg
FROM M4R_WSM_DWIP_HDR_STAGING
WHERE msg_id = p_hdr_rec.msg_id
AND hdr_id = p_hdr_rec.hdr_id;
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_custom_valid_err_msg,l_group_id);
ROLLBACK TO before_insert;
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'R',NULL,l_header_id);
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,NULL);
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_header_id);
SELECT h.hdr_id,h.prev_lot_number,h.from_sub_inventory,h.operation_seq_num,h.lot_code,h.prev_lot_qty,q.lot_qty,
h.start_lot_alt_rout_designator,q.lot_classification_code,h.status_flag,h.lot_number
FROM M4R_WSM_DWIP_HDR_STAGING H ,M4R_WSM_DWIP_LOT_QTY_STAGING Q
WHERE h.msg_id = l_msg_id
AND( h.transaction_type = 'MERGE' OR q.lot_classification_code ='MERGE' )
AND h.lot_number = l_lot_number
AND h.from_sub_inventory = l_sub_inv
AND h.operation_seq_num IS NOT NULL
--AND h.status_flag = 'V'
AND q.hdr_id =h.hdr_id ;
SELECT h.hdr_id,h.prev_lot_qty,q.lot_qty,h.prev_operation_seq_num,h.scheduled_start_date,
h.scheduled_completion_date,h.starting_lot_item_code,h.primary_item_code,
h.lot_number,h.alt_routing_designator,h.to_sub_inventory,h.from_sub_inventory,
h.start_lot_alt_rout_designator,h.status_flag,h.prev_lot_number
FROM M4R_WSM_DWIP_HDR_STAGING H ,M4R_WSM_DWIP_LOT_QTY_STAGING Q
WHERE h.msg_id = l_msg_id
AND( h.transaction_type = 'SPLIT' OR q.lot_classification_code ='SPLIT' )
AND h.prev_lot_number = l_lot_number
AND h.from_sub_inventory = l_sub_inv
AND h.operation_seq_num IS NOT NULL
-- AND h.status_flag = 'V'
AND q.hdr_id =h.hdr_id ;
SELECT wsm_sm_txn_int_group_s.NEXTVAL
INTO l_group_id
FROM DUAL;
SELECT wsm_sm_txn_interface_s.NEXTVAL
INTO l_header_id
FROM DUAL;
SELECT wsm_split_merge_transactions_s.NEXTVAL
INTO l_trx_id
FROM DUAL;
SAVEPOINT before_insert;
INSERT
INTO WSM_SPLIT_MERGE_TXN_INTERFACE ( HEADER_ID,
TRANSACTION_TYPE_ID,
TRANSACTION_DATE,
ORGANIZATION_ID,
GROUP_ID,
PROCESS_STATUS,
TRANSACTION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES ( l_header_id,
decode(p_hdr_rec.transaction_type,'SPLIT',1,'MERGE',2,'CHANGE ASSEMBLY',3,'BONUS',4,
'CHANGE QUANTITY',6,'CHANGE JOB NAME',7),
p_hdr_rec.transaction_date,
p_org_id,
l_group_id,
'1', -- PROCESS_STATUS
l_trx_id,
sysdate,
p_user_id,
sysdate,
p_user_id);
cln_debug_pub.Add('-------- Values successfully inserted into WSM_SPLIT_MERGE_TXN_INTERFACE --------',1);
ROLLBACK TO BEFORE_INSERT;
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag ='E',
error_message = l_err_msg
WHERE msg_id = p_hdr_rec.msg_id
AND transaction_type = 'MERGE'
AND lot_number = l_rec.lot_number
AND from_sub_inventory = l_rec.from_sub_inventory
AND operation_seq_num IS NOT NULL
AND status_flag = 'V';
ROLLBACK TO BEFORE_INSERT;
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag ='E',
error_message = l_interface_err1
WHERE msg_id = p_hdr_rec.msg_id
AND transaction_type = 'MERGE'
AND lot_number = l_rec.lot_number
AND from_sub_inventory = l_rec.from_sub_inventory
AND operation_seq_num IS NOT NULL
AND status_flag = 'V';
g_exception_tracking_msg := 'Inserting values into WSM_STARTING_JOBS_INTERFACE for hdr_id : '|| l_rec.hdr_id;
INSERT
INTO WSM_STARTING_JOBS_INTERFACE ( HEADER_ID,
WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
INTRAOPERATION_STEP,
REPRESENTATIVE_FLAG,
GROUP_ID,
PROCESS_STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES ( l_header_id,
l_prev_wip_entity_id,
l_rec.operation_seq_num,
l_intra_step, -- INTRAOPERATION_STEP
l_rep_flag,
l_group_id,
1, -- PROCESS_STATUS
sysdate,
p_user_id,
sysdate,
p_user_id);
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,l_rec.hdr_id,'T',NULL,l_group_id);
cln_debug_pub.Add('-------- Values successfully inserted into WSM_STARTING_JOBS_INTERFACE for Job '|| l_rec.prev_lot_number,1);
SELECT WE.WIP_ENTITY_ID,WD.NET_QUANTITY
INTO l_prev_wip_entity_id,l_net_qty
FROM WIP_DISCRETE_JOBS WD,WIP_ENTITIES WE
WHERE we.wip_entity_name = p_hdr_rec.prev_lot_number
AND we.ORGANIZATION_ID = p_org_id
AND we.wip_entity_id = wd.wip_entity_id;
g_exception_tracking_msg := 'Inserting values into WSM_STARTING_JOBS_INTERFACE for hdr_id : '|| p_hdr_rec.hdr_id;
INSERT
INTO WSM_STARTING_JOBS_INTERFACE ( HEADER_ID,
WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
INTRAOPERATION_STEP,
REPRESENTATIVE_FLAG,
GROUP_ID,
PROCESS_STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES ( l_header_id,
l_prev_wip_entity_id,
p_hdr_rec.operation_seq_num,
l_intra_step,
NULL, -- REPRESENTATIVE_FLAG
l_group_id,
1, -- PROCESS_STATUS
sysdate,
p_user_id,
sysdate,
p_user_id);
cln_debug_pub.Add('-------- Values successfully inserted into WSM_STARTING_JOBS_INTERFACE --------',2);
ROLLBACK TO BEFORE_INSERT;
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag ='E',
error_message = l_err_msg
WHERE msg_id = p_hdr_rec.msg_id
AND transaction_type = 'SPLIT'
AND prev_lot_number = l_rec.prev_lot_number
AND from_sub_inventory = l_rec.from_sub_inventory
AND operation_seq_num IS NOT NULL
AND status_flag = 'V';
ROLLBACK TO BEFORE_INSERT;
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag ='E',
error_message = l_interface_err1
WHERE msg_id = p_hdr_rec.msg_id
AND transaction_type = 'SPLIT'
AND prev_lot_number = l_rec.prev_lot_number
AND from_sub_inventory = l_rec.from_sub_inventory
AND operation_seq_num IS NOT NULL
AND status_flag = 'V';
g_exception_tracking_msg := 'Inserting values into WSM_RESULTING_JOBS_INTERFACE for hdr_id : '|| l_rec.hdr_id;
INSERT
INTO WSM_RESULTING_JOBS_INTERFACE
( HEADER_ID,
GROUP_ID,
WIP_ENTITY_NAME,
PRIMARY_ITEM_ID,
START_QUANTITY,
NET_QUANTITY,
COMMON_BOM_SEQUENCE_ID,
COMMON_ROUTING_SEQUENCE_ID,
ROUTING_REVISION,
ROUTING_REVISION_DATE,
BOM_REVISION,
BOM_REVISION_DATE,
ALTERNATE_BOM_DESIGNATOR,
ALTERNATE_ROUTING_DESIGNATOR,
COMPLETION_SUBINVENTORY,
STARTING_OPERATION_SEQ_NUM,
STARTING_INTRAOPERATION_STEP,
SCHEDULED_START_DATE,
SCHEDULED_COMPLETION_DATE,
FORWARD_OP_OPTION,
BONUS_ACCT_ID,
PROCESS_STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES ( l_header_id,
l_group_id,
l_rec.lot_number,
l_inventory_item_id,
l_rec.lot_qty, -- starting quantity (sum of all the start qty in the resulting job >=
-- existing for the job)
l_rec.lot_qty,-- net quantity
l_common_bom_seq_id,
l_common_rout_seq_id,
l_rout_rev,
l_rout_rev_date,
l_bom_rev,
l_bom_rev_date,
l_alt_bom,
decode(l_rec.alt_routing_designator,NULL,l_alt_rout,l_rec.alt_routing_designator),
decode(l_rec.to_sub_inventory,NULL,l_comp_sub_inventory,l_rec.to_sub_inventory),
l_rec.prev_operation_seq_num,
1, -- STARTING_INTRAOPERATION_STEP
l_rec.scheduled_start_date,
l_rec.scheduled_completion_date,
4, -- FORWARD_OP_OPTION
NULL, --l_bonus_acc_id
1, -- PROCESS_STATUS
sysdate,
p_user_id,
sysdate,
p_user_id);
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,l_rec.hdr_id,'T',NULL,NULL);
g_exception_tracking_msg := '-------- Values successfully inserted into WSM_RESULTING_JOBS_INTERFACE --------';
ELSIF p_process_type = 'JOB_UPDATE' THEN
BEGIN
IF p_hdr_rec.transaction_type = 'CHANGE JOB NAME' THEN
g_exception_tracking_msg := 'Querying WIP_DISCRETE_JOBS for prev_lot_number';
SELECT WE.WIP_ENTITY_ID,WD.NET_QUANTITY
--INTO l_prev_wip_entity_id,l_net_qty bsaratna
INTO l_wip_entity_id,l_net_qty
FROM WIP_DISCRETE_JOBS WD,WIP_ENTITIES WE
WHERE we.wip_entity_name = p_hdr_rec.prev_lot_number
AND we.ORGANIZATION_ID = p_org_id
AND we.wip_entity_id = wd.wip_entity_id;
SELECT WE.WIP_ENTITY_ID,WD.NET_QUANTITY
--INTO l_prev_wip_entity_id,l_net_qty bsaratna
INTO l_wip_entity_id,l_net_qty
FROM WIP_DISCRETE_JOBS WD,WIP_ENTITIES WE
WHERE we.wip_entity_name = p_hdr_rec.lot_number
AND we.ORGANIZATION_ID = p_org_id
AND we.wip_entity_id = wd.wip_entity_id;
g_exception_tracking_msg := 'Inserting values into WSM_STARTING_JOBS_INTERFACE';
INSERT
INTO WSM_STARTING_JOBS_INTERFACE ( HEADER_ID,
WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
INTRAOPERATION_STEP,
REPRESENTATIVE_FLAG,
GROUP_ID,
PROCESS_STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES ( l_header_id,
l_wip_entity_id,
p_hdr_rec.operation_seq_num,
l_intra_step,
NULL, -- REPRESENTATIVE_FLAG
l_group_id,
1, -- PROCESS_STATUS
sysdate,
p_user_id,
sysdate,
p_user_id);
g_exception_tracking_msg := '-------- Values successfully inserted into WSM_STARTING_JOBS_INTERFACE --------';
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'T',NULL,NULL);
ROLLBACK TO BEFORE_INSERT;
g_exception_tracking_msg := 'Inserting values into WSM_RESULTING_JOBS_INTERFACE';
INSERT
INTO WSM_RESULTING_JOBS_INTERFACE
( HEADER_ID,
GROUP_ID,
WIP_ENTITY_NAME,
PRIMARY_ITEM_ID,
START_QUANTITY,
NET_QUANTITY,
COMMON_BOM_SEQUENCE_ID,
COMMON_ROUTING_SEQUENCE_ID,
ROUTING_REVISION,
ROUTING_REVISION_DATE,
BOM_REVISION,
BOM_REVISION_DATE,
ALTERNATE_BOM_DESIGNATOR,
ALTERNATE_ROUTING_DESIGNATOR,
COMPLETION_SUBINVENTORY,
STARTING_OPERATION_SEQ_NUM,
STARTING_INTRAOPERATION_STEP,
SCHEDULED_START_DATE,
SCHEDULED_COMPLETION_DATE,
FORWARD_OP_OPTION,
BONUS_ACCT_ID,
PROCESS_STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES ( l_header_id,
l_group_id,
p_hdr_rec.lot_number,
l_inventory_item_id,
decode(p_process_type,'JOB_RECOVERY',p_qty_rec.lot_qty,'JOB_UPDATE',p_qty_rec.lot_qty,l_result_qty),
-- starting quantity should be greater than existing (start_q - scrapped_q - completed_q)
decode(p_hdr_rec.transaction_type,'BONUS',p_qty_rec.lot_qty,'CHANGE QUANTITY',p_qty_rec.lot_qty,
'CHANGE JOB NAME',NULL,'CHANGE ASSEMBLY',NULL,l_result_qty),-- net quantity
l_common_bom_seq_id,
l_common_rout_seq_id,
l_rout_rev,
l_rout_rev_date,
l_bom_rev,
l_bom_rev_date,
l_alt_bom,
decode(p_hdr_rec.alt_routing_designator,NULL,l_alt_rout,p_hdr_rec.alt_routing_designator),
decode(p_hdr_rec.to_sub_inventory,NULL,l_comp_sub_inventory,p_hdr_rec.to_sub_inventory),
p_hdr_rec.operation_seq_num,
decode(p_process_type,'JOB_RECOVERY',1,'JOB_UPDATE',1,NULL), -- STARTING_INTRAOPERATION_STEP
p_hdr_rec.scheduled_start_date,
p_hdr_rec.scheduled_completion_date,
decode(p_process_type,'JOB_RECOVERY',4,'WIP_MERGE',4,NULL), -- FORWARD_OP_OPTION
l_bonus_acc_id,
1, -- PROCESS_STATUS
sysdate,
p_user_id,
sysdate,
p_user_id);
g_exception_tracking_msg := '-------- Values successfully inserted into WSM_RESULTING_JOBS_INTERFACE --------';
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'T',NULL,NULL);
SELECT custom_valid_status,error_message
INTO l_custom_valid_pass,l_custom_valid_err_msg
FROM M4R_WSM_DWIP_HDR_STAGING
WHERE msg_id = p_hdr_rec.msg_id
AND hdr_id = p_hdr_rec.hdr_id;
ROLLBACK TO before_insert;
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag ='I' , error_message = l_custom_valid_err_msg,group_id = l_group_id
WHERE msg_id = p_hdr_rec.msg_id
AND status_flag ='T';
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag ='R' ,group_id = l_group_id
WHERE msg_id = p_hdr_rec.msg_id
AND status_flag ='T';
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag ='S',group_id = l_group_id
WHERE msg_id = p_hdr_rec.msg_id
AND status_flag ='R';
FOR i IN (SELECT message
FROM wsm_interface_errors
WHERE message_type = 1
AND header_id = (SELECT header_id
FROM wsm_split_merge_txn_interface
WHERE group_id = l_group_id))
LOOP
IF (g_debug_level <= 5) THEN
cln_debug_pub.Add('Loop error : ' || i.message, 5);
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag ='E', error_message = l_errbuf || l_interface_err,group_id = l_group_id
WHERE msg_id = p_hdr_rec.msg_id
AND status_flag = 'R';
cln_debug_pub.Add('M4R_WSM_DWIP_HDR_STAGING updated', 1);
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag ='S', group_id = l_group_id
WHERE msg_id = p_hdr_rec.msg_id
AND status_flag ='R';
/*SELECT MESSAGE
INTO l_interface_err
FROM WSM_INTERFACE_ERRORS
WHERE MESSAGE_TYPE = 1
AND header_id = ( SELECT HEADER_ID
FROM wsm_split_merge_txn_interface
WHERE group_id = l_group_id);
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag ='E', error_message = l_errbuf || l_interface_err,group_id = l_group_id
WHERE msg_id = p_hdr_rec.msg_id
AND status_flag = 'R';
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag ='S', group_id = l_group_id
WHERE msg_id = p_hdr_rec.msg_id
AND status_flag ='R';*/
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_group_id);
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_group_id);
PROCEDURE INSERT_INV_REC( p_process_type IN VARCHAR2,
p_org_id IN NUMBER,
p_user_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_from_sub_inventory IN VARCHAR2,
p_to_sub_inventory IN VARCHAR2,
p_transfer_to_org IN VARCHAR2,
p_lot_qty IN NUMBER,
p_lot_uom IN VARCHAR2,
p_trx_date IN DATE,
p_lot_number IN VARCHAR2,
p_op_seq_num IN NUMBER,
p_trx_bat_seq IN NUMBER,
p_trx_if_id IN NUMBER,
p_parent_id IN NUMBER,
p_trx_hdr_id IN NUMBER,
p_wip_entity_id IN NUMBER,
p_wip_entity_name IN VARCHAR2,
x_resultout OUT NOCOPY VARCHAR2,
x_err_msg OUT NOCOPY VARCHAR2) AS
l_prev_locator_id NUMBER;
cln_debug_pub.Add('-------- Entering procedure INSERT_INV_REC --------',2);
SELECT ORGANIZATION_ID
INTO l_org_id
FROM mtl_parameters
WHERE organization_code = p_transfer_to_org;
g_exception_tracking_msg := 'Inserting values into MTL_TRANSACTIONS_INTERFACE';
INSERT
INTO MTL_TRANSACTIONS_INTERFACE ( SOURCE_CODE,
SOURCE_LINE_ID,
SOURCE_HEADER_ID,
PROCESS_FLAG,
TRANSACTION_MODE,
VALIDATION_REQUIRED,
TRANSACTION_INTERFACE_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
LOCATOR_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
TRANSACTION_DATE,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_NAME,
TRANSACTION_TYPE_ID,
WIP_ENTITY_TYPE,
OPERATION_SEQ_NUM,
TRANSACTION_BATCH_SEQ,
TRANSACTION_BATCH_ID,
TRANSACTION_HEADER_ID,
PARENT_ID,
TRANSFER_SUBINVENTORY,
TRANSFER_ORGANIZATION,
TRANSFER_LOCATOR,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY ,
FLOW_SCHEDULE,
SCHEDULED_FLAG,
LOCK_FLAG)
VALUES ( decode(p_process_type,'INV_SPLIT','Split Lot','INV_MERGE','Merge Lot','LOT_TRANSFER','Transfer',
'LOT_TRANSLATE','Translate','MTL_CONSUME','Issue to WIP'),
1,--SOURCE_LINE_ID
1,--SOURCE_HEADER_ID
1,--PROCESS_FLAG
2,--TRANSACTION_MODE -- (3 - Backgound, if cp is used to process the rows)
1, --VALIDATION_REQUIRED (FULL validation, 2 if validate only derived columns)
p_trx_if_id,
p_inventory_item_id,
p_org_id,
p_from_sub_inventory,
NULL, --l_prev_locator_id,
p_lot_qty,
p_lot_uom,
p_trx_date,
p_wip_entity_id, --TRANSACTION_SOURCE_ID
p_wip_entity_name,
decode(p_process_type,'INV_SPLIT',82,'INV_MERGE',83,'LOT_TRANSLATE',84,
'LOT_TRANSFER',2,'MTL_CONSUME',35),
decode(p_process_type,'MTL_CONSUME',1,NULL),
p_op_seq_num,
p_trx_bat_seq, --TRANSACTION_BATCH_SEQ
p_trx_hdr_id, -- TRANSACTION_BATCH_ID
p_trx_hdr_id, -- TRANSACTION_HEADER_ID
p_parent_id,
p_to_sub_inventory,
l_org_id,
NULL, -- p_hdr_rec.locator_id, this has to be the to_locator_id
sysdate,
p_user_id,
sysdate,
p_user_id,
NULL, -- FLOW_SCHEDULE
2,
2);
cln_debug_pub.Add('-------- Values successfully inserted into MTL_TRANSACTIONS_INTERFACE --------',1);
g_exception_tracking_msg := 'Inserting values into MTL_TRANSACTION_LOTS_INTERFACE';
INSERT
INTO MTL_TRANSACTION_LOTS_INTERFACE ( TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LOT_NUMBER,
TRANSACTION_QUANTITY)
VALUES (p_trx_if_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_lot_number,
p_lot_qty);
cln_debug_pub.Add('-------- Values successfully inserted into MTL_TRANSACTION_LOTS_INTERFACE --------',2);
cln_debug_pub.Add('-------- Exiting procedure INSERT_INV_REC --------',2);
cln_debug_pub.Add('------- Exception in procedure INSERT_INV_REC --------',5);
END INSERT_INV_REC;
SELECT *
FROM M4R_WSM_DWIP_HDR_STAGING H
WHERE h.msg_id = l_msg_id
AND h.transaction_type = 'MERGE'
AND h.lot_number = l_lot_number
AND h.from_sub_inventory = l_sub_inv
AND h.prev_operation_seq_num IS NULL
AND h.operation_seq_num IS NULL
AND h.status_flag = 'V';
SELECT h.hdr_id,h.transaction_date,h.operation_seq_num,h.lot_number,h.prev_lot_number,h.prev_lot_uom,h.prev_lot_qty,
h.primary_item_code,h.primary_item_revision,h.starting_lot_item_code,h.alt_routing_designator,h.from_sub_inventory,
h.to_sub_inventory,q.lot_uom,q.lot_qty,h.transfer_to_org,h.status_flag
FROM M4R_WSM_DWIP_HDR_STAGING H ,M4R_WSM_DWIP_LOT_QTY_STAGING Q
WHERE h.msg_id = l_msg_id
AND h.transaction_type = 'SPLIT'
AND h.prev_lot_number = l_lot_number
AND h.from_sub_inventory = l_sub_inv
AND h.prev_operation_seq_num IS NULL
AND h.operation_seq_num IS NULL
AND h.status_flag = 'V'
AND q.hdr_id = h.hdr_id;
SAVEPOINT before_insert;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_trx_if_id
FROM DUAL;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_trx_hdr_id
FROM DUAL;
ROLLBACK TO BEFORE_INSERT;
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag ='E',
error_message = l_interface_err1,
group_id = l_trx_if_id
WHERE msg_id = p_hdr_rec.msg_id
AND transaction_type = 'SPLIT'
AND prev_lot_number = p_hdr_rec.prev_lot_number
AND from_sub_inventory = p_hdr_rec.from_sub_inventory
AND prev_operation_seq_num IS NULL
AND operation_seq_num IS NULL
AND status_flag = 'V';
INSERT_INV_REC( p_process_type,
p_org_id,
p_user_id,
l_prev_inventory_item_id,
p_hdr_rec.from_sub_inventory,
p_hdr_rec.to_sub_inventory,
p_hdr_rec.transfer_to_org,
l_lot_qty,
p_hdr_rec.prev_lot_uom,
p_hdr_rec.transaction_date,
p_hdr_rec.prev_lot_number,
NULL, -- OP SEQ NUM
l_trx_bat_seq,
l_trx_if_id,
l_trx_if_id,
l_trx_hdr_id,
NULL, -- WIP ID
NULL, -- WIP NAME
l_retcode,
l_err_msg);
ROLLBACK TO BEFORE_INSERT;
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag ='E',
error_message = l_interface_err
WHERE msg_id = p_hdr_rec.msg_id
AND transaction_type = 'SPLIT'
AND prev_lot_number = l_rec.prev_lot_number
AND from_sub_inventory = l_rec.from_sub_inventory
AND prev_operation_seq_num IS NULL
AND operation_seq_num IS NULL
AND status_flag = 'V';
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_trx_if_id
FROM DUAL;
ROLLBACK TO BEFORE_INSERT;
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag ='E',
error_message = l_interface_err1
WHERE msg_id = p_hdr_rec.msg_id
AND transaction_type = 'SPLIT'
AND prev_lot_number = l_rec.prev_lot_number
AND from_sub_inventory = l_rec.from_sub_inventory
AND prev_operation_seq_num IS NULL
AND operation_seq_num IS NULL
AND status_flag = 'V';
INSERT_INV_REC( p_process_type,
p_org_id,
p_user_id,
l_inventory_item_id,
l_rec.from_sub_inventory,
l_rec.to_sub_inventory,
l_rec.transfer_to_org,
l_rec.lot_qty,
l_rec.lot_uom,
l_rec.transaction_date,
l_rec.lot_number,
NULL, -- OP SEQ NUM
l_trx_bat_seq,
l_trx_if_id,
l_parent_id,
l_trx_hdr_id,
NULL, -- WIP ID
NULL, -- WIP NAME
l_retcode,
l_err_msg);
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,l_rec.hdr_id,'T',NULL,NULL);
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,l_rec.hdr_id,'I',l_err_msg,l_trx_if_id);
ROLLBACK TO BEFORE_INSERT;
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag ='E',
error_message = l_interface_err1
WHERE msg_id = p_hdr_rec.msg_id
AND transaction_type = 'MERGE'
AND lot_number = p_hdr_rec.lot_number
AND from_sub_inventory = p_hdr_rec.from_sub_inventory
AND prev_operation_seq_num IS NULL
AND operation_seq_num IS NULL
AND status_flag = 'V';
INSERT_INV_REC( p_process_type,
p_org_id,
p_user_id,
l_inventory_item_id,
p_hdr_rec.from_sub_inventory,
p_hdr_rec.to_sub_inventory,
p_hdr_rec.transfer_to_org,
p_qty_rec.lot_qty,
p_qty_rec.lot_uom,
p_hdr_rec.transaction_date,
p_hdr_rec.lot_number,
NULL, -- OP SEQ NUM
l_trx_bat_seq,
l_trx_if_id,
l_parent_id,
l_trx_hdr_id,
NULL, -- WIP ID
NULL, -- WIP NAME
l_retcode,
l_err_msg);
ROLLBACK TO BEFORE_INSERT;
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag ='E',
error_message = l_interface_err
WHERE msg_id = p_hdr_rec.msg_id
AND transaction_type = 'MERGE'
AND lot_number = l_rec.lot_number
AND from_sub_inventory = l_rec.from_sub_inventory
AND prev_operation_seq_num IS NULL
AND operation_seq_num IS NULL
AND status_flag = 'V';
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_trx_if_id
FROM DUAL;
ROLLBACK TO BEFORE_INSERT;
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag ='E',
error_message = l_interface_err1
WHERE msg_id = p_hdr_rec.msg_id
AND transaction_type = 'MERGE'
AND lot_number = l_rec.lot_number
AND from_sub_inventory = l_rec.from_sub_inventory
AND prev_operation_seq_num IS NULL
AND operation_seq_num IS NULL
AND status_flag = 'V';
INSERT_INV_REC( p_process_type,
p_org_id,
p_user_id,
l_prev_inventory_item_id,
l_rec.from_sub_inventory,
l_rec.to_sub_inventory,
l_rec.transfer_to_org,
l_lot_qty,
l_rec.prev_lot_uom,
l_rec.transaction_date,
l_rec.prev_lot_number,
NULL, -- OP SEQ NUM
l_trx_bat_seq,
l_trx_if_id,
l_parent_id,
l_trx_hdr_id,
NULL, -- WIP ID
NULL, -- WIP NAME
l_retcode,
l_err_msg);
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,l_rec.hdr_id,'T',NULL,NULL);
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,l_rec.hdr_id,'I',l_err_msg,l_trx_if_id);
ROLLBACK TO BEFORE_INSERT;
ROLLBACK TO BEFORE_INSERT;
INSERT_INV_REC( p_process_type,
p_org_id,
p_user_id,
l_prev_inventory_item_id,
p_hdr_rec.from_sub_inventory,
p_hdr_rec.to_sub_inventory,
p_hdr_rec.transfer_to_org,
l_lot_qty,
p_hdr_rec.prev_lot_uom,
p_hdr_rec.transaction_date,
p_hdr_rec.prev_lot_number,
p_hdr_rec.operation_seq_num, -- OP SEQ NUM
1, -- batch seq
l_trx_if_id,
l_trx_if_id, -- parent id
l_trx_hdr_id,
l_wip_entity_id, -- WIP ID
l_wip_entity_name, -- WIP NAME
l_retcode,
l_err_msg);
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'T',NULL,NULL);
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_parent_id);
ROLLBACK TO BEFORE_INSERT;
INSERT_INV_REC( p_process_type,
p_org_id,
p_user_id,
l_prev_inventory_item_id,
p_hdr_rec.from_sub_inventory,
p_hdr_rec.to_sub_inventory,
p_hdr_rec.transfer_to_org,
l_lot_qty,
p_hdr_rec.prev_lot_uom,
p_hdr_rec.transaction_date,
p_hdr_rec.prev_lot_number,
NULL, -- OP SEQ NUM
1, --l_trx_bat_seq,
l_trx_if_id,
l_parent_id,
l_trx_hdr_id,
NULL, -- WIP ID
NULL, -- WIP NAME
l_retcode,
l_err_msg);
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_trx_if_id
FROM DUAL;
INSERT_INV_REC( p_process_type,
p_org_id,
p_user_id,
l_inventory_item_id,
p_hdr_rec.from_sub_inventory,
p_hdr_rec.to_sub_inventory,
p_hdr_rec.transfer_to_org,
p_qty_rec.lot_qty,
p_qty_rec.lot_uom,
p_hdr_rec.transaction_date,
p_hdr_rec.lot_number,
NULL, -- OP SEQ NUM
2, --l_trx_bat_seq,
l_trx_if_id,
l_parent_id,
l_trx_hdr_id,
NULL, -- WIP ID
NULL, -- WIP NAME
l_retcode,
l_err_msg);
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'T',NULL,l_parent_id);
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_parent_id);
ROLLBACK TO BEFORE_INSERT;
INSERT_INV_REC( p_process_type,
p_org_id,
p_user_id,
l_inventory_item_id,
p_hdr_rec.from_sub_inventory,
p_hdr_rec.to_sub_inventory,
p_hdr_rec.transfer_to_org,
p_qty_rec.lot_qty,
p_qty_rec.lot_uom,
p_hdr_rec.transaction_date,
p_hdr_rec.lot_number,
NULL, -- OP SEQ NUM
1, --l_trx_bat_seq,
l_trx_if_id,
l_trx_if_id,
l_trx_hdr_id,
NULL, -- WIP ID
NULL, -- WIP NAME
l_retcode,
l_err_msg);
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'T',NULL,l_parent_id);
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_parent_id);
SELECT custom_valid_status,error_message
INTO l_custom_valid_pass,l_custom_valid_err_msg
FROM M4R_WSM_DWIP_HDR_STAGING
WHERE msg_id = p_hdr_rec.msg_id
AND hdr_id = p_hdr_rec.hdr_id;
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_custom_valid_err_msg,l_parent_id);
ROLLBACK TO before_insert;
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'R',NULL,l_trx_if_id);
SELECT PROCESS_FLAG,ERROR_CODE,ERROR_EXPLANATION
INTO l_interface_status,l_err_code,l_err_msg
FROM MTL_TRANSACTIONS_INTERFACE
WHERE TRANSACTION_INTERFACE_ID = l_parent_id;
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag = 'E',error_message = l_err_msg,group_id = l_parent_id --l_trx_hdr_id
WHERE msg_id = p_hdr_rec.msg_id
AND lot_number = p_hdr_rec.lot_number
AND status_flag ='R';
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag = 'S',error_message = NULL
WHERE msg_id = p_hdr_rec.msg_id
AND lot_number = p_hdr_rec.lot_number
AND status_flag ='R';
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag = 'E',error_message = l_err_msg,group_id = l_trx_hdr_id
WHERE msg_id = p_hdr_rec.msg_id
AND lot_number = p_hdr_rec.lot_number
AND status_flag ='R';
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,NULL);
UPDATE_STATUS_FLAG(p_hdr_rec.msg_id,p_hdr_rec.hdr_id,'I',l_err_msg,l_trx_hdr_id);
l_update_cln_parameter_list wf_parameter_list_t;
SELECT *
FROM M4R_WSM_DWIP_LOT_QTY_STAGING
WHERE HDR_ID = l_hdr_id;
SELECT *
INTO l_hdr_rec
FROM M4R_WSM_DWIP_HDR_STAGING
WHERE MSG_ID = l_msg_id
AND status_flag = 'V'
AND ROWNUM = 1;
SELECT ORGANIZATION_ID
INTO l_org_id
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE organization_code = l_hdr_rec.TRANSFER_FROM_ORG;
l_process_type= 'STATUS_UPDATE' THEN
wf_engine.SetItemAttrText(p_itemtype,p_itemkey,'TRANSACTION_TYPE','CREATE_UPD');
l_process_type = 'JOB_UPDATE' OR
l_process_type = 'WIP_MERGE' OR
l_process_type = 'WIP_SPLIT' THEN
WIP_LOT_TXNS( l_process_type,
l_hdr_rec,
l_qty_rec,
l_org_id,
l_user_id,
p_itemkey,
x_resultout);
ELSIF ((l_process_type= 'LOT_TRANSLATE') OR -- (Lot Update/Change Item)
(l_process_type= 'LOT_TRANSFER' ) OR --(Lot Update/Change Lot Number,Lot transfer)
(l_process_type= 'INV_MERGE') OR
(l_process_type= 'INV_SPLIT')) THEN
wf_engine.SetItemAttrText(p_itemtype,p_itemkey,'TRANSACTION_TYPE','INV_LOT_TXN');
UPDATE_STATUS_FLAG(l_hdr_rec.msg_id,l_hdr_rec.hdr_id,'I',l_coll_hist_msg,NULL);
UPDATE_STATUS_FLAG(l_hdr_rec.msg_id,l_hdr_rec.hdr_id,'I',l_coll_hist_msg,NULL);
UPDATE_STATUS_FLAG(l_hdr_rec.msg_id,l_hdr_rec.hdr_id,'I',l_coll_hist_msg,NULL);
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag = 'V'
WHERE MSG_ID = l_msg_id;
SELECT count(*)
INTO l_count_valid_rows
FROM M4R_WSM_DWIP_HDR_STAGING
WHERE MSG_ID = l_msg_id
AND status_flag = 'V';
l_update_cln_parameter_list wf_parameter_list_t;
SELECT *
INTO l_rec
FROM M4R_WSM_DWIP_HDR_STAGING
WHERE msg_id = l_msg_id
AND status_flag = 'R'
AND rownum < 2;
SELECT PROCESS_STATUS,ERROR_MSG
INTO l_interface_status,l_err_msg
FROM WSM_LOT_JOB_INTERFACE
WHERE header_id = l_rec.group_id;
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag = 'E',error_message = l_err_msg
WHERE msg_id = l_msg_id
AND hdr_id = l_rec.hdr_id;
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag = 'E',error_message = l_err_msg
WHERE msg_id = l_msg_id
AND hdr_id = l_rec.hdr_id;
UPDATE M4R_WSM_DWIP_HDR_STAGING
SET status_flag = 'S',error_message = NULL
WHERE msg_id = l_msg_id
AND hdr_id = l_rec.hdr_id;
l_update_cln_parameter_list := wf_parameter_list_t();
WF_EVENT.AddParameterToList('COLLABORATION_POINT','APPS',l_update_cln_parameter_list);
WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',l_msg_id,l_update_cln_parameter_list);
WF_EVENT.AddParameterToList('MESSAGE_TEXT', l_err_msg, l_update_cln_parameter_list);
l_update_cln_parameter_list := wf_parameter_list_t();
WF_EVENT.AddParameterToList('COLLABORATION_POINT','APPS',l_update_cln_parameter_list);
WF_EVENT.AddParameterToList('XMLG_INTERNAL_CONTROL_NUMBER',l_msg_id,l_update_cln_parameter_list);
WF_EVENT.AddParameterToList('COLLABORATION_STATUS','ERROR',l_update_cln_parameter_list);
WF_EVENT.AddParameterToList('DOCUMENT_STATUS','ERROR',l_update_cln_parameter_list);
WF_EVENT.AddParameterToList('MESSAGE_TEXT', l_err_msg, l_update_cln_parameter_list);
cln_debug_pub.Add('Workflow event- oracle.apps.cln.ch.collaboration.update', 1);
SELECT M4R_7B1_OSFM_S1.NEXTVAL
INTO l_event_key
FROM DUAL;
g_exception_tracking_msg := 'Raising oracle.apps.cln.ch.collaboration.update event ';
wf_event.raise(p_event_name => 'oracle.apps.cln.ch.collaboration.update',
p_event_key => '7B1:' || l_event_key,
p_parameters => l_update_cln_parameter_list);