[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cursor c_header_id is select CSF_DEBRIEF_HEADERS_S1.nextval from dual;
cursor c_header_number is select CSF_DEBRIEF_HEADERS_S2.nextval from dual;
select a.resource_id resource_id, b.resource_type resource_type
from CSP_RS_RESOURCES_V b, jtf_rs_resource_extns a
where a.resource_id =b.resource_id
and a.user_id = FND_GLOBAL.USER_ID;
cursor c_dbf_line_id is select CSF_DEBRIEF_LINES_S.nextval from dual;
select primary_uom_code from mtl_system_items_vl
where organization_id = v_org_id
and inventory_item_id = v_item_id;
select creation_date from cs_incidents_all_b where incident_id = v_incident_id;
cursor c_dbf_lineId is select CSF_DEBRIEF_LINES_S.nextval from dual;
select primary_uom_code from mtl_system_items_vl
where organization_id = v_org_id
and inventory_item_id = v_item_id;
PROCEDURE Update_debrief_Expense_line(
p_debrief_line_id IN NUMBER,
p_expense_amount IN NUMBER,
p_currency_code IN VARCHAR2,
p_txn_billing_type_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_business_process_id IN NUMBER,
p_charge_Entry IN VARCHAR2,
p_incident_id IN NUMBER,
p_txnTypeId IN NUMBER,
p_justificationCode IN VARCHAR2,
p_quantity IN NUMBER,
p_uom_code IN VARCHAR2,
p_error_id OUT NOCOPY NUMBER,
p_error OUT NOCOPY VARCHAR2
)IS
l_return_status varchar2(2000);
select DEBRIEF_HEADER_ID from CSF_DEBRIEF_LINES where DEBRIEF_LINE_ID =
v_debrief_line_id ;
select primary_uom_code from mtl_system_items_vl
where organization_id = v_org_id
and inventory_item_id = v_item_id;
CSF_DEBRIEF_PUB.Update_debrief_line(
P_Api_Version_Number => 1.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE,
P_Upd_tskassgnstatus => NULL,
P_Task_Assignment_status => NULL,
P_DEBRIEF_LINE_Rec => P_DEBRIEF_LINE_Rec,
X_Return_Status => l_return_status ,
X_Msg_Count => l_msg_count ,
X_Msg_Data => l_msg_data
);
END Update_debrief_Expense_line;
PROCEDURE Update_debrief_Labor_line(
p_debrief_line_id IN NUMBER,
p_labor_start_date IN DATE,
p_labor_end_date IN DATE,
p_service_date IN DATE,
p_txn_billing_type_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_business_process_id IN NUMBER,
p_charge_Entry IN VARCHAR2,
p_incident_id IN NUMBER,
p_txnTypeId IN NUMBER,
p_quantity IN NUMBER,
p_uom IN VARCHAR2,
p_justificationCode IN VARCHAR2,
p_error_id OUT NOCOPY NUMBER,
p_error OUT NOCOPY VARCHAR2
)IS
l_resource_type VARCHAR2(30);
select DEBRIEF_HEADER_ID from CSF_DEBRIEF_LINES where DEBRIEF_LINE_ID =
v_debrief_line_id;
select primary_uom_code from mtl_system_items_vl
where organization_id = v_org_id
and inventory_item_id = v_item_id;
select creation_date from cs_incidents_all_b where incident_id = v_incident_id;
select a.resource_id resource_id, b.resource_type resource_type
from CSP_RS_RESOURCES_V b, jtf_rs_resource_extns a
where a.resource_id =b.resource_id
and a.user_id = FND_GLOBAL.USER_ID;
CSF_DEBRIEF_PUB.Update_debrief_line(
P_Api_Version_Number => 1.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE,
P_Upd_tskassgnstatus => NULL,
P_Task_Assignment_status => NULL,
P_DEBRIEF_LINE_Rec => P_DEBRIEF_LINE_Rec,
X_Return_Status => l_return_status ,
X_Msg_Count => l_msg_count ,
X_Msg_Data => l_msg_data
);
END Update_debrief_Labor_line;
p_updateIBFlag IN VARCHAR2,
p_srcChangeOwner IN VARCHAR2,
p_srcChangeOwnerToCode IN VARCHAR2,
p_srcReferenceReqd IN VARCHAR2,
p_srcReturnReqd IN VARCHAR2,
p_parentReferenceReqd IN VARCHAR2,
p_srcStatusId IN VARCHAR2,
p_srcStatusName IN VARCHAR2,
p_csiTxnTypeId IN VARCHAR2,
p_subInv IN VARCHAR2,
p_orgId IN VARCHAR2,
p_serviceDate IN VARCHAR2,
p_qty IN VARCHAR2,
p_chgFlag IN VARCHAR2,
p_ibFlag IN VARCHAR2,
p_invFlag IN VARCHAR2,
p_reasonCd IN VARCHAR2,
p_instanceId IN VARCHAR2,
p_parentProductId IN VARCHAR2,
p_partStatusCd IN VARCHAR2,
p_recoveredPartId IN VARCHAR2,
p_retReasonCd IN VARCHAR2,
p_serialNr IN VARCHAR2,
p_lotNr IN VARCHAR2,
p_revisionNr IN VARCHAR2,
p_locatorId IN VARCHAR2,
p_UOM IN VARCHAR2,
p_updateFlag IN NUMBER,
p_dbfLineId IN NUMBER,
p_ret_dbfLine_id OUT NOCOPY NUMBER,
p_error_id OUT NOCOPY NUMBER,
p_error OUT NOCOPY VARCHAR2,
p_return_date IN VARCHAR2
)IS
--l_return_status varchar2(2000);
l_updateIBFlag varchar2(30);
select install_site_use_id,
ship_to_site_use_id
from cs_incidents_all
where incident_id = p_incident_id;
select party_site_id
from hz_party_site_uses
where party_site_use_id = p_install_site_id;
select instance_number
from csi_item_instances
where instance_id = p_instance_id;
select internal_party_id
from csi_install_parameters;
select default_outbound_line_type_id
from oe_transaction_types_all
where transaction_type_id = p_order_type_id
and transaction_type_code = 'ORDER';
select default_inbound_line_type_id
from oe_transaction_types_all
where transaction_type_id = p_order_type_id
and transaction_type_code = 'ORDER';
select meaning
from fnd_lookups
where lookup_type = 'CSF_INTERFACE_STATUS'
and lookup_code = p_code;
select customer_id,customer_account_id from csf_debrief_tasks_v where debrief_header_id = b_dbfId;
select DEBRIEF_HEADER_ID from csf_debrief_headers where debrief_number = v_dbf_nr;
cursor c_dbf_lines is select CSF_DEBRIEF_LINES_S.nextval from dual;
select name
from csi_instance_statuses
where INSTANCE_STATUS_ID = v_partStatusCd;
select creation_date from cs_incidents_all_b where incident_id = v_incident_id;
if p_updateIBFlag = '$$#@' then
l_updateIBFlag := null;
l_updateIBFlag := p_updateIBFlag;
if(p_updateFlag = 1) then
P_DEBRIEF_LINE_Rec.DEBRIEF_LINE_ID := p_dbfLineId;
if (p_updateFlag = 1) then
CSF_DEBRIEF_PUB.Update_debrief_line(
P_Api_Version_Number => 1.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_TRUE,
P_Upd_tskassgnstatus => NULL,
P_Task_Assignment_status => NULL,
P_DEBRIEF_LINE_Rec => P_DEBRIEF_LINE_Rec,
X_Return_Status => l_return_status ,
X_Msg_Count => l_msg_count ,
X_Msg_Data => l_msg_data
);
PROCEDURE UPDATE_CHARGES(
p_dbfLineId in number,
p_incidentId in number,
p_error out NOCOPY varchar2,
p_error_id out NOCOPY number
)IS
l_return_status varchar2(10);
select default_outbound_line_type_id
from oe_transaction_types_all
where transaction_type_id = p_order_type_id
and transaction_type_code = 'ORDER';
select default_inbound_line_type_id
from oe_transaction_types_all
where transaction_type_id = p_order_type_id
and transaction_type_code = 'ORDER';
select meaning
from fnd_lookups
where lookup_type = 'CSF_INTERFACE_STATUS'
and lookup_code = p_code;
SELECT BUSINESS_PROCESS_ID
, inventory_item_id
, UOM_CODE
, QUANTITY
, TXN_BILLING_TYPE_ID
, REMOVED_PRODUCT_ID
, RETURN_REASON_CODE
FROM CSF_DEBRIEF_MAT_LINES_V
WHERE DEBRIEF_LINE_ID = b_dbfLineId;
select line_order_category_code into l_orderCategoryCode
from cs_transaction_types_b
where TRANSACTION_TYPE_ID = (select TRANSACTION_TYPE_ID from CSF_DEBRIEF_MAT_LINES_V
where DEBRIEF_LINE_ID = p_dbfLineId);
select order_type_id into l_order_type_id from cs_business_processes where business_process_id = r_dbfLineRec.BUSINESS_PROCESS_ID;
CSF_DEBRIEF_LINES_PKG.Update_Row(
p_DEBRIEF_LINE_ID => p_dbfLineId,
p_DEBRIEF_HEADER_ID => FND_API.G_MISS_NUM,
p_DEBRIEF_LINE_NUMBER => FND_API.G_MISS_NUM,
p_SERVICE_DATE => FND_API.G_MISS_DATE,
p_BUSINESS_PROCESS_ID => FND_API.G_MISS_NUM,
p_TXN_BILLING_TYPE_ID => FND_API.G_MISS_NUM,
p_INVENTORY_ITEM_ID => FND_API.G_MISS_NUM,
P_INSTANCE_ID => FND_API.G_MISS_NUM,
p_ISSUING_INVENTORY_ORG_ID => FND_API.G_MISS_NUM,
p_RECEIVING_INVENTORY_ORG_ID => FND_API.G_MISS_NUM,
p_ISSUING_SUB_INVENTORY_CODE => FND_API.G_MISS_CHAR,
p_RECEIVING_SUB_INVENTORY_CODE => FND_API.G_MISS_CHAR,
p_ISSUING_LOCATOR_ID => FND_API.G_MISS_NUM,
p_RECEIVING_LOCATOR_ID => FND_API.G_MISS_NUM,
p_PARENT_PRODUCT_ID => FND_API.G_MISS_NUM,
p_REMOVED_PRODUCT_ID => FND_API.G_MISS_NUM,
p_STATUS_OF_RECEIVED_PART => FND_API.G_MISS_CHAR,
p_ITEM_SERIAL_NUMBER => FND_API.G_MISS_CHAR,
p_ITEM_REVISION => FND_API.G_MISS_CHAR,
p_ITEM_LOTNUMBER => FND_API.G_MISS_CHAR,
p_UOM_CODE => FND_API.G_MISS_CHAR,
p_QUANTITY => FND_API.G_MISS_NUM,
p_RMA_HEADER_ID => FND_API.G_MISS_NUM,
p_DISPOSITION_CODE => FND_API.G_MISS_CHAR,
p_MATERIAL_REASON_CODE => FND_API.G_MISS_CHAR,
p_LABOR_REASON_CODE => FND_API.G_MISS_CHAR,
p_EXPENSE_REASON_CODE => FND_API.G_MISS_CHAR,
p_LABOR_START_DATE => FND_API.G_MISS_DATE,
p_LABOR_END_DATE => FND_API.G_MISS_DATE,
p_STARTING_MILEAGE => FND_API.G_MISS_NUM,
p_ENDING_MILEAGE => FND_API.G_MISS_NUM,
p_EXPENSE_AMOUNT => FND_API.G_MISS_NUM,
p_CURRENCY_CODE => FND_API.G_MISS_CHAR,
p_DEBRIEF_LINE_STATUS_ID => FND_API.G_MISS_NUM,
P_RETURN_REASON_CODE => FND_API.G_MISS_CHAR,
p_CHANNEL_CODE => 'WIRELESS_USER',
p_CHARGE_UPLOAD_STATUS => l_interface_status,
p_CHARGE_UPLOAD_MSG_CODE => FND_API.G_MISS_CHAR,
p_CHARGE_UPLOAD_MESSAGE => l_interface_status_meaning,
p_IB_UPDATE_STATUS => FND_API.G_MISS_CHAR,
p_IB_UPDATE_MSG_CODE => FND_API.G_MISS_CHAR,
p_IB_UPDATE_MESSAGE => FND_API.G_MISS_CHAR,
p_SPARE_UPDATE_STATUS => FND_API.G_MISS_CHAR,
p_SPARE_UPDATE_MSG_CODE => FND_API.G_MISS_CHAR,
p_SPARE_UPDATE_MESSAGE => FND_API.G_MISS_CHAR,
p_CREATED_BY => FND_API.G_MISS_NUM,
p_CREATION_DATE => FND_API.G_MISS_DATE,
p_LAST_UPDATED_BY => FND_API.G_MISS_NUM,
p_LAST_UPDATE_DATE => FND_API.G_MISS_DATE,
p_LAST_UPDATE_LOGIN => FND_API.G_MISS_NUM,
p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR);
END UPDATE_CHARGES;
PROCEDURE UPDATE_IB
(
p_dbfLineId in number,
p_incidentId in number,
p_error_id out NOCOPY number,
p_error out NOCOPY varchar2
) IS
l_in_out_flag varchar2(4);
select internal_party_id
from csi_install_parameters;
select meaning
from fnd_lookups
where lookup_type = 'CSF_INTERFACE_STATUS'
and lookup_code = p_code;
select install_site_use_id,
ship_to_site_use_id
from cs_incidents_all
where incident_id = p_incident_id;
select party_site_id
from hz_party_site_uses
where party_site_use_id = p_install_site_id;
select instance_number
from csi_item_instances
where instance_id = p_instance_id;
SELECT service_date
, DEBRIEF_HEADER_ID
, inventory_item_id
, PARENT_PRODUCT_ID
, ITEM_SERIAL_NUMBER
, INVENTORY_ORG_ID
, SUB_INVENTORY_CODE
, INSTANCE_ID
, QUANTITY
, UOM_CODE
FROM CSF_DEBRIEF_MAT_LINES_V
WHERE DEBRIEF_LINE_ID = b_dbfLineId;
select
ctst.sub_type_id sub_type_id ,
ctst.transaction_type_id transaction_type_id_csi ,
cttv.line_order_category_code line_order_category_code
from
cs_transaction_types_vl cttv,
cs_txn_billing_types ctbt,
cs_bus_process_txns cbpt,
cs_business_processes cbp,
csi_txn_sub_types ctst,
csi_txn_types ctt,
csi_instance_statuses cis
where
cttv.transaction_type_id = ctbt.transaction_type_id and
ctbt.transaction_type_id = cbpt.transaction_type_id
and ctbt.transaction_type_id = (select transaction_type_id from CSF_DEBRIEF_MAT_LINES_V where debrief_line_id = b_dbfLineId)
and trunc(sysdate) between nvl(ctbt.start_date_active,to_date(sysdate)) and nvl(ctbt.end_date_active,to_date(sysdate))
and trunc(sysdate) between nvl(cbpt.start_date_active, to_date(sysdate)) and nvl(cbpt.end_date_active, to_date(sysdate))
and cbpt.business_process_id = cbp.business_process_id
and cbpt.business_process_id = (select business_process_id from CSF_DEBRIEF_MAT_LINES_V where debrief_line_id = b_dbfLineId)
and cbp.field_service_flag = 'Y'
and ctbt.billing_type = 'M'
and ctst.cs_transaction_type_id = cttv.transaction_type_id
and ctt.source_application_id=513
and ctt.transaction_type_id = ctst.transaction_type_id
and ctst.src_status_id = cis.instance_status_id(+)
and (nvl(ctst.update_ib_flag, 'N') = 'N'
or ( ctst.update_ib_flag = 'Y'
and trunc(sysdate) between nvl(cis.start_date_active,trunc(sysdate)) and nvl(cis.end_date_active,trunc(sysdate))
and nvl(cis.terminated_flag, 'N') <> 'Y'
and ctst.src_change_owner = 'Y'
and nvl(ctst.src_return_reqd, 'N') = 'N'
and (
(ctst.src_change_owner_to_code = 'I'
and nvl(ctst.parent_reference_reqd, 'N') = 'N'
and cttv.line_order_category_code='RETURN')
or
(ctst.src_change_owner_to_code = 'E'
and ctst.src_reference_reqd = 'Y'
and cttv.line_order_category_code='ORDER')
)
)
) ;
select customer_id,customer_account_id from csf_debrief_tasks_v where debrief_header_id = b_dbfId;
csf_ib.update_install_base(
p_api_version => 1.0,
p_init_msg_list => null,
p_commit => null,
p_validation_level => null,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_new_instance_id => l_new_instance_id,
p_in_out_flag => l_in_out_flag,
p_transaction_type_id => l_transaction_type_id_csi,
p_txn_sub_type_id => l_txn_sub_type_id,
p_instance_id => l_instance_id,
p_inventory_item_id => l_inventory_item_id,
p_inv_organization_id => l_inv_organization_id,
p_inv_subinventory_name => l_inv_subinventory_name,
p_quantity => l_quantity,
p_inv_master_organization_id => l_inv_master_organization_id,
p_mfg_serial_number_flag => l_mfg_serial_number_flag,
p_serial_number => l_serial_number,
p_lot_number => l_lot_number,
p_unit_of_measure => l_uom,
p_party_id => l_party_id,
p_party_account_id => l_party_account_id,
p_party_site_id => l_party_site_id,
p_parent_instance_id => l_parent_instance_id) ;
CSF_DEBRIEF_LINES_PKG.Update_Row(
p_DEBRIEF_LINE_ID => p_dbfLineId,
p_DEBRIEF_HEADER_ID => FND_API.G_MISS_NUM,
p_DEBRIEF_LINE_NUMBER => FND_API.G_MISS_NUM,
p_SERVICE_DATE => FND_API.G_MISS_DATE,
p_BUSINESS_PROCESS_ID => FND_API.G_MISS_NUM,
p_TXN_BILLING_TYPE_ID => FND_API.G_MISS_NUM,
p_INVENTORY_ITEM_ID => FND_API.G_MISS_NUM,
P_INSTANCE_ID => FND_API.G_MISS_NUM,
p_ISSUING_INVENTORY_ORG_ID => FND_API.G_MISS_NUM,
p_RECEIVING_INVENTORY_ORG_ID => FND_API.G_MISS_NUM,
p_ISSUING_SUB_INVENTORY_CODE => FND_API.G_MISS_CHAR,
p_RECEIVING_SUB_INVENTORY_CODE => FND_API.G_MISS_CHAR,
p_ISSUING_LOCATOR_ID => FND_API.G_MISS_NUM,
p_RECEIVING_LOCATOR_ID => FND_API.G_MISS_NUM,
p_PARENT_PRODUCT_ID => FND_API.G_MISS_NUM,
p_REMOVED_PRODUCT_ID => FND_API.G_MISS_NUM,
p_STATUS_OF_RECEIVED_PART => FND_API.G_MISS_CHAR,
p_ITEM_SERIAL_NUMBER => FND_API.G_MISS_CHAR,
p_ITEM_REVISION => FND_API.G_MISS_CHAR,
p_ITEM_LOTNUMBER => FND_API.G_MISS_CHAR,
p_UOM_CODE => FND_API.G_MISS_CHAR,
p_QUANTITY => FND_API.G_MISS_NUM,
p_RMA_HEADER_ID => FND_API.G_MISS_NUM,
p_DISPOSITION_CODE => FND_API.G_MISS_CHAR,
p_MATERIAL_REASON_CODE => FND_API.G_MISS_CHAR,
p_LABOR_REASON_CODE => FND_API.G_MISS_CHAR,
p_EXPENSE_REASON_CODE => FND_API.G_MISS_CHAR,
p_LABOR_START_DATE => FND_API.G_MISS_DATE,
p_LABOR_END_DATE => FND_API.G_MISS_DATE,
p_STARTING_MILEAGE => FND_API.G_MISS_NUM,
p_ENDING_MILEAGE => FND_API.G_MISS_NUM,
p_EXPENSE_AMOUNT => FND_API.G_MISS_NUM,
p_CURRENCY_CODE => FND_API.G_MISS_CHAR,
p_DEBRIEF_LINE_STATUS_ID => FND_API.G_MISS_NUM,
P_RETURN_REASON_CODE => FND_API.G_MISS_CHAR,
p_CHANNEL_CODE => 'WIRELESS_USER',
p_CHARGE_UPLOAD_STATUS => FND_API.G_MISS_CHAR,
p_CHARGE_UPLOAD_MSG_CODE => FND_API.G_MISS_CHAR,
p_CHARGE_UPLOAD_MESSAGE => FND_API.G_MISS_CHAR,
p_IB_UPDATE_STATUS => l_interface_status,
p_IB_UPDATE_MSG_CODE => FND_API.G_MISS_CHAR,
p_IB_UPDATE_MESSAGE => l_interface_status_meaning,
p_SPARE_UPDATE_STATUS => FND_API.G_MISS_CHAR,
p_SPARE_UPDATE_MSG_CODE => FND_API.G_MISS_CHAR,
p_SPARE_UPDATE_MESSAGE => FND_API.G_MISS_CHAR,
p_CREATED_BY => FND_API.G_MISS_NUM,
p_CREATION_DATE => FND_API.G_MISS_DATE,
p_LAST_UPDATED_BY => FND_API.G_MISS_NUM,
p_LAST_UPDATE_DATE => FND_API.G_MISS_DATE,
p_LAST_UPDATE_LOGIN => FND_API.G_MISS_NUM,
p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR);
END UPDATE_IB;
PROCEDURE UPDATE_SPARES(
p_dbfLineId in number,
p_dbfNr in varchar2,
p_error_id out NOCOPY number,
p_error out NOCOPY varchar2
)IS
l_transaction_type_id number ;
select meaning
from fnd_lookups
where lookup_type = 'CSF_INTERFACE_STATUS'
and lookup_code = p_code;
SELECT DEBRIEF_HEADER_ID
, inventory_item_id
, PARENT_PRODUCT_ID
, ITEM_SERIAL_NUMBER
, INVENTORY_ORG_ID
, SUB_INVENTORY_CODE
, INSTANCE_ID
, QUANTITY
, UOM_CODE
, LOCATOR
, ITEM_REVISION
, ITEM_LOTNUMBER
FROM CSF_DEBRIEF_MAT_LINES_V
WHERE DEBRIEF_LINE_ID = b_dbfLineId;
select
ctst.sub_type_id sub_type_id ,
ctst.transaction_type_id transaction_type_id_csi ,
cttv.line_order_category_code line_order_category_code
from
cs_transaction_types_vl cttv,
cs_txn_billing_types ctbt,
cs_bus_process_txns cbpt,
cs_business_processes cbp,
csi_txn_sub_types ctst,
csi_txn_types ctt,
csi_instance_statuses cis
where
cttv.transaction_type_id = ctbt.transaction_type_id and
ctbt.transaction_type_id = cbpt.transaction_type_id
and ctbt.transaction_type_id = (select transaction_type_id from CSF_DEBRIEF_MAT_LINES_V where debrief_line_id = b_dbfLineId)
and trunc(sysdate) between nvl(ctbt.start_date_active,to_date(sysdate)) and nvl(ctbt.end_date_active,to_date(sysdate))
and trunc(sysdate) between nvl(cbpt.start_date_active, to_date(sysdate)) and nvl(cbpt.end_date_active, to_date(sysdate))
and cbpt.business_process_id = cbp.business_process_id
and cbpt.business_process_id = (select business_process_id from CSF_DEBRIEF_MAT_LINES_V where debrief_line_id = b_dbfLineId)
and cbp.field_service_flag = 'Y'
and ctbt.billing_type = 'M'
and ctst.cs_transaction_type_id = cttv.transaction_type_id
and ctt.source_application_id=513
and ctt.transaction_type_id = ctst.transaction_type_id
and ctst.src_status_id = cis.instance_status_id(+)
and (nvl(ctst.update_ib_flag, 'N') = 'N'
or ( ctst.update_ib_flag = 'Y'
and trunc(sysdate) between nvl(cis.start_date_active,trunc(sysdate)) and nvl(cis.end_date_active,trunc(sysdate))
and nvl(cis.terminated_flag, 'N') <> 'Y'
and ctst.src_change_owner = 'Y'
and nvl(ctst.src_return_reqd, 'N') = 'N'
and (
(ctst.src_change_owner_to_code = 'I'
and nvl(ctst.parent_reference_reqd, 'N') = 'N'
and cttv.line_order_category_code='RETURN')
or
(ctst.src_change_owner_to_code = 'E'
and ctst.src_reference_reqd = 'Y'
and cttv.line_order_category_code='ORDER')
)
)
) ;
CSF_DEBRIEF_LINES_PKG.Update_Row(
p_DEBRIEF_LINE_ID => p_dbfLineId,
p_DEBRIEF_HEADER_ID => FND_API.G_MISS_NUM,
p_DEBRIEF_LINE_NUMBER => FND_API.G_MISS_NUM,
p_SERVICE_DATE => FND_API.G_MISS_DATE,
p_BUSINESS_PROCESS_ID => FND_API.G_MISS_NUM,
p_TXN_BILLING_TYPE_ID => FND_API.G_MISS_NUM,
p_INVENTORY_ITEM_ID => FND_API.G_MISS_NUM,
P_INSTANCE_ID => FND_API.G_MISS_NUM,
p_ISSUING_INVENTORY_ORG_ID => FND_API.G_MISS_NUM,
p_RECEIVING_INVENTORY_ORG_ID => FND_API.G_MISS_NUM,
p_ISSUING_SUB_INVENTORY_CODE => FND_API.G_MISS_CHAR,
p_RECEIVING_SUB_INVENTORY_CODE => FND_API.G_MISS_CHAR,
p_ISSUING_LOCATOR_ID => FND_API.G_MISS_NUM,
p_RECEIVING_LOCATOR_ID => FND_API.G_MISS_NUM,
p_PARENT_PRODUCT_ID => FND_API.G_MISS_NUM,
p_REMOVED_PRODUCT_ID => FND_API.G_MISS_NUM,
p_STATUS_OF_RECEIVED_PART => FND_API.G_MISS_CHAR,
p_ITEM_SERIAL_NUMBER => FND_API.G_MISS_CHAR,
p_ITEM_REVISION => FND_API.G_MISS_CHAR,
p_ITEM_LOTNUMBER => FND_API.G_MISS_CHAR,
p_UOM_CODE => FND_API.G_MISS_CHAR,
p_QUANTITY => FND_API.G_MISS_NUM,
p_RMA_HEADER_ID => FND_API.G_MISS_NUM,
p_DISPOSITION_CODE => FND_API.G_MISS_CHAR,
p_MATERIAL_REASON_CODE => FND_API.G_MISS_CHAR,
p_LABOR_REASON_CODE => FND_API.G_MISS_CHAR,
p_EXPENSE_REASON_CODE => FND_API.G_MISS_CHAR,
p_LABOR_START_DATE => FND_API.G_MISS_DATE,
p_LABOR_END_DATE => FND_API.G_MISS_DATE,
p_STARTING_MILEAGE => FND_API.G_MISS_NUM,
p_ENDING_MILEAGE => FND_API.G_MISS_NUM,
p_EXPENSE_AMOUNT => FND_API.G_MISS_NUM,
p_CURRENCY_CODE => FND_API.G_MISS_CHAR,
p_DEBRIEF_LINE_STATUS_ID => FND_API.G_MISS_NUM,
P_RETURN_REASON_CODE => FND_API.G_MISS_CHAR,
p_CHANNEL_CODE => 'WIRELESS_USER',
p_CHARGE_UPLOAD_STATUS => FND_API.G_MISS_CHAR,
p_CHARGE_UPLOAD_MSG_CODE => FND_API.G_MISS_CHAR,
p_CHARGE_UPLOAD_MESSAGE => FND_API.G_MISS_CHAR,
p_IB_UPDATE_STATUS => FND_API.G_MISS_CHAR,
p_IB_UPDATE_MSG_CODE => FND_API.G_MISS_CHAR,
p_IB_UPDATE_MESSAGE => FND_API.G_MISS_CHAR,
p_SPARE_UPDATE_STATUS => l_interface_status,
p_SPARE_UPDATE_MSG_CODE => FND_API.G_MISS_CHAR,
p_SPARE_UPDATE_MESSAGE => l_interface_status_meaning,
p_CREATED_BY => FND_API.G_MISS_NUM,
p_CREATION_DATE => FND_API.G_MISS_DATE,
p_LAST_UPDATED_BY => FND_API.G_MISS_NUM,
p_LAST_UPDATE_DATE => FND_API.G_MISS_DATE,
p_LAST_UPDATE_LOGIN => FND_API.G_MISS_NUM,
p_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
p_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR);
END UPDATE_SPARES;
/* Updates info for travel debrief */
PROCEDURE Create_Travel_Debrief
( p_task_assignment_id IN NUMBER
, p_debrief_header_id IN NUMBER
, p_start_date IN DATE
, p_end_date IN DATE
, p_distance IN NUMBER
, p_error_id OUT NOCOPY NUMBER
, p_error OUT NOCOPY VARCHAR2
)
IS
P_DEBRIEF_Rec CSF_DEBRIEF_PUB.DEBRIEF_Rec_Type;
select
DEBRIEF_HEADER_ID, DEBRIEF_NUMBER,
DEBRIEF_DATE, DEBRIEF_STATUS_ID,
TASK_ASSIGNMENT_ID, CREATED_BY,
CREATION_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
ATTRIBUTE_CATEGORY, object_version_number,
TRAVEL_START_TIME, TRAVEL_END_TIME,
TRAVEL_DISTANCE_IN_KM
from CSF_DEBRIEF_HEADERS
where DEBRIEF_HEADER_ID = v_hrd_id
and TASK_ASSIGNMENT_ID = v_asgn_id;
/* changing the values to be updated */
P_DEBRIEF_Rec.DEBRIEF_HEADER_ID := p_debrief_header_id;
CSF_DEBRIEF_PUB.Update_DEBRIEF(
P_Api_Version_Number => 1.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_TRUE,
P_DEBRIEF_Rec => P_DEBRIEF_Rec,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
PROCEDURE Update_Debrief_Header
( p_DEBRIEF_ID IN NUMBER,
p_DEBRIEF_NUMBER IN VARCHAR2,
p_DEBRIEF_DATE IN DATE,
p_DEBRIEF_STATUS_ID IN NUMBER,
p_TASK_ASSIGNMENT_ID IN NUMBER,
p_CREATED_BY IN NUMBER,
p_CREATION_DATE IN DATE,
p_LAST_UPDATED_BY IN NUMBER,
p_LAST_UPDATE_DATE IN DATE,
p_LAST_UPDATE_LOGIN IN NUMBER,
p_ATTRIBUTE1 IN VARCHAR2,
p_ATTRIBUTE2 IN VARCHAR2,
p_ATTRIBUTE3 IN VARCHAR2,
p_ATTRIBUTE4 IN VARCHAR2,
p_ATTRIBUTE5 IN VARCHAR2,
p_ATTRIBUTE6 IN VARCHAR2,
p_ATTRIBUTE7 IN VARCHAR2,
p_ATTRIBUTE8 IN VARCHAR2,
p_ATTRIBUTE9 IN VARCHAR2,
p_ATTRIBUTE10 IN VARCHAR2,
p_ATTRIBUTE11 IN VARCHAR2,
p_ATTRIBUTE12 IN VARCHAR2,
p_ATTRIBUTE13 IN VARCHAR2,
p_ATTRIBUTE14 IN VARCHAR2,
p_ATTRIBUTE15 IN VARCHAR2,
p_ATTRIBUTE_CATEGORY IN VARCHAR2,
p_return_status OUT NOCOPY VARCHAR2,
p_error_count OUT NOCOPY NUMBER,
p_error OUT NOCOPY VARCHAR2
)
IS
P_DEBRIEF_Rec CSF_DEBRIEF_PUB.DEBRIEF_Rec_Type;
CSF_DEBRIEF_PUB.Update_debrief (
P_Api_Version_Number => 1.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE,
P_DEBRIEF_Rec => P_DEBRIEF_Rec,
X_Return_Status => p_return_status,
X_Msg_Count => p_error_count,
X_Msg_Data => l_error_msg
);
END Update_Debrief_Header;