[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM csf_m_debrief_parts_inq
WHERE tranid$$ = b_tranid
AND clid$$cs = b_user_name;
SELECT USER_ID,RESPONSIBILITY_ID,APP_ID FROM asg_user WHERE user_name = b_user;
SELECT INVENTORY_ITEM_ID,MATERIAL_BILLABLE_FLAG,PRIMARY_UOM_CODE
FROM MTL_SYSTEM_ITEMS_B
WHERE UPPER(SEGMENT1) = UPPER(c_item_name)
AND ORGANIZATION_ID = c_org_id;
SELECT cii.INSTANCE_ID,
cii.SERIAL_NUMBER,
cii.LOT_NUMBER,
cii.INVENTORY_REVISION,
msi.INVENTORY_ITEM_ID,
msi.MATERIAL_BILLABLE_FLAG,
msi.PRIMARY_UOM_CODE
FROM CSI_ITEM_INSTANCES cii,
MTL_SYSTEM_ITEMS_B msi
WHERE UPPER(SERIAL_NUMBER) = UPPER(c_serial_number)
AND msi.inventory_item_id = cii.inventory_item_id
AND msi.organization_id = c_org_id;
SELECT cii.instance_id,
cii.SERIAL_NUMBER,
cii.LOT_NUMBER,
cii.INVENTORY_REVISION,
msi.INVENTORY_ITEM_ID,
msi.MATERIAL_BILLABLE_FLAG,
msi.PRIMARY_UOM_CODE
FROM CSI_ITEM_INSTANCES cii,
MTL_SYSTEM_ITEMS_B msi
WHERE UPPER(INSTANCE_NUMBER) = UPPER(c_inst_number)
AND msi.inventory_item_id = cii.inventory_item_id
AND msi.organization_id = c_org_id;
SELECT tbt.txn_billing_type_id
FROM
csm_txn_billing_types_acc acc,
cs_txn_billing_types tbt,
cs_transaction_types_b ttb,
cs_business_processes bpr, cs_bus_process_txns bpt, CS_BILLING_TYPE_CATEGORIES cbtc
WHERE acc.txn_billing_type_id = tbt.txn_billing_type_id
AND acc.business_process_id = bpt.business_process_id
AND tbt.transaction_type_id = ttb.transaction_type_id
AND tbt.billing_type = cbtc.billing_type
AND cbtc.billing_category IN ('M')
AND bpt.transaction_type_id = tbt.transaction_type_id
AND bpr.business_process_id = bpt.business_process_id
AND bpr.field_service_flag = 'Y'
AND bpr.business_process_id = c_buss_proc_id
AND bpt.transaction_type_id = c_txn_type_id
AND cbtc.billing_type = c_billing_type;
SELECT ttb.line_order_category_code
FROM CS_TRANSACTION_TYPES_B ttb
WHERE ttb.transaction_type_id = b_transaction_type_id;
SELECT tst.rejected_flag, tst.on_hold_flag, tst.cancelled_flag,
tst.closed_flag, tst.completed_flag
FROM csf_debrief_headers dh, jtf_task_assignments tas,
jtf_task_statuses_b tst
WHERE dh.task_assignment_id = tas.task_assignment_id
AND tas.assignment_status_id = tst.task_status_id
AND dh.debrief_header_id = p_debrief_header_id;
This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
***/
PROCEDURE APPLY_INSERT
(
p_record IN c_debrief_parts%ROWTYPE,
p_error_msg out nocopy VARCHAR2,
x_return_status IN out nocopy VARCHAR2
) IS
cursor c_deb_head ( b_task_assignment_id number )
is
select debrief_header_id
, task_assignment_id
, debrief_number
from csf_debrief_headers
where task_assignment_id = b_task_assignment_id;
select source_object_type_code
from jtf_tasks_b jtb
, jtf_task_assignments jta
where jtb.task_id = jta.task_id
and jta.task_assignment_id = b_task_assignment_id;
l_spare_update_status csf_debrief_lines.spare_update_status%TYPE;
CSM_UTIL_PKG.log( 'Entered APPLY_INSERT for debrief_line_id'|| p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_PARTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'|| ' ROOT ERROR: ' || p_error_msg|| ' for PK ' ||
p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_PARTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
l_deb_rec.last_update_date := l_date;
l_deb_rec.last_updated_by := NVL(p_record.last_updated_by,FND_GLOBAL.USER_ID); --12.1
l_deb_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
|| ' ROOT ERROR: csf_debrief_pub.create_debrief'
|| ' for PK ' || p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_PARTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
CSM_UTIL_PKG.log('Error in CSF_DEBRIEF_PARTS_PKG.APPLY_INSERT: '
|| 'Root cause csp_transactions_pub.transact_material failed: '
|| 'for Debrief Line Id: ' || p_record.debrief_line_id,'CSM_DEBRIEF_PARTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
l_spare_update_status := 'FAILED';
l_spare_update_status := 'SUCCEEDED';
l_spare_update_status := NULL;
CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
|| ' ROOT ERROR: ' || p_error_msg
|| ' for PK ' ||p_record.DEBRIEF_LINE_ID,
'CSM_DEBRIEF_PARTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
l_line_rec.last_update_date := l_date;
l_line_rec.last_updated_by := NVL(p_record.last_updated_by,FND_GLOBAL.USER_ID); --12.1
l_line_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
l_line_rec.spare_update_status := l_spare_update_status;
CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
|| ' ROOT ERROR: csf_debrief_pub.create_debrief_lines'
|| ' for PK ' || p_record.DEBRIEF_LINE_ID, 'CSM_DEBRIEF_PARTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR );
csf_debrief_update_pkg.form_Call (1.0, l_debrief_header_id );
fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_INSERT:'
|| ' for PK ' || p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_PARTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_EXCEPTION );
END APPLY_INSERT;
This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
***/
PROCEDURE APPLY_UPDATE
(
p_record IN c_debrief_parts%ROWTYPE,
p_error_msg out nocopy VARCHAR2,
x_return_status IN out nocopy VARCHAR2
) IS
--CURSOR Declarations
CURSOR c_cdl( b_debrief_line_number csf_debrief_lines.debrief_line_number%TYPE )
IS
SELECT cdl.debrief_header_id
, cdl.debrief_line_id
, cdl.last_update_date
, cdl.issuing_inventory_org_id
from CSF_DEBRIEF_LINES CDL
WHERE cdl.debrief_line_number = b_debrief_line_number;
SELECT cdl.debrief_line_id
from CSF_DEBRIEF_LINES CDL
where CDL.DEBRIEF_LINE_NUMBER = b_debrief_line_num;
l_spare_update_status csf_debrief_lines.spare_update_status%TYPE;
CSM_UTIL_PKG.log( 'Entered APPLY_UPDATE for debrief_line_id'|| p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_PARTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_UPDATE:'
|| ' ROOT ERROR: ' || p_error_msg
|| ' for PK ' ||
p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_PARTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_ERROR);
CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_UPDATE:'
|| ' ROOT ERROR: ' || p_error_msg
|| ' for PK ' ||
p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_PARTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_ERROR);
IF(r_cdl.last_update_date <> p_record.server_last_update_date) THEN
x_return_status := FND_API.G_RET_STS_ERROR;
'UPWARD SYNC CONFLICT: CLIENT LOST: CSM_DEBRIEF_PARTS_PKG.APPLY_UPDATE: P_KEY = '
|| p_record.debrief_line_id;
csm_util_pkg.log(p_error_msg,'CSM_DEBRIEF_PARTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_ERROR);
l_spare_update_status := NULL;
CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_INSERT:'
|| ' ROOT ERROR: ' || p_error_msg
|| ' for PK ' ||p_record.DEBRIEF_LINE_ID,
'CSM_DEBRIEF_PARTS_PKG.APPLY_INSERT',FND_LOG.LEVEL_ERROR);
l_line_rec.last_update_date := l_date;
l_line_rec.last_updated_by := NVL(p_record.last_updated_by,FND_GLOBAL.USER_ID); --12.1
l_line_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
l_line_rec.spare_update_status := l_spare_update_status;
csf_debrief_pub.update_debrief_line
( p_api_version_number => 1.0
, p_init_msg_list => FND_API.G_TRUE
, p_commit => FND_API.G_FALSE
, x_return_status => x_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_debrief_line_rec => l_line_rec
);
CSM_UTIL_PKG.log( 'Error in ' || g_object_name || '.APPLY_UPDATE:'
|| ' ROOT ERROR: csf_debrief_pub.update_debrief_line'
|| ' for PK ' || p_record.DEBRIEF_LINE_ID, 'CSM_DEBRIEF_PARTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_ERROR );
csf_debrief_update_pkg.form_Call (1.0, l_debrief_header_id );
fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_UPDATE:'
|| ' for PK ' || p_record.DEBRIEF_LINE_ID,'CSM_DEBRIEF_PARTS_PKG.APPLY_UPDATE',FND_LOG.LEVEL_EXCEPTION );
END APPLY_UPDATE;
APPLY_INSERT
(
p_record,
p_error_msg,
x_return_status
);
APPLY_UPDATE
(
p_record,
p_error_msg,
x_return_status
);
( 'Delete is not supported for this entity'
|| ' for PK ' || p_record.debrief_line_id ,'CSM_DEBRIEF_PARTS_PKG.APPLY_RECORD',FND_LOG.LEVEL_ERROR);
a fast sync. This procedure will insert the data that came from mobile into the backend tables using
public APIs.
***/
PROCEDURE APPLY_CLIENT_CHANGES
(
p_user_name IN VARCHAR2,
p_tranid IN NUMBER,
p_debug_level IN NUMBER,
x_return_status IN out nocopy VARCHAR2,
p_task_assignment_id IN NUMBER
) IS
L_PROCESS_STATUS varchar2(1);
OPEN c_cur FOR 'SELECT * FROM csf_m_debrief_parts_inq inq WHERE tranid$$ = '||p_tranid||' AND clid$$cs = '''||p_user_name
||''' AND TASK_ASSIGNMENT_ID NOT IN ('||CSM_UTIL_PKG.get_String_fromList(g_processed_assignment_list)||')';
OPEN c_cur FOR 'SELECT * FROM csf_m_debrief_parts_inq WHERE tranid$$ = '||p_tranid||' AND clid$$cs = '''||p_user_name
||''' AND TASK_ASSIGNMENT_ID='||p_task_assignment_id;
/*** Yes -> delete record from inqueue ***/
if G_REJECT_ROW then --IF insert then reject the record as new line id has to be downloaded
CSM_UTIL_PKG.REJECT_RECORD
( p_user_name,
p_tranid,
r_debrief_parts.seqno$$,
r_debrief_parts.debrief_line_id,
g_object_name,
g_pub_name,
l_error_msg,
l_process_status
);
IF (l_process_status = FND_API.G_RET_STS_SUCCESS AND --When reapplying rejet row will not call delete
ASG_DEFER.is_deferred(p_user_name,p_tranid,g_pub_name,r_debrief_parts.seqno$$)=FND_API.G_TRUE) THEN
CSM_UTIL_PKG.DELETE_RECORD
( p_user_name,
p_tranid,
r_debrief_parts.seqno$$,
r_debrief_parts.debrief_line_id,
g_object_name,
g_pub_name,
l_error_msg,
l_process_status
);
CSM_UTIL_PKG.DELETE_RECORD
( p_user_name,
p_tranid,
r_debrief_parts.seqno$$,
r_debrief_parts.debrief_line_id,
g_object_name,
g_pub_name,
l_error_msg,
l_process_status
);
/*** was delete successful? ***/
IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
/*** no -> rollback ***/
CSM_UTIL_PKG.LOG
( 'Deleting from inqueue failed, rolling back to savepoint'
|| ' for PK ' || r_debrief_parts.debrief_line_id,'CSM_DEBRIEF_PARTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR ); -- put PK column here
/*** Record was not processed successfully or delete failed -> defer and reject record ***/
CSM_UTIL_PKG.LOG
( 'Record not processed successfully, deferring and rejecting record'
|| ' for PK ' || r_debrief_parts.debrief_line_id,'CSM_DEBRIEF_PARTS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR ); -- put PK column here