[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Called from : Called from Update API */
/* */
/* STANDARD PARAMETERS */
/* In Parameters : */
/* p_message Required Debug message that needs to be logged */
/* p_mod_name Required Module name */
/* p_severity_level Required Severity level */
/* Output Parameters: */
/* NON-STANDARD PARAMETERS */
/* In Parameters */
/* Out parameters */
/* Change Hist : */
/* 09/20/03 vlakaman Initial Creation. */
/*-------------------------------------------------------------------------------------*/
PROCEDURE DEBUG(p_message IN VARCHAR2,
p_mod_name IN VARCHAR2,
p_severity_level IN NUMBER) IS
-- Variables used in FND Log
l_stat_level NUMBER := Fnd_Log.LEVEL_STATEMENT;
SELECT party_site_id
FROM hz_party_site_uses
WHERE party_site_use_id = p_party_site_use_id;
SELECT party_id
FROM hz_party_sites
WHERE party_site_id = p_party_site_id;
r_service_request_rec.last_update_program_code := 'CSD_REPAIR_ORDER_FORM';
/* description : procedure used to create/update */
/* delete charge lines */
/* */
/*--------------------------------------------------*/
PROCEDURE process_charge_lines(p_api_version IN NUMBER,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_action IN VARCHAR2,
p_Charges_Rec IN Cs_Charge_Details_Pub.Charges_Rec_Type,
x_estimate_detail_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_CHARGE_LINES';
select invoice_to_org_id, ship_to_org_id
into l_invoice_to_org_id, l_ship_to_org_id
from cs_estimate_details
where estimate_detail_id = l_Charges_Rec.estimate_detail_id;
Service Request level and it would error out if the bill to address selected at product
line level is of related party.
*/
IF (l_Charges_Rec.invoice_to_org_id IS NOT NULL AND
l_Charges_Rec.invoice_to_org_id <> Fnd_Api.G_MISS_NUM)
THEN
BEGIN
SELECT party_id
INTO l_Charges_Rec.bill_to_party_id
FROM hz_party_sites
WHERE party_site_id = l_Charges_Rec.invoice_to_org_id;
Service Request level and it would error out if the ship to address selected at product
line level is of related party.
*/
IF (l_Charges_Rec.ship_to_org_id IS NOT NULL AND
l_Charges_Rec.ship_to_org_id <> Fnd_Api.G_MISS_NUM)
THEN
BEGIN
SELECT party_id
INTO l_Charges_Rec.ship_to_party_id
FROM hz_party_sites
WHERE party_site_id = l_Charges_Rec.ship_to_org_id;
ELSIF p_action = 'UPDATE'
THEN
Debug('Creating the charge lines ', l_mod_name, 1);
SELECT business_process_id
INTO l_Charges_Rec.business_process_id
FROM cs_estimate_details
WHERE estimate_detail_id =
l_Charges_Rec.estimate_detail_id;
Cs_Charge_Details_Pub.Update_Charge_Details(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_transaction_control => Fnd_Api.G_TRUE,
p_Charges_Rec => l_Charges_Rec,
x_object_version_number => x_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
Debug('Return Status from Update_Charge_Details' ||
x_return_status,
l_mod_name,
1);
Debug('update_charge_details failed', l_mod_name, 1);
ELSIF p_action = 'DELETE'
THEN
Debug('l_estimate_detail_id =' || l_estimate_detail_id,
l_mod_name,
1);
Cs_Charge_Details_Pub.Delete_Charge_Details(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_transaction_control => Fnd_Api.G_TRUE,
p_estimate_detail_id => l_estimate_detail_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
Debug('Return Status from Delete_Charge_Details' ||
x_return_status,
l_mod_name,
1);
Debug('Delete_Charge_Details failed ', l_mod_name, 1);
SELECT cr.serial_number,
wdd.delivery_detail_id,
wdd.requested_quantity,
mtl.serial_number_control_code
FROM csd_repairs cr,
mtl_system_items_b mtl,
cs_estimate_details ced,
wsh_delivery_details wdd,
--Changed to view from table, bug: 4341784
wsh_delivery_assignments_v wda
WHERE cr.repair_line_id = ced.original_source_id
AND ced.original_source_code = 'DR'
AND ced.order_line_id = wdd.source_line_id
AND wdd.SOURCE_CODE = 'OE' /*Fixed for bug#5846054*/
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.released_status = 'Y'
AND wda.delivery_id = p_del_id
AND wdd.inventory_item_id = mtl.inventory_item_id
AND wdd.ship_from_location_id = mtl.organization_id;
SELECT '*'
INTO l_dummy
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
Csd_Gen_Utility_Pvt.ADD('Calling Update_Shipping_Attributes');
Wsh_Delivery_Details_Pub.Update_Shipping_Attributes(p_api_version_number => 1.0,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_changed_attributes => changed_attributes,
p_source_code => source_code);
Csd_Gen_Utility_Pvt.ADD('x_return_status(Update_Shipping_Attributes )=' ||
x_return_status);
Csd_Gen_Utility_Pvt.ADD('Update_Shipping_Attributes failed');
Csd_Gen_Utility_Pvt.ADD('Error Msg from Update_Shipping_Attributes');
'CSD_UPDATE_SHIPPING_FAILED');
END IF; --end of update_shipping_attributes
SELECT PICKING_RULE_ID
FROM WSH_PICKING_RULES
WHERE PICKING_RULE_ID = x_rule_id
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE) AND
NVL(END_DATE_ACTIVE, SYSDATE + 1);
SELECT batch_id
FROM WSH_PICKING_BATCHES
WHERE NAME = x_batch_name;
SELECT header_id
FROM MTL_TXN_REQUEST_HEADERS
WHERE request_number = x_batch_name;
SELECT a.sold_to_org_id,
a.order_type_id,
a.source_document_type_id
FROM oe_order_headers_all a
WHERE a.header_id = p_ord_header_id;
SELECT b.delivery_id,
a.delivery_detail_id,
c.serial_number_control_code,
/* Fix for bug# 4433942 */
c.lot_control_code,
c.revision_qty_control_code,
c.reservable_type,
a.organization_id,
a.inventory_item_id,
c.segment1 item_name,
p.organization_code
FROM wsh_delivery_details a,
--Changed to view from table, bug: 4341784
wsh_delivery_assignments_v b,
mtl_system_items c,
mtl_parameters p
WHERE a.delivery_detail_id = b.delivery_detail_id
AND a.inventory_item_id = c.inventory_item_id
AND a.organization_id = c.organization_id
AND a.released_status = 'Y'
AND a.SOURCE_CODE = 'OE' /*Fixed for bug#5846054*/
AND a.source_header_id = p_ord_header_id
AND a.source_line_id = p_ord_line_id
AND a.organization_id = p.organization_id;
SELECT org_id
FROM oe_order_headers_all
WHERE header_id = p_header_id;
select reservation_id from mtl_Serial_numbers
where serial_number = p_srl_num
and inventory_item_Id = p_item_id;
select delivery_detail_id
from wsh_Delivery_Details
where source_header_id = p_order_header_id and source_line_id = p_order_line_id;
Aso_Order_Int.Update_order(P_Api_Version_Number => 1.0,
P_Init_Msg_List => Fnd_Api.G_TRUE,
P_Commit => Fnd_Api.G_FALSE,
P_Qte_Rec => l_header_rec,
P_Qte_Line_Tbl => l_line_tbl,
P_Qte_Line_Dtl_Tbl => l_line_dtl_tbl,
P_Line_Shipment_Tbl => l_ln_shipment_tbl,
P_Header_Payment_Tbl => l_hd_payment_tbl,
P_Line_Price_Adj_Tbl => l_line_price_adj_tbl,
P_Control_Rec => l_control_rec,
X_Order_Header_Rec => x_order_header_rec,
X_Order_Line_Tbl => x_order_line_tbl,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data);
Debug('Return Status from Update_order' || x_return_status,
l_mod_name,
1);
Debug('ASO_ORDER_INT.UPDATE_ORDER failed', l_mod_name, 1);
--'S' menas "Selected ship line only"
--if the profile value set to "Selected ship line only", then pass the delivery_detail_id
--else it will pass null value to delivery_detail_id.
If (fnd_profile.value('CSD_PROCESS_AUTO_PICK_RELEASE') = 'S') then
open get_delivery_detail_id(p_order_rec.order_header_id, p_order_rec.order_line_id);
--R12 : SU Putting Insert statement in Begin and End Block
Begin
SELECT NVL(DOCUMENT_SET_ID, l_document_set_id),
'I', -- Include backorders also 11.5.10 saupadhy: BACKORDERS_ONLY_FLAG,
NVL(EXISTING_RSVS_ONLY_FLAG, 'N'),
SHIPMENT_PRIORITY_CODE,
p_order_rec.order_header_id,
l_delivery_detail_id, --bug#6071005
l_order_type_id,
NULL, --SHIP_FROM_LOCATION_ID,
l_customer_id,
NULL, --SHIP_TO_LOCATION_ID,
SHIP_METHOD_CODE,
NVL(l_Sub_Inventory, PICK_FROM_SUBINVENTORY),
NVL(l_locator_id,PICK_FROM_LOCATOR_ID), -- Fix for Enh Req#3948563 NULL, --PICK_FROM_LOCATOR_ID,
DEFAULT_STAGE_SUBINVENTORY,
DEFAULT_STAGE_LOCATOR_ID,
AUTODETAIL_PR_FLAG,
AUTO_PICK_CONFIRM_FLAG,
SHIP_SET_NUMBER,
NULL, --INVENTORY_ITEM_ID,
NULL,
NULL,
NULL,
NULL,
PICK_GROUPING_RULE_ID,
PICK_SEQUENCE_RULE_ID,
NVL(l_Organization_Id, ORGANIZATION_ID),
PROJECT_ID,
TASK_ID,
INCLUDE_PLANNED_LINES,
AUTOCREATE_DELIVERY_FLAG
INTO
l_batch_rec.Document_set_id,
l_batch_Rec.Backorders_only_flag,
l_batch_rec.Existing_Rsvs_Only_Flag,
l_batch_rec.Shipment_Priority_Code,
l_batch_rec.order_header_id,
l_batch_rec.delivery_detail_id, --bug#6071005
l_batch_rec.order_type_id,
l_batch_rec.ship_from_location_id,
l_batch_rec.customer_id,
l_batch_rec.ship_to_location_id,
l_batch_rec.ship_method_code,
l_batch_rec.pick_from_subinventory,
l_batch_rec.pick_from_locator_id,
l_batch_rec.default_stage_subinventory,
l_batch_rec.default_stage_locator_id,
l_batch_rec.autodetail_pr_flag,
l_batch_rec.auto_pick_confirm_flag,
l_batch_rec.ship_set_number,
l_batch_rec.inventory_item_id,
l_batch_rec.From_requested_date,
l_batch_rec.to_Requested_date,
l_batch_rec.from_scheduled_ship_date,
l_batch_Rec.to_scheduled_ship_date,
l_batch_Rec.pick_grouping_rule_id,
l_batch_rec.pick_sequence_rule_id,
l_batch_rec.organization_id,
l_batch_rec.project_id,
l_batch_rec.task_id,
l_batch_rec.Include_Planned_Lines,
l_batch_rec.Autocreate_Delivery_Flag
FROM WSH_PICKING_RULES
WHERE PICKING_RULE_ID = p_order_rec.PICKING_RULE_ID;
Debug(' Picking rules insert failed ',l_mod_name, 1);
Debug('Calling Update_Shipping_Attributes',
l_mod_name,
1);
Wsh_Delivery_Details_Pub.Update_Shipping_Attributes(p_api_version_number => 1.0,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_changed_attributes => changed_attributes,
p_source_code => source_code);
Debug('x_return_status(Update_Shipping_Attributes )=' ||
x_return_status,
l_mod_name,
1);
Debug('Update_Shipping_Attributes failed',
l_mod_name,
1);
Call API WSH_DELIVERY_DETAILS_PUB.Update_Shipping_Attributes
and update with serial_number and quantity.
End If;
Call API WSH_DELIVERY_DETAILS_PUB.Update_Shipping_Attributes and
Update Serial_Number, Quantity ( will be 1 for serial controlled
items), lot_number, Revision and subinventory
Else if item is non serial controlled then
Call API WSH_DELIVERY_DETAILS_PUB.Update_Shipping_Attributes
Values for Quantity, Lot_Number, Revision and Sub Inventory
should be pulled from UI.
Update Quantity, Lot_Number, Revision and Sub-Inventory
End If ;
In case of serial controlled at SO issue item, update the serial number on the
delivery line details
*/
BEGIN
IF( i.RESERVABLE_TYPE = 1) Then /*Item is reservable */
IF i.serial_number_control_code = C_SRL_NUM_Cnt_Code_SO_Issue Then
source_code := 'OE'; -- The only source code that should be used by the API
csd_gen_utility_pvt.ADD('Calling Update_Shipping_Attributes');
WSH_DELIVERY_DETAILS_PUB.Update_Shipping_Attributes(
p_api_version_number => 1.0,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_changed_attributes => changed_attributes,
p_source_code => source_code);
csd_gen_utility_pvt.ADD('x_return_status(Update_Shipping_Attributes )='||x_return_status);
csd_gen_utility_pvt.ADD('Update_Shipping_Attributes failed');
select Lot_Number, Revision, current_SubInventory_code
into l_lot_number ,l_revision ,l_sub_inventory
from mtl_serial_numbers
where serial_number = l_shipped_serial_number
AND inventory_item_id = i.inventory_item_id
AND current_organization_id = i.organization_id;
SELECT msi.secondary_inventory_name
into l_sub_inventory
FROM mtl_secondary_inventories msi
where msi.organization_id = i.organization_id
AND msi.secondary_inventory_name = p_product_txn_rec.sub_inventory
AND NVL(msi.DISABLE_DATE,SYSDATE+1) > SYSDATE
AND msi.QUANTITY_TRACKED = 1;
/* Till here all the validations are done now we can call update attribute API */
source_code := 'OE'; /*The only source code that should be used by the API */
csd_gen_utility_pvt.ADD('Calling Update_Shipping_Attributes');
WSH_DELIVERY_DETAILS_PUB.Update_Shipping_Attributes(
p_api_version_number => 1.0,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_changed_attributes => changed_attributes,
p_source_code => source_code);
csd_gen_utility_pvt.ADD('x_return_status(Update_Shipping_Attributes )='||x_return_status);
csd_gen_utility_pvt.ADD('Update_Shipping_Attributes failed');
Debug('Error Msg from Update_Shipping_Attributes',
l_mod_name,
1);
Fnd_Message.SET_NAME('CSD','CSD_UPDATE_SHIPPING_FAILED');
select 'N'
into l_shipped_flag
from wsh_delivery_details wdd
where wdd.delivery_detail_id = i.delivery_detail_id
and wdd.released_status <> 'C';
/* 1. It may from the ON-INSERT trigger in repair */
/* order form for creating ptoduct txn manually. */
/* 2. It may be also called from create_default_txn */
/* API to create product txn at the time creating */
/* repair orders */
/*------------------------------------------------------------------*/
PROCEDURE create_product_txn(p_api_version IN NUMBER,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
x_product_txn_rec IN OUT NOCOPY PRODUCT_TXN_REC,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'CREATE_PRODUCT_TXN';
SELECT customer_id, account_id
FROM cs_incidents_all_b
WHERE incident_id = p_incident_id;
SELECT estimate_detail_id, object_version_number
FROM cs_estimate_details
WHERE source_id = p_rep_line_id
AND source_code = 'DR'
AND interface_to_oe_flag = 'N'
AND order_header_id IS NULL
AND order_line_id IS NULL;
SELECT COUNT(*) p_count
FROM PJM_PROJECTS_ORG_OU_SECURE_V --bug#13472453 PJM_PROJECTS_ORG_V
WHERE project_id = l_project_id and inventory_organization_id = l_p_ship_from_org_id;
SELECT b.order_line_id, a.ship_from_org_id
FROM oe_order_lines_all a, cs_estimate_details b
WHERE a.line_id = b.order_line_id
AND b.estimate_detail_id = l_est_detail_id;
SELECT account_id
FROM cs_incidents_all_b cs, csd_repairs csd
WHERE cs.incident_id = csd.incident_id
AND repair_line_id = l_repair_line_id;
SELECT 'x'
FROM wsh_picking_rules
WHERE picking_rule_id = p_pick_rule_id
AND SYSDATE BETWEEN NVL(start_Date_Active, SYSDATE) AND
NVL(end_Date_active, SYSDATE + 1);
select distinct
hp.party_site_id
from hz_party_sites_v hp,
hz_parties hz,
hz_cust_acct_sites_all hca,
hz_cust_site_uses_all hcsu,
cs_incidents_all_b cs
where hcsu.site_use_code = p_site_use_type
and cs.incident_id = p_incident_id
and hp.status = 'A'
and hcsu.status = 'A'
and hp.party_id = hz.party_id
and hca.party_site_id = hp.party_site_id
and hca.cust_account_id = cs.account_id
and hcsu.cust_acct_site_id = hca.cust_acct_site_id
and hca.org_id = cs.org_id
and hcsu.primary_flag = 'Y'
and rownum = 1;
SELECT 'X'
INTO l_check
FROM fnd_lookups
WHERE lookup_type = 'CSD_PRODUCT_TXN_STATUS'
AND lookup_code = x_product_txn_rec.PROD_TXN_STATUS;
SELECT incident_id,
original_source_reference,
original_source_header_id,
original_source_line_id
INTO l_incident_id,
l_orig_src_reference,
l_orig_src_header_id,
l_orig_src_line_id
FROM csd_repairs
WHERE repair_line_id = x_product_txn_rec.repair_line_id;
SELECT transaction_type_id
INTO l_transaction_type_id
FROM cs_txn_billing_types
WHERE txn_billing_type_id =
x_product_txn_rec.txn_billing_type_id;
SELECT cov.actual_coverage_id,
-- cov.coverage_name, -- Commented for bugfix 3617932
ent.txn_group_id
INTO l_coverage_id,
-- l_coverage_name, -- Commented for bugfix 3617932
l_txn_group_id
FROM oks_ent_coverages_v cov,
oks_ent_txn_groups_v ent
WHERE cov.contract_line_id =
x_product_txn_rec.contract_id
AND cov.actual_coverage_id = ent.coverage_id;
SELECT cov.actual_coverage_id,
-- cov.coverage_name, -- Commented for bugfix 3617932
ent.txn_group_id
INTO l_coverage_id,
-- l_coverage_name, -- Commented for bugfix 3617932
l_txn_group_id
FROM oks_ent_coverages_v cov,
oks_ent_txn_groups_v ent
WHERE cov.contract_line_id =
x_product_txn_rec.contract_id
AND cov.actual_coverage_id = ent.coverage_id
AND ent.business_process_id = l_bus_process_id;
SELECT currency_code
INTO l_curr_code
FROM oe_price_lists
WHERE price_list_id = x_product_txn_rec.price_list_id;
Debug('Call csd_product_transactions_pkg.insert_row to insert prod txns',
l_mod_name,
1);
Csd_Product_Transactions_Pkg.INSERT_ROW(px_PRODUCT_TRANSACTION_ID => x_product_txn_rec.PRODUCT_TRANSACTION_ID,
p_REPAIR_LINE_ID => x_product_txn_rec.REPAIR_LINE_ID,
p_ESTIMATE_DETAIL_ID => x_estimate_detail_id,
p_ACTION_TYPE => x_product_txn_rec.ACTION_TYPE,
p_ACTION_CODE => x_product_txn_rec.ACTION_CODE,
p_LOT_NUMBER => x_product_txn_rec.LOT_NUMBER,
p_SUB_INVENTORY => x_product_txn_rec.SUB_INVENTORY,
p_INTERFACE_TO_OM_FLAG => 'N',
p_BOOK_SALES_ORDER_FLAG => 'N',
p_RELEASE_SALES_ORDER_FLAG => 'N',
p_SHIP_SALES_ORDER_FLAG => 'N',
p_PROD_TXN_STATUS => 'ENTERED',
p_PROD_TXN_CODE => x_product_txn_rec.PROD_TXN_CODE,
p_LAST_UPDATE_DATE => SYSDATE,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => Fnd_Global.USER_ID,
p_CREATED_BY => Fnd_Global.USER_ID,
p_LAST_UPDATE_LOGIN => Fnd_Global.USER_ID,
p_ATTRIBUTE1 => x_product_txn_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => x_product_txn_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => x_product_txn_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => x_product_txn_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => x_product_txn_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => x_product_txn_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => x_product_txn_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => x_product_txn_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => x_product_txn_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => x_product_txn_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => x_product_txn_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => x_product_txn_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => x_product_txn_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => x_product_txn_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => x_product_txn_rec.ATTRIBUTE15,
p_CONTEXT => x_product_txn_rec.CONTEXT,
p_OBJECT_VERSION_NUMBER => 1,
P_SOURCE_SERIAL_NUMBER => x_product_txn_rec.source_serial_number,
P_SOURCE_INSTANCE_ID => x_product_txn_rec.source_instance_Id,
P_NON_SOURCE_SERIAL_NUMBER => x_product_txn_rec.non_source_serial_number,
P_NON_SOURCE_INSTANCE_ID => x_product_txn_rec.non_source_instance_id,
P_REQ_HEADER_ID => x_product_txn_rec.Req_Header_Id,
P_REQ_LINE_ID => x_product_txn_rec.Req_Line_Id,
P_ORDER_HEADER_ID => x_Product_Txn_Rec.Order_Header_Id,
P_ORDER_LINE_ID => x_Product_Txn_Rec.Order_Line_Id,
P_PRD_TXN_QTY_RECEIVED => x_product_txn_rec.Prd_Txn_Qty_Received,
P_PRD_TXN_QTY_SHIPPED => x_product_txn_rec.Prd_Txn_Qty_Shipped,
P_SUB_INVENTORY_RCVD => x_product_txn_rec.Sub_Inventory_Rcvd,
P_LOT_NUMBER_RCVD => x_product_txn_rec.Lot_Number_Rcvd,
P_LOCATOR_ID => x_product_txn_rec.Locator_Id,
--R12 Development Changes
p_picking_rule_id => x_product_txn_rec.picking_rule_id,
P_PROJECT_ID => x_product_txn_rec.project_id,
P_TASK_ID => x_product_txn_rec.task_id,
P_UNIT_NUMBER => x_product_txn_rec.unit_number,
P_INTERNAL_PO_HEADER_ID => x_product_txn_rec.internal_po_header_id); -- swai: bug 6148019
UPDATE csd_repairs
SET ro_txn_status = 'CHARGE_ENTERED'
WHERE repair_line_id = x_product_txn_rec.REPAIR_LINE_ID;
Fnd_Message.SET_NAME('CSD', 'CSD_ERR_REPAIRS_UPDATE');
SELECT revision_qty_control_code
INTO l_rev_ctl_code
FROM mtl_system_items
WHERE organization_id =
Cs_Std.get_item_valdn_orgzn_id
AND inventory_item_id =
x_product_txn_rec.inventory_item_id;
SELECT 'x'
INTO l_dummy
FROM mtl_item_revisions
WHERE inventory_item_id =
x_product_txn_rec.inventory_item_id
AND organization_id =
Cs_Std.get_item_valdn_orgzn_id
AND revision = x_product_txn_rec.revision;
UPDATE csd_product_transactions
SET prod_txn_status = 'SUBMITTED',
interface_to_om_flag = 'Y'
WHERE product_transaction_id =
x_product_txn_rec.PRODUCT_TRANSACTION_ID;
'CSD_ERR_PRD_TXN_UPDATE');
UPDATE csd_repairs
SET ro_txn_status = 'OM_SUBMITTED'
WHERE repair_line_id =
x_product_txn_rec.REPAIR_LINE_ID;
'CSD_ERR_REPAIRS_UPDATE');
l_Line_Tbl_Type(1).operation := OE_GLOBALS.G_OPR_UPDATE;
FND_MESSAGE.SET_NAME('CSD','CSD_ERR_PROJECT_UPDATE');
end if; -- end update project and unit number fields
end if; -- end update 3rd party fields
SELECT a.order_header_id, a.order_line_id
INTO l_order_header_id, l_order_line_id
FROM cs_estimate_details a
WHERE a.estimate_detail_id = x_estimate_detail_id
AND a.order_header_id IS NOT NULL
AND a.order_line_id IS NOT NULL;
SELECT ship_from_org_id, unit_selling_price, org_id
INTO l_ship_from_org_id,
l_unit_selling_price,
l_order_rec.org_id
FROM oe_order_lines_all
WHERE line_id = l_order_line_id;
SELECT booked_flag
INTO l_booked_flag
FROM oe_order_headers_all
WHERE header_id = l_order_rec.order_header_id;
l_Line_Tbl_Type(1).operation := OE_GLOBALS.G_OPR_UPDATE;
FND_MESSAGE.SET_NAME('CSD','CSD_ERR_PROJECT_UPDATE');
end if; -- end update 3rd party fields
Csd_Update_Programs_Pvt.prod_txn_status_upd(p_repair_line_id => x_product_txn_rec.repair_line_id,
p_commit => Fnd_Api.g_false);
UPDATE csd_repairs
SET ro_txn_status = 'OM_BOOKED'
WHERE repair_line_id =
x_product_txn_rec.REPAIR_LINE_ID;
'CSD_ERR_REPAIRS_UPDATE');
SELECT 'x'
INTO l_dummy
FROM mtl_secondary_inventories
WHERE secondary_inventory_name =
x_product_txn_rec.sub_inventory
AND organization_id = l_ship_from_org_id;
SELECT picking_rule_id
INTO l_picking_rule_id
FROM wsh_picking_rules
WHERE picking_rule_id = l_picking_rule_id;
SELECT released_status
INTO l_release_status
FROM wsh_delivery_details
WHERE source_line_id = l_order_line_id
AND SOURCE_CODE = 'OE'; /*Fixed for bug#5846054 */
SELECT released_status
INTO l_release_status
FROM wsh_delivery_details
WHERE source_line_id = l_order_line_id
AND SOURCE_CODE = 'OE'; /*Fixed for bug#5846054 */
UPDATE csd_product_transactions
SET prod_txn_status = 'RELEASED',
release_sales_order_flag = 'Y'
WHERE product_transaction_id =
x_product_txn_rec.PRODUCT_TRANSACTION_ID;
'CSD_ERR_PRD_TXN_UPDATE');
UPDATE csd_repairs
SET ro_txn_status = 'OM_RELEASED'
WHERE repair_line_id =
x_product_txn_rec.REPAIR_LINE_ID;
'CSD_ERR_REPAIRS_UPDATE');
UPDATE csd_product_transactions
SET prod_txn_status = 'BOOKED'
WHERE product_transaction_id =
x_product_txn_rec.PRODUCT_TRANSACTION_ID;
'CSD_ERR_PRD_TXN_UPDATE');
UPDATE csd_repairs
SET ro_txn_status = 'OM_BOOKED'
WHERE repair_line_id =
x_product_txn_rec.REPAIR_LINE_ID;
'CSD_ERR_REPAIRS_UPDATE');
SELECT requested_quantity, released_status
INTO l_ship_qty, l_release_status
FROM wsh_delivery_details
WHERE source_header_id =
l_order_rec.order_header_id
AND source_line_id = l_order_rec.order_line_id
AND SOURCE_CODE = 'OE'; /*Fixed for bug#5685341*/
UPDATE csd_product_transactions
SET prod_txn_status = 'SHIPPED',
ship_sales_order_flag = 'Y'
WHERE product_transaction_id =
x_product_txn_rec.PRODUCT_TRANSACTION_ID;
'CSD_ERR_PRD_TXN_UPDATE');
UPDATE csd_repairs
SET ro_txn_status = 'OM_SHIPPED'
WHERE repair_line_id =
x_product_txn_rec.REPAIR_LINE_ID;
'CSD_ERR_REPAIRS_UPDATE');
UPDATE csd_product_transactions
SET interface_to_om_flag = 'N',
book_sales_order_flag = 'N',
release_sales_order_flag = 'N',
ship_sales_order_flag = 'N'
WHERE product_transaction_id =
x_product_txn_rec.PRODUCT_TRANSACTION_ID;
UPDATE csd_repairs
SET auto_process_rma = 'N'
WHERE repair_line_id = x_product_txn_rec.repair_line_id;
UPDATE csd_product_transactions
SET book_sales_order_flag = 'N',
release_sales_order_flag = 'N',
ship_sales_order_flag = 'N'
WHERE product_transaction_id =
x_product_txn_rec.PRODUCT_TRANSACTION_ID;
UPDATE csd_repairs
SET auto_process_rma = 'N'
WHERE repair_line_id = x_product_txn_rec.repair_line_id;
UPDATE csd_product_transactions
SET release_sales_order_flag = 'N',
ship_sales_order_flag = 'N'
WHERE product_transaction_id =
x_product_txn_rec.PRODUCT_TRANSACTION_ID;
UPDATE csd_product_transactions
SET ship_sales_order_flag = 'N'
WHERE product_transaction_id =
x_product_txn_rec.PRODUCT_TRANSACTION_ID;
SELECT customer_id, account_id
FROM cs_incidents_all_b
WHERE incident_id = p_incident_id;
SELECT estimate_detail_id, object_version_number
FROM cs_estimate_details
WHERE source_id = p_rep_line_id
AND source_code = 'DR'
AND interface_to_oe_flag = 'N'
AND order_header_id IS NULL
AND order_line_id IS NULL
AND line_category_code = 'ORDER';
SELECT 'X'
INTO l_check
FROM fnd_lookups
WHERE lookup_type = 'CSD_PRODUCT_TXN_STATUS'
AND lookup_code = x_product_txn_rec.PROD_TXN_STATUS;
SELECT incident_id
INTO l_incident_id
FROM csd_repairs
WHERE repair_line_id = x_product_txn_rec.repair_line_id;
SELECT currency_code
INTO l_curr_code
FROM oe_price_lists
WHERE price_list_id = x_product_txn_rec.price_list_id;
Csd_Gen_Utility_Pvt.ADD('Call csd_product_transactions_pkg.insert_row to insert prod txns');
Csd_Product_Transactions_Pkg.INSERT_ROW(px_PRODUCT_TRANSACTION_ID => x_product_txn_rec.PRODUCT_TRANSACTION_ID,
p_REPAIR_LINE_ID => x_product_txn_rec.REPAIR_LINE_ID,
p_ESTIMATE_DETAIL_ID => x_estimate_detail_id,
p_ACTION_TYPE => x_product_txn_rec.ACTION_TYPE,
p_ACTION_CODE => x_product_txn_rec.ACTION_CODE,
p_LOT_NUMBER => x_product_txn_rec.LOT_NUMBER,
p_SUB_INVENTORY => x_product_txn_rec.SUB_INVENTORY,
p_INTERFACE_TO_OM_FLAG => x_product_txn_rec.INTERFACE_TO_OM_FLAG,
p_BOOK_SALES_ORDER_FLAG => x_product_txn_rec.BOOK_SALES_ORDER_FLAG,
p_RELEASE_SALES_ORDER_FLAG => x_product_txn_rec.RELEASE_SALES_ORDER_FLAG,
p_SHIP_SALES_ORDER_FLAG => x_product_txn_rec.SHIP_SALES_ORDER_FLAG,
p_PROD_TXN_STATUS => 'SUBMITTED',
p_PROD_TXN_CODE => x_product_txn_rec.PROD_TXN_CODE,
p_LAST_UPDATE_DATE => SYSDATE,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => Fnd_Global.USER_ID,
p_CREATED_BY => Fnd_Global.USER_ID,
p_LAST_UPDATE_LOGIN => Fnd_Global.USER_ID,
p_ATTRIBUTE1 => x_product_txn_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => x_product_txn_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => x_product_txn_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => x_product_txn_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => x_product_txn_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => x_product_txn_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => x_product_txn_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => x_product_txn_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => x_product_txn_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => x_product_txn_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => x_product_txn_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => x_product_txn_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => x_product_txn_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => x_product_txn_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => x_product_txn_rec.ATTRIBUTE15,
p_CONTEXT => x_product_txn_rec.CONTEXT,
p_OBJECT_VERSION_NUMBER => 1,
P_SOURCE_SERIAL_NUMBER => x_product_txn_rec.source_serial_number,
P_SOURCE_INSTANCE_ID => x_product_txn_rec.source_instance_Id,
P_NON_SOURCE_SERIAL_NUMBER => x_product_txn_rec.non_source_serial_number,
P_NON_SOURCE_INSTANCE_ID => x_product_txn_rec.non_source_instance_id,
P_REQ_HEADER_ID => x_product_txn_rec.Req_Header_Id,
P_REQ_LINE_ID => x_product_txn_rec.Req_Line_Id,
P_ORDER_HEADER_ID => x_Product_Txn_Rec.Order_Header_Id,
P_ORDER_LINE_ID => x_Product_Txn_Rec.Order_Line_Id,
P_PRD_TXN_QTY_RECEIVED => x_product_txn_rec.Prd_Txn_Qty_Received,
P_PRD_TXN_QTY_SHIPPED => x_product_txn_rec.Prd_Txn_Qty_Shipped,
P_SUB_INVENTORY_RCVD => x_product_txn_rec.Sub_Inventory_Rcvd,
P_LOT_NUMBER_RCVD => x_product_txn_rec.Lot_Number_Rcvd,
P_LOCATOR_ID => x_product_txn_rec.Locator_Id,
p_picking_rule_id => x_product_txn_rec.picking_rule_id, --R12 development changes
P_PROJECT_ID => x_product_txn_rec.project_id,
P_TASK_ID => x_product_txn_rec.task_id,
P_UNIT_NUMBER => x_product_txn_rec.unit_number,
P_INTERNAL_PO_HEADER_ID => x_product_txn_rec.internal_po_header_id); -- swai: bug 6148019
UPDATE csd_repairs
SET ro_txn_status = 'OM_SUBMITTED'
WHERE repair_line_id = x_product_txn_rec.REPAIR_LINE_ID;
Fnd_Message.SET_NAME('CSD', 'CSD_ERR_REPAIRS_UPDATE');
/* procedure name: update_product_txn */
/* description : procedure to update product txn lines.It is */
/* called from ON-UPDATE trigger in the repair */
/* order form */
/*----------------------------------------------------------------*/
PROCEDURE update_product_txn(p_api_version IN NUMBER,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
x_product_txn_rec IN OUT NOCOPY PRODUCT_TXN_REC,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PRODUCT_TXN';
SELECT estimate_detail_id, object_version_number
FROM cs_estimate_details
WHERE source_id = p_rep_line_id
AND source_code = 'DR'
AND interface_to_oe_flag = 'N'
AND order_header_id IS NULL
AND order_line_id IS NULL;
l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_process_pvt.update_product_txn';
SAVEPOINT UPDATE_PRODUCT_TXN_PVT;
Select ship_from_org_id, header_id
into l_ship_from_org_id, l_order_header_id
from oe_order_lines_all oel,
cs_estimate_details ced
where oel.line_id = ced.order_line_id
and ced.estimate_detail_id = x_product_txn_rec.estimate_detail_id;
ROLLBACK TO UPDATE_PRODUCT_TXN_PVT;
UPDATE csd_product_transactions
SET interface_to_om_flag = 'N',
book_sales_order_flag = 'N',
release_sales_order_flag = 'N',
ship_sales_order_flag = 'N'
WHERE product_transaction_id =
x_product_txn_rec.PRODUCT_TRANSACTION_ID;
UPDATE csd_product_transactions
SET book_sales_order_flag = 'N',
release_sales_order_flag = 'N',
ship_sales_order_flag = 'N'
WHERE product_transaction_id =
x_product_txn_rec.PRODUCT_TRANSACTION_ID;
UPDATE csd_product_transactions
SET release_sales_order_flag = 'N',
ship_sales_order_flag = 'N'
WHERE product_transaction_id =
x_product_txn_rec.PRODUCT_TRANSACTION_ID;
UPDATE csd_product_transactions
SET ship_sales_order_flag = 'N'
WHERE product_transaction_id =
x_product_txn_rec.PRODUCT_TRANSACTION_ID;
ROLLBACK TO UPDATE_PRODUCT_TXN_PVT;
ROLLBACK TO UPDATE_PRODUCT_TXN_PVT;
ROLLBACK TO UPDATE_PRODUCT_TXN_PVT;
END update_product_txn;
/* procedure name: delete_product_txn */
/* description : procedure used to delete */
/* product transaction lines */
/* */
/*--------------------------------------------------*/
PROCEDURE delete_product_txn(p_api_version IN NUMBER,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_product_txn_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PRODUCT_TXN';
l_delete_allow VARCHAR2(1);
SAVEPOINT delete_product_txn;
SELECT a.estimate_detail_id
INTO l_est_detail_id
FROM csd_product_transactions a, cs_estimate_details b
WHERE a.estimate_detail_id = b.estimate_detail_id
AND a.product_transaction_id = p_product_txn_id
AND b.order_header_id IS NULL;
l_delete_allow := 'Y';
l_delete_allow := 'N';
Fnd_Message.SET_NAME('CSD', 'CSD_API_DELETE_NOT_ALLOWED');
Csd_Gen_Utility_Pvt.ADD('Product txn is interfaced,so it cannot be deleted');
Csd_Gen_Utility_Pvt.ADD('Too many from Product txn line is allowed to delete');
Csd_Gen_Utility_Pvt.ADD('l_delete_allow =' ||
l_delete_allow);
IF l_delete_allow = 'Y'
THEN
l_Charges_Rec.estimate_detail_id := l_est_detail_id;
Csd_Gen_Utility_Pvt.ADD('Call process_charge_lines to delete');
p_action => 'DELETE',
p_Charges_Rec => l_Charges_Rec,
x_estimate_detail_id => x_estimate_detail_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
Csd_Gen_Utility_Pvt.ADD('Call csd_product_transactions_pkg.Delete_Row');
Csd_Product_Transactions_Pkg.Delete_Row(p_PRODUCT_TRANSACTION_ID => p_product_txn_id);
END IF; --end of delete
ROLLBACK TO delete_product_txn;
ROLLBACK TO delete_product_txn;
ROLLBACK TO delete_product_txn;
END delete_product_txn;
/* procedure name: update_task */
/* description : procedure used to update task */
/* Called from : Depot Repair Form to Create Task */
/* Input Parm : p_api_version NUMBER Required Api Version number */
/* p_init_msg_list VARCHAR2 Optional Initializes message stack if fnd_api.g_true, */
/* default value is fnd_api.g_false */
/* p_commit VARCHAR2 Optional Commits in API if fnd_api.g_true, default */
/* fnd_api.g_false */
/* p_validation_level NUMBER Optional API uses this parameter to determine which */
/* validation steps must be done and which steps */
/* should be skipped. */
/* CREATE_TASK_REC_TYPE RECORD Required Columns are in the Record CREATE_TASK_REC_TYPE */
/* Output Parm : x_return_status VARCHAR2 Return status after the call. The status can be*/
/* fnd_api.g_ret_sts_success (success) */
/* fnd_api.g_ret_sts_error (error) */
/* fnd_api.g_ret_sts_unexp_error (unexpected) */
/* x_msg_count NUMBER Number of messages in the message stack */
/* x_msg_data VARCHAR2 Message text if x_msg_count >= 1 */
/*-----------------------------------------------------------------------------------------------------------*/
PROCEDURE update_task(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_create_task_rec IN CREATE_TASK_REC_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TASK';
SAVEPOINT update_task;
Csd_Gen_Utility_Pvt.ADD('CSD_PROCESS_PVT.update_task before jtf_tasks_pub.UPDATE_TASK');
Jtf_Tasks_Pub.UPDATE_TASK(P_API_VERSION => 1.0,
P_INIT_MSG_LIST => Fnd_Api.g_true,
P_COMMIT => Fnd_Api.g_false,
P_TASK_ID => l_create_task_rec.task_id,
P_TASK_NAME => l_create_task_rec.TASK_NAME,
P_TASK_TYPE_ID => l_create_task_rec.TASK_TYPE_ID,
P_DESCRIPTION => l_create_task_rec.DESCRIPTION,
P_TASK_STATUS_ID => l_create_task_rec.TASK_STATUS_ID,
P_TASK_PRIORITY_NAME => l_create_task_rec.TASK_PRIORITY_NAME,
P_TASK_PRIORITY_ID => l_create_task_rec.TASK_PRIORITY_ID,
P_OWNER_TYPE_CODE => l_create_task_rec.OWNER_TYPE_CODE,
P_OWNER_ID => l_create_task_rec.OWNER_ID,
P_OWNER_TERRITORY_ID => l_create_task_rec.OWNER_TERRITORY_ID,
P_ASSIGNED_BY_ID => l_create_task_rec.ASSIGNED_BY_ID,
P_CUSTOMER_ID => l_create_task_rec.CUSTOMER_ID,
P_CUST_ACCOUNT_ID => l_create_task_rec.CUST_ACCOUNT_ID,
P_ADDRESS_ID => l_create_task_rec.ADDRESS_ID,
P_PLANNED_START_DATE => l_create_task_rec.planned_start_date,
P_PLANNED_END_DATE => l_create_task_rec.planned_end_date,
P_SCHEDULED_START_DATE => l_create_task_rec.scheduled_start_date,
P_SCHEDULED_END_DATE => l_create_task_rec.scheduled_end_date,
P_ACTUAL_START_DATE => l_create_task_rec.actual_start_date,
P_ACTUAL_END_DATE => l_create_task_rec.actual_end_date,
P_TIMEZONE_ID => l_create_task_rec.TIMEZONE_ID,
P_SOURCE_OBJECT_TYPE_CODE => l_create_task_rec.SOURCE_OBJECT_TYPE_CODE,
P_SOURCE_OBJECT_ID => l_create_task_rec.SOURCE_OBJECT_ID,
P_SOURCE_OBJECT_NAME => l_create_task_rec.SOURCE_OBJECT_NAME,
P_DURATION => l_create_task_rec.DURATION,
P_DURATION_UOM => l_create_task_rec.DURATION_UOM,
P_PLANNED_EFFORT => l_create_task_rec.PLANNED_EFFORT,
P_PLANNED_EFFORT_UOM => l_create_task_rec.PLANNED_EFFORT_UOM,
P_ACTUAL_EFFORT => l_create_task_rec.ACTUAL_EFFORT,
P_ACTUAL_EFFORT_UOM => l_create_task_rec.ACTUAL_EFFORT_UOM,
P_PRIVATE_FLAG => l_create_task_rec.PRIVATE_FLAG,
P_PUBLISH_FLAG => l_create_task_rec.PUBLISH_FLAG,
P_RESTRICT_CLOSURE_FLAG => l_create_task_rec.RESTRICT_CLOSURE_FLAG,
P_ATTRIBUTE1 => l_create_task_rec.ATTRIBUTE1,
P_ATTRIBUTE2 => l_create_task_rec.ATTRIBUTE2,
P_ATTRIBUTE3 => l_create_task_rec.ATTRIBUTE3,
P_ATTRIBUTE4 => l_create_task_rec.ATTRIBUTE4,
P_ATTRIBUTE5 => l_create_task_rec.ATTRIBUTE5,
P_ATTRIBUTE6 => l_create_task_rec.ATTRIBUTE6,
P_ATTRIBUTE7 => l_create_task_rec.ATTRIBUTE7,
P_ATTRIBUTE8 => l_create_task_rec.ATTRIBUTE8,
P_ATTRIBUTE9 => l_create_task_rec.ATTRIBUTE9,
P_ATTRIBUTE10 => l_create_task_rec.ATTRIBUTE10,
P_ATTRIBUTE11 => l_create_task_rec.ATTRIBUTE11,
P_ATTRIBUTE12 => l_create_task_rec.ATTRIBUTE12,
P_ATTRIBUTE13 => l_create_task_rec.ATTRIBUTE13,
P_ATTRIBUTE14 => l_create_task_rec.ATTRIBUTE14,
P_ATTRIBUTE15 => l_create_task_rec.ATTRIBUTE15,
P_ATTRIBUTE_CATEGORY => l_create_task_rec.ATTRIBUTE_CATEGORY,
P_BOUND_MODE_CODE => l_create_task_rec.bound_mode_code,
P_SOFT_BOUND_FLAG => l_create_task_rec.soft_bound_flag,
P_PARENT_TASK_ID => l_create_task_rec.PARENT_TASK_ID,
P_ESCALATION_LEVEL => l_create_task_rec.ESCALATION_LEVEL,
P_OBJECT_VERSION_NUMBER => l_create_task_rec.OBJECT_VERSION_NUMBER,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data);
Csd_Gen_Utility_Pvt.ADD('CSD_PROCESS_PVT.update_task after jtf_tasks_pub.UPDATE_TASK x_return_status' ||
x_return_status);
ROLLBACK TO update_task;
ROLLBACK TO update_task;
ROLLBACK TO update_task;
END update_task;
SELECT tsk.task_id, -- hist.paramn1
tsk.last_updated_by, -- hist.paramn2
tsk.owner_id, -- hist.paramn3
tsk.assigned_by_id, -- hist.paramn4
tsk.task_status_id, -- hist.paramn5
tsk.task_number, -- hist.paramc1
tsk.owner_type, -- hist.paramc2
tsk.owner, -- hist.paramc3
NULL assignee_type, -- hist.paramc4
NULL assignee_name, -- hist.paramc5
tsk.task_status, -- hist.paramc6
tsk.planned_start_date, -- hist.paramd1
tsk.actual_start_date, -- hist.paramd2
tsk.actual_end_date, -- hist.paramd3
tsk.last_update_date -- hist.paramd4
FROM CSD_REPAIR_TASKS_V tsk
WHERE tsk.source_object_type_code = 'DR'
AND tsk.source_object_id = p_repair_line_id
AND tsk.task_id = p_task_id;
SELECT COUNT(*)
INTO l_check_task
FROM CSD_REPAIR_TASKS_V tsk, CSD_REPAIR_HISTORY hist
WHERE tsk.source_object_id = hist.repair_line_id
AND hist.paramn1 = p_task_id
AND tsk.source_object_type_code = 'DR'
AND hist.event_code = 'TC';
l_paramn2, -- last updated by
l_paramn3, -- owner id
l_paramn4, -- assigned by id
l_paramn5, -- status id
l_paramc1, -- task number
l_paramc2, -- owner type
l_paramc3, -- owner name
l_paramc4, -- null assignee type
l_paramc5, -- null assignee name
l_paramc6, -- status
l_paramd1, -- planned start date
l_paramd2, -- actual start date
l_paramd3, -- actual end date
l_paramd4; -- last updated date
p_PROGRAM_UPDATE_DATE => NULL,
p_CREATED_BY => Fnd_Global.USER_ID,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => Fnd_Global.USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_repair_line_id => p_repair_line_id,
p_EVENT_CODE => l_event_code,
p_EVENT_DATE => SYSDATE,
p_QUANTITY => NULL,
p_PARAMN1 => l_paramn1,
p_PARAMN2 => l_paramn2,
p_PARAMN3 => l_paramn3,
p_PARAMN4 => l_paramn4,
p_PARAMN5 => l_paramn5,
p_PARAMN6 => NULL,
p_PARAMN7 => NULL,
p_PARAMN8 => NULL,
p_PARAMN9 => NULL,
p_PARAMN10 => Fnd_Global.USER_ID,
p_PARAMC1 => l_paramc1,
p_PARAMC2 => l_paramc2,
p_PARAMC3 => l_paramc3,
p_PARAMC4 => l_paramc4,
p_PARAMC5 => l_paramc5,
p_PARAMC6 => l_paramc6,
p_PARAMC7 => NULL,
p_PARAMC8 => NULL,
p_PARAMC9 => NULL,
p_PARAMC10 => NULL,
p_PARAMD1 => l_paramd1,
p_PARAMD2 => l_paramd1,
p_PARAMD3 => l_paramd1,
p_PARAMD4 => l_paramd1,
p_PARAMD5 => NULL,
p_PARAMD6 => NULL,
p_PARAMD7 => NULL,
p_PARAMD8 => NULL,
p_PARAMD9 => NULL,
p_PARAMD10 => NULL,
p_ATTRIBUTE_CATEGORY => NULL,
p_ATTRIBUTE1 => NULL,
p_ATTRIBUTE2 => NULL,
p_ATTRIBUTE3 => NULL,
p_ATTRIBUTE4 => NULL,
p_ATTRIBUTE5 => NULL,
p_ATTRIBUTE6 => NULL,
p_ATTRIBUTE7 => NULL,
p_ATTRIBUTE8 => NULL,
p_ATTRIBUTE9 => NULL,
p_ATTRIBUTE10 => NULL,
p_ATTRIBUTE11 => NULL,
p_ATTRIBUTE12 => NULL,
p_ATTRIBUTE13 => NULL,
p_ATTRIBUTE14 => NULL,
p_ATTRIBUTE15 => NULL,
p_LAST_UPDATE_LOGIN => Fnd_Global.CONC_LOGIN_ID,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data);
/* procedure name: Update_repair_task_hist */
/* description : procedure used to Update Repair Order history */
/* for task creation */
/* Called from : Depot Repair Form to update to Repair history */
/* Input Parm : p_api_version NUMBER Required Api Version number */
/* p_init_msg_list VARCHAR2 Optional Initializes message stack if fnd_api.g_true, */
/* default value is fnd_api.g_false */
/* p_commit VARCHAR2 Optional Commits in API if fnd_api.g_true, default */
/* fnd_api.g_false */
/* p_validation_level NUMBER Optional API uses this parameter to determine which */
/* validation steps must be done and which steps */
/* should be skipped. */
/* p_task_id NUMBER Required Task Id */
/* p_repair_line_id NUMBER Required Repair_line_id */
/* Output Parm : x_return_status VARCHAR2 Return status after the call. The status can be*/
/* fnd_api.g_ret_sts_success (success) */
/* fnd_api.g_ret_sts_error (error) */
/* fnd_api.g_ret_sts_unexp_error (unexpected) */
/* x_msg_count NUMBER Number of messages in the message stack */
/* x_msg_data VARCHAR2 Message text if x_msg_count >= 1 */
/*-----------------------------------------------------------------------------------------------------------*/
/*
PROCEDURE Update_repair_task_hist
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
p_task_id IN NUMBER,
p_repair_line_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_AND_WRITE';
SELECT hist.event_code,
hist.paramc2, -- tsk.owner_type
hist.paramc3, -- tsk.owner
null paramc4, -- tsk.assignee_type
null paramc5, -- tsk.assignee_name
hist.paramc6 -- tsk.task_status
FROM CSD_REPAIR_HISTORY hist
WHERE hist.paramn1 = p_task_id
AND hist.repair_line_id = p_repair_line_id
--and hist.event_code = 'TC'
ORDER BY hist.repair_history_id DESC;
SELECT tsk.task_id, -- hist.paramn1
tsk.last_updated_by, -- hist.paramn2
tsk.owner_id, -- hist.paramn3
tsk.assigned_by_id, -- hist.paramn4
tsk.task_status_id, -- hist.paramn5
tsk.task_number, -- hist.paramc1
tsk.owner_type, -- hist.paramc2
tsk.owner, -- hist.paramc3
null assignee_type, -- hist.paramc4
null assignee_name, -- hist.paramc5
tsk.task_status, -- hist.paramc6
tsk.planned_start_date, -- hist.paramd1
tsk.actual_start_date, -- hist.paramd2
tsk.actual_end_date, -- hist.paramd3
tsk.last_update_date -- hist.paramd4
FROM CSD_REPAIR_TASKS_V tsk
WHERE tsk.source_object_type_code = 'DR'
AND tsk.source_object_id = p_repair_line_id
AND tsk.task_id = p_task_id;
SELECT COUNT(*)
INTO l_check_id
FROM CSD_REPAIR_TASKS_V tsk,
CSD_REPAIR_HISTORY hist
WHERE tsk.source_object_id = p_repair_line_id
AND hist.paramn1 = p_task_id
AND tsk.task_id = hist.paramn1
AND tsk.source_object_id = hist.repair_line_id
AND tsk.source_object_type_code = 'DR';
l_paramn2, -- last updated by
l_paramn3, -- owner id
l_paramn4, -- assigned by id
l_paramn5, -- status id
l_paramc1, -- task number
l_paramc2, -- owner type
l_paramc3, -- owner name
l_paramc4, -- null assignee type
l_paramc5, -- null assignee name
l_paramc6, -- status
l_paramd1, -- planned start date
l_paramd2, -- actual start date
l_paramd3, -- actual end date
l_paramd4; -- last updated date
SAVEPOINT Update_repair_task_hist;
csd_gen_utility_pvt.ADD('Before Task to Update Repair History');
p_PROGRAM_UPDATE_DATE => NULL,
p_CREATED_BY => FND_GLOBAL.USER_ID,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_repair_line_id => p_repair_line_id,
p_EVENT_CODE => l_event_code,
p_EVENT_DATE => SYSDATE,
p_QUANTITY => NULL,
p_PARAMN1 => l_paramn1,
p_PARAMN2 => l_paramn2,
p_PARAMN3 => l_paramn3,
p_PARAMN4 => l_paramn4,
p_PARAMN5 => l_paramn5,
p_PARAMN6 => NULL,
p_PARAMN7 => NULL,
p_PARAMN8 => NULL,
p_PARAMN9 => NULL,
p_PARAMN10 => FND_GLOBAL.USER_ID,
p_PARAMC1 => l_paramc1,
p_PARAMC2 => l_paramc2,
p_PARAMC3 => l_paramc3,
p_PARAMC4 => l_paramc4,
p_PARAMC5 => l_paramc5,
p_PARAMC6 => l_paramc6,
p_PARAMC7 => NULL,
p_PARAMC8 => NULL,
p_PARAMC9 => NULL,
p_PARAMC10 => NULL,
p_PARAMD1 => l_paramd1,
p_PARAMD2 => l_paramd1,
p_PARAMD3 => l_paramd1,
p_PARAMD4 => l_paramd1,
p_PARAMD5 => NULL,
p_PARAMD6 => NULL,
p_PARAMD7 => NULL,
p_PARAMD8 => NULL,
p_PARAMD9 => NULL,
p_PARAMD10 => NULL,
p_ATTRIBUTE_CATEGORY => NULL,
p_ATTRIBUTE1 => NULL,
p_ATTRIBUTE2 => NULL,
p_ATTRIBUTE3 => NULL,
p_ATTRIBUTE4 => NULL,
p_ATTRIBUTE5 => NULL,
p_ATTRIBUTE6 => NULL,
p_ATTRIBUTE7 => NULL,
p_ATTRIBUTE8 => NULL,
p_ATTRIBUTE9 => NULL,
p_ATTRIBUTE10 => NULL,
p_ATTRIBUTE11 => NULL,
p_ATTRIBUTE12 => NULL,
p_ATTRIBUTE13 => NULL,
p_ATTRIBUTE14 => NULL,
p_ATTRIBUTE15 => NULL,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data
);
csd_gen_utility_pvt.ADD('After Task to Update Repair History : '||x_return_status);
ROLLBACK TO Update_repair_task_hist;
ROLLBACK TO Update_repair_task_hist;
ROLLBACK TO Update_repair_task_hist;
END Update_repair_task_hist;
l_last_updated_by NUMBER;
l_last_update_date DATE;
SELECT tsk.task_id, -- hist.paramn1
tsk.last_updated_by, -- hist.paramn2
tsk.owner_id, -- hist.paramn3
tsk.assigned_by_id, -- hist.paramn4
tsk.task_status_id, -- hist.paramn5
tsk.task_number, -- hist.paramc1
tsk.owner_type, -- hist.paramc2
tsk.owner, -- hist.paramc3
--null assignee_type, -- hist.paramc4
--null assignee_name, -- hist.paramc5
tsk.task_status, -- hist.paramc6
tsk.task_name, -- hist.paramc7
tsk.planned_start_date, -- hist.paramd1
tsk.actual_start_date, -- hist.paramd2
tsk.actual_end_date, -- hist.paramd3
tsk.last_update_date -- hist.paramd4
FROM CSD_REPAIR_TASKS_V tsk
WHERE tsk.source_object_type_code = 'DR'
AND tsk.source_object_id = p_repair_line_id
AND tsk.task_id = p_task_id;*/
SELECT
tsk.last_updated_by, -- hist.paramn2
tsk.assigned_by_id, -- hist.paramn4
tsk.task_number, -- hist.paramc1
tsk.task_name,
tsk.planned_start_date, -- hist.paramd1
tsk.actual_start_date, -- hist.paramd2
tsk.actual_end_date, -- hist.paramd3
tsk.last_update_date -- hist.paramd4
FROM CSD_REPAIR_TASKS_V tsk
WHERE tsk.source_object_type_code = 'DR'
AND tsk.source_object_id = p_repair_line_id
AND tsk.task_id = p_task_id;
SELECT COUNT(*)
INTO l_check_task
FROM CSD_REPAIR_TASKS_V tsk, CSD_REPAIR_HISTORY hist
WHERE tsk.source_object_id = hist.repair_line_id
AND hist.paramn1 = p_task_activity_rec.task_id
AND tsk.source_object_type_code = 'DR'
AND hist.event_code = 'TC';
l_last_updated_by, -- last updated by
l_assigned_by_id, -- assigned by id
l_task_number, -- task number
l_task_name, -- task name
l_planned_start_Date, -- planned start date
l_actual_start_Date, -- actual start date
l_actual_end_Date, -- actual end date
l_last_update_date; -- last updated date
l_paramn2, -- last updated by
l_paramn3, -- owner id
l_paramn4, -- assigned by id
l_paramn5, -- status id
l_paramc1, -- task number
l_paramc2, -- owner type sangiguptask
l_paramc3, -- owner name
--l_paramc4, -- null assignee type
-- l_paramc5, -- null assignee name
l_paramc6, -- status
l_paramc7, -- task name
l_paramd1, -- planned start date
l_paramd2, -- actual start date
l_paramd3, -- actual end date
l_paramd4-- last updated date;*/
p_PROGRAM_UPDATE_DATE => NULL,
p_CREATED_BY => Fnd_Global.USER_ID,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => Fnd_Global.USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_repair_line_id => p_task_activity_rec.repair_line_id,
p_EVENT_CODE => l_event_code,
p_EVENT_DATE => SYSDATE,
p_QUANTITY => NULL,
p_PARAMN1 => p_task_activity_rec.task_id, --task id,
p_PARAMN2 => l_last_updated_by, --l_paramn2,
p_PARAMN3 => p_task_activity_rec.new_owner_id, --l_paramn3,
p_PARAMN4 => l_assigned_by_id, --l_paramn4,
p_PARAMN5 => p_task_activity_rec.new_status_id, --l_paramn5,
p_PARAMN6 => p_task_activity_rec.new_resource_id, --l_paramn6,--assignee_id
p_PARAMN7 => NULL,
p_PARAMN8 => NULL,
p_PARAMN9 => NULL,
p_PARAMN10 => Fnd_Global.USER_ID,
p_PARAMC1 => l_task_number,
p_PARAMC2 => p_task_activity_rec.new_owner_type_code,
p_PARAMC3 => p_task_activity_rec.new_owner_name,
p_PARAMC4 => p_task_activity_rec.new_resource_type_code, --resource_type_code sangiguptask
p_PARAMC5 => p_task_activity_rec.new_resource_name, --assignee_name sangiguptask
p_PARAMC6 => p_task_activity_rec.new_status, --task status
p_PARAMC7 => l_task_name, --task name sangiguptask
p_PARAMC8 => NULL,
p_PARAMC9 => NULL,
p_PARAMC10 => NULL, -- split from rep line number sangiguptask
p_PARAMD1 => l_planned_start_Date, -- planned start date
p_PARAMD2 => l_actual_start_Date,
p_PARAMD3 => l_actual_end_Date,
p_PARAMD4 => l_last_update_date,
p_PARAMD5 => NULL,
p_PARAMD6 => NULL,
p_PARAMD7 => NULL,
p_PARAMD8 => NULL,
p_PARAMD9 => NULL,
p_PARAMD10 => NULL,
p_ATTRIBUTE_CATEGORY => NULL,
p_ATTRIBUTE1 => NULL,
p_ATTRIBUTE2 => NULL,
p_ATTRIBUTE3 => NULL,
p_ATTRIBUTE4 => NULL,
p_ATTRIBUTE5 => NULL,
p_ATTRIBUTE6 => NULL,
p_ATTRIBUTE7 => NULL,
p_ATTRIBUTE8 => NULL,
p_ATTRIBUTE9 => NULL,
p_ATTRIBUTE10 => NULL,
p_ATTRIBUTE11 => NULL,
p_ATTRIBUTE12 => NULL,
p_ATTRIBUTE13 => NULL,
p_ATTRIBUTE14 => NULL,
p_ATTRIBUTE15 => NULL,
p_LAST_UPDATE_LOGIN => Fnd_Global.CONC_LOGIN_ID,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data);
/* procedure name: Update_repair_task_hist */
/* description : procedure used to Update Repair Order history */
/* for task creation */
/* Called from : Depot Repair Form to update to Repair history */
/* Input Parm : p_api_version NUMBER Required Api Version number */
/* p_init_msg_list VARCHAR2 Optional Initializes message stack if fnd_api.g_true, */
/* default value is fnd_api.g_false */
/* p_commit VARCHAR2 Optional Commits in API if fnd_api.g_true, default */
/* fnd_api.g_false */
/* p_validation_level NUMBER Optional API uses this parameter to determine which */
/* validation steps must be done and which steps */
/* should be skipped. */
/* p_task_id NUMBER Required Task Id */
/* p_repair_line_id NUMBER Required Repair_line_id */
/* Output Parm : x_return_status VARCHAR2 Return status after the call. The status can be*/
/* fnd_api.g_ret_sts_success (success) */
/* fnd_api.g_ret_sts_error (error) */
/* fnd_api.g_ret_sts_unexp_error (unexpected) */
/* x_msg_count NUMBER Number of messages in the message stack */
/* x_msg_data VARCHAR2 Message text if x_msg_count >= 1 */
/*-----------------------------------------------------------------------------------------------------------*/
PROCEDURE Update_repair_task_hist(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_true,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_task_activity_rec IN Csd_Process_Pvt.TASK_ACTIVITY_REC,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_AND_WRITE';
l_last_updated_by NUMBER;
l_last_update_date DATE;
SELECT hist.event_code,
hist.paramc2, -- owner type/assignee type(TAC)
hist.paramc3, -- tsk.owner name/assignee name (TAC)
hist.paramc4, -- assignee_type/previous assignee type (TAC)
hist.paramc5, -- tsk.assignee_name/prev task assignee name (TAC)
hist.paramc6, -- task status/null (TAC)
hist.paramn3, -- task owner id/assignee isd (TAC)
hist.paramn5, --status id/null (TAC)
hist.paramn6 -- tsk.assignee_id (TAC)
FROM CSD_REPAIR_HISTORY hist
WHERE hist.paramn1 = p_task_id
AND hist.repair_line_id = p_repair_line_id
--and hist.event_code = 'TC'
ORDER BY hist.repair_history_id DESC;
SELECT
tsk.last_updated_by, -- hist.paramn2
tsk.assigned_by_id, -- hist.paramn4
tsk.task_number, -- hist.paramc1
tsk.task_name,
tsk.planned_start_date, -- hist.paramd1
tsk.actual_start_date, -- hist.paramd2
tsk.actual_end_date, -- hist.paramd3
tsk.last_update_date -- hist.paramd4
FROM CSD_REPAIR_TASKS_V tsk
WHERE tsk.source_object_type_code = 'DR'
AND tsk.source_object_id = p_repair_line_id
AND tsk.task_id = p_task_id;
SAVEPOINT Update_repair_task_hist;
INTO l_last_updated_by, -- last updated by
l_assigned_by_id, -- assigned by id
l_task_number, -- task number
l_task_name, -- task name
l_planned_start_Date, -- planned start date
l_actual_start_Date, -- actual start date
l_actual_end_Date, -- actual end date
l_last_update_date; -- last updated date
p_PROGRAM_UPDATE_DATE => NULL,
p_CREATED_BY => Fnd_Global.USER_ID,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => Fnd_Global.USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_repair_line_id => p_task_activity_rec.repair_line_id,
p_EVENT_CODE => l_event_code,
p_EVENT_DATE => SYSDATE,
p_QUANTITY => NULL,
p_PARAMN1 => p_task_activity_rec.task_id, -- task_id
p_PARAMN2 => l_last_updated_by, -- last_updated_by
p_PARAMN3 => p_task_Activity_rec.new_owner_id, -- owner_id
p_PARAMN4 => l_assigned_by_id, -- assigned_by_id
p_PARAMN5 => p_task_Activity_rec.new_status_id, --l_paramn5, -- status_id
p_PARAMN6 => p_task_activity_rec.old_owner_id, -- assignee_id sangiguptask
p_PARAMN7 => NULL,
p_PARAMN8 => NULL,
p_PARAMN9 => NULL,
p_PARAMN10 => Fnd_Global.USER_ID,
p_PARAMC1 => l_task_number, -- l_paramc1,-- task number
p_PARAMC2 => p_task_activity_rec.new_owner_type_code, -- new owner type code
p_PARAMC3 => p_task_activity_rec.new_owner_name, -- task owner name
p_PARAMC4 => NULL, -- l_paramc4,-- asisgnee type
p_PARAMC5 => NULL, -- assignee name
p_PARAMC6 => p_task_activity_rec.new_status, -- task status
p_PARAMC7 => l_task_name,
p_PARAMC8 => p_task_activity_rec.old_owner_type_code, --prev owner type code
p_PARAMC9 => p_task_activity_Rec.old_owner_name, -- prev task owner name
p_PARAMC10 => NULL,
p_PARAMD1 => l_planned_start_Date,
p_PARAMD2 => l_actual_start_date,
p_PARAMD3 => l_actual_end_date,
p_PARAMD4 => l_last_update_Date,
p_PARAMD5 => NULL,
p_PARAMD6 => NULL,
p_PARAMD7 => NULL,
p_PARAMD8 => NULL,
p_PARAMD9 => NULL,
p_PARAMD10 => NULL,
p_ATTRIBUTE_CATEGORY => NULL,
p_ATTRIBUTE1 => NULL,
p_ATTRIBUTE2 => NULL,
p_ATTRIBUTE3 => NULL,
p_ATTRIBUTE4 => NULL,
p_ATTRIBUTE5 => NULL,
p_ATTRIBUTE6 => NULL,
p_ATTRIBUTE7 => NULL,
p_ATTRIBUTE8 => NULL,
p_ATTRIBUTE9 => NULL,
p_ATTRIBUTE10 => NULL,
p_ATTRIBUTE11 => NULL,
p_ATTRIBUTE12 => NULL,
p_ATTRIBUTE13 => NULL,
p_ATTRIBUTE14 => NULL,
p_ATTRIBUTE15 => NULL,
p_LAST_UPDATE_LOGIN => Fnd_Global.CONC_LOGIN_ID,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data);
p_PROGRAM_UPDATE_DATE => NULL,
p_CREATED_BY => Fnd_Global.USER_ID,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => Fnd_Global.USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_repair_line_id => p_task_activity_rec.repair_line_id,
p_EVENT_CODE => l_event_code,
p_EVENT_DATE => SYSDATE,
p_QUANTITY => NULL,
p_PARAMN1 => p_task_activity_rec.task_id, -- task_id
p_PARAMN2 => l_last_updated_by, -- last_updated_by
p_PARAMN3 => p_task_Activity_rec.new_owner_id, -- owner_id
p_PARAMN4 => l_assigned_by_id, -- assigned_by_id
p_PARAMN5 => p_task_Activity_rec.new_status_id, --l_paramn5, -- status_id
p_PARAMN6 => p_task_activity_rec.old_status_id,
p_PARAMN7 => NULL,
p_PARAMN8 => NULL,
p_PARAMN9 => NULL,
p_PARAMN10 => Fnd_Global.USER_ID,
p_PARAMC1 => l_task_number, -- task number
p_PARAMC2 => p_task_activity_rec.new_owner_type_code, -- new owner type code
p_PARAMC3 => p_task_activity_rec.new_owner_name, -- task owner name
p_PARAMC4 => NULL, -- asisgnee type
p_PARAMC5 => NULL, -- assignee name
p_PARAMC6 => p_task_activity_rec.new_status, -- task status
p_PARAMC7 => l_task_name,
p_PARAMC8 => p_task_activity_rec.old_status, -- prev task status
p_PARAMC9 => NULL,
p_PARAMC10 => NULL,
p_PARAMD1 => l_planned_start_Date,
p_PARAMD2 => l_actual_start_date,
p_PARAMD3 => l_actual_end_date,
p_PARAMD4 => l_last_update_Date,
p_PARAMD5 => NULL,
p_PARAMD6 => NULL,
p_PARAMD7 => NULL,
p_PARAMD8 => NULL,
p_PARAMD9 => NULL,
p_PARAMD10 => NULL,
p_ATTRIBUTE_CATEGORY => NULL,
p_ATTRIBUTE1 => NULL,
p_ATTRIBUTE2 => NULL,
p_ATTRIBUTE3 => NULL,
p_ATTRIBUTE4 => NULL,
p_ATTRIBUTE5 => NULL,
p_ATTRIBUTE6 => NULL,
p_ATTRIBUTE7 => NULL,
p_ATTRIBUTE8 => NULL,
p_ATTRIBUTE9 => NULL,
p_ATTRIBUTE10 => NULL,
p_ATTRIBUTE11 => NULL,
p_ATTRIBUTE12 => NULL,
p_ATTRIBUTE13 => NULL,
p_ATTRIBUTE14 => NULL,
p_ATTRIBUTE15 => NULL,
p_LAST_UPDATE_LOGIN => Fnd_Global.CONC_LOGIN_ID,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data);
p_PROGRAM_UPDATE_DATE => NULL,
p_CREATED_BY => Fnd_Global.USER_ID,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => Fnd_Global.USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_repair_line_id => p_task_activity_rec.repair_line_id,
p_EVENT_CODE => l_event_code,
p_EVENT_DATE => SYSDATE,
p_QUANTITY => NULL,
p_PARAMN1 => p_task_activity_rec.task_id, -- task_id
p_PARAMN2 => l_last_updated_by, -- last_updated_by
p_PARAMN3 => p_task_Activity_rec.new_resource_id, -- new assignee id
p_PARAMN4 => p_task_Activity_rec.old_resource_id, -- prev assignee id
p_PARAMN5 => NULL,
p_PARAMN6 => NULL,
p_PARAMN7 => NULL,
p_PARAMN8 => NULL,
p_PARAMN9 => NULL,
p_PARAMN10 => Fnd_Global.USER_ID,
p_PARAMC1 => l_task_number, -- task number
p_PARAMC2 => NULL,
p_PARAMC3 => NULL,
p_PARAMC4 => p_task_activity_rec.new_resource_type_code, -- new assignee type code
p_PARAMC5 => p_task_activity_rec.new_resource_name, -- new task assignee name
p_PARAMC6 => NULL,
p_PARAMC7 => l_task_name,
p_PARAMC8 => p_task_activity_rec.old_resource_type_code, -- old assignee type code
p_PARAMC9 => p_task_activity_rec.old_resource_name, -- old task assignee name
p_PARAMC10 => NULL,
p_PARAMD1 => NULL,
p_PARAMD2 => NULL,
p_PARAMD3 => NULL,
p_PARAMD4 => NULL,
p_PARAMD5 => NULL,
p_PARAMD6 => NULL,
p_PARAMD7 => NULL,
p_PARAMD8 => NULL,
p_PARAMD9 => NULL,
p_PARAMD10 => NULL,
p_ATTRIBUTE_CATEGORY => NULL,
p_ATTRIBUTE1 => NULL,
p_ATTRIBUTE2 => NULL,
p_ATTRIBUTE3 => NULL,
p_ATTRIBUTE4 => NULL,
p_ATTRIBUTE5 => NULL,
p_ATTRIBUTE6 => NULL,
p_ATTRIBUTE7 => NULL,
p_ATTRIBUTE8 => NULL,
p_ATTRIBUTE9 => NULL,
p_ATTRIBUTE10 => NULL,
p_ATTRIBUTE11 => NULL,
p_ATTRIBUTE12 => NULL,
p_ATTRIBUTE13 => NULL,
p_ATTRIBUTE14 => NULL,
p_ATTRIBUTE15 => NULL,
p_LAST_UPDATE_LOGIN => Fnd_Global.CONC_LOGIN_ID,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data);
Csd_Gen_Utility_Pvt.ADD('After Task to Update Repair History : ' ||
x_return_status);
ROLLBACK TO Update_repair_task_hist;
ROLLBACK TO Update_repair_task_hist;
ROLLBACK TO Update_repair_task_hist;
END Update_repair_task_hist;
SELECT crog.repair_group_id,
crt.repair_type_ref,
crog.group_txn_status
FROM csd_repair_order_groups crog, csd_repair_types_vl crt
WHERE crog.repair_type_id = crt.repair_type_id
AND crog.incident_id = p_incident_id;
SELECT repair_line_id
FROM csd_repairs
WHERE repair_group_id = p_repair_group_id;
SELECT object_version_number
INTO l_ro_obj_version_number
FROM csd_repairs
WHERE repair_line_id = ro.repair_line_id;
Csd_Gen_Utility_Pvt.ADD('Update Repair Line Id =' ||
ro.repair_line_id);
Csd_Repairs_Pub.Update_Repair_Order(P_Api_Version_Number => 1.0,
P_Init_Msg_List => 'T',
P_Commit => 'F',
p_validation_level => 0,
p_REPAIR_LINE_ID => ro.repair_line_id,
P_REPLN_Rec => l_ro_rec,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data);
SELECT object_version_number
INTO l_grp_obj_version_number
FROM csd_repair_order_groups
WHERE repair_group_id = grp.repair_group_id;
Csd_Gen_Utility_Pvt.ADD('Update Repair Group Id =' ||
grp.repair_group_id);
Csd_Repair_Groups_Pvt.UPDATE_REPAIR_GROUPS(p_api_version => 1.0,
p_commit => 'F',
p_init_msg_list => 'T',
p_validation_level => 0,
x_repair_order_group_rec => l_grp_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT object_version_number
INTO l_ro_obj_version_number
FROM csd_repairs
WHERE repair_line_id = ro.repair_line_id;
Csd_Gen_Utility_Pvt.ADD('Update Repair Line Id =' ||
ro.repair_line_id);
Csd_Repairs_Pub.Update_Repair_Order(P_Api_Version_Number => 1.0,
P_Init_Msg_List => 'T',
P_Commit => 'F',
p_validation_level => 0,
p_REPAIR_LINE_ID => ro.repair_line_id,
P_REPLN_Rec => l_ro_rec,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data);
SELECT object_version_number
INTO l_grp_obj_version_number
FROM csd_repair_order_groups
WHERE repair_group_id = grp.repair_group_id;
Csd_Gen_Utility_Pvt.ADD('Update Repair Group Id =' ||
grp.repair_group_id);
Csd_Repair_Groups_Pvt.UPDATE_REPAIR_GROUPS(p_api_version => 1.0,
p_commit => 'F',
p_init_msg_list => 'T',
p_validation_level => 0,
x_repair_order_group_rec => l_grp_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT COUNT(*)
INTO l_open_ro_cnt
FROM csd_repairs
WHERE incident_id = p_incident_id
AND status <> 'C';
Csd_Gen_Utility_Pvt.ADD('Update Incident Id =' ||
p_incident_id);
SELECT object_version_number
INTO l_inc_obj_version_number
FROM cs_incidents_all_b
WHERE incident_id = p_incident_id;
SELECT name
INTO l_sr_status
FROM cs_incident_statuses_vl
WHERE incident_subtype = 'INC'
AND CLOSE_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(start_date_active, SYSDATE)) AND
TRUNC(NVL(end_date_active, SYSDATE))
AND status_code = 'CLOSED';
SELECT name
INTO l_sr_status
FROM cs_incident_statuses_vl
WHERE incident_subtype = 'INC'
AND CLOSE_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(start_date_active, SYSDATE)) AND
TRUNC(NVL(end_date_active, SYSDATE))
AND ROWNUM < 2;
Cs_Servicerequest_Pub.Update_Status(p_api_version => 2.0,
p_init_msg_list => 'T',
p_commit => 'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_resp_appl_id => Fnd_Global.RESP_APPL_ID,
p_resp_id => Fnd_Global.RESP_ID,
p_user_id => Fnd_Global.USER_ID,
p_login_id => Fnd_Global.LOGIN_ID,
p_request_id => p_incident_id,
p_request_number => NULL,
p_object_version_number => l_inc_obj_version_number,
p_status_id => NULL,
-- p_status => 'CLOSED',
/* Fixed for bug#3416001
Hardcoding for parameter p_status has been removed.
*/
p_status => l_sr_status,
p_closed_date => SYSDATE,
p_audit_comments => NULL,
p_called_by_workflow => 'F',
p_workflow_process_id => NULL,
p_comments => NULL,
p_public_comment_flag => 'F',
x_interaction_id => l_interaction_id);
Csd_Gen_Utility_Pvt.ADD('Update Repair Line ID =' ||
p_repair_line_id);
SELECT object_version_number
INTO l_ro_obj_version_number
FROM csd_repairs
WHERE repair_line_id = p_repair_line_id;
Csd_Repairs_Pub.Update_Repair_Order(P_Api_Version_Number => 1.0,
P_Init_Msg_List => 'T',
P_Commit => 'F',
p_validation_level => 0,
p_REPAIR_LINE_ID => p_repair_line_id,
P_REPLN_Rec => l_ro_rec,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data);
SELECT repair_group_id, incident_id
INTO l_repair_group_id, l_incident_id
FROM csd_repairs
WHERE repair_line_id = p_repair_line_id;
SELECT COUNT(*)
INTO l_grp_count
FROM csd_repairs
WHERE status = 'C'
AND repair_group_id = l_repair_group_id;
Csd_Gen_Utility_Pvt.ADD('Update Repair Group Id =' ||
l_repair_group_id);
SELECT object_version_number
INTO l_grp_obj_version_number
FROM csd_repair_order_groups
WHERE repair_group_id = l_repair_group_id;
Csd_Repair_Groups_Pvt.UPDATE_REPAIR_GROUPS(p_api_version => 1.0,
p_commit => 'F',
p_init_msg_list => 'T',
p_validation_level => 0,
x_repair_order_group_rec => l_grp_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT COUNT(*)
INTO l_open_ro_cnt
FROM csd_repairs
WHERE incident_id = l_incident_id
AND status <> 'C';
Csd_Gen_Utility_Pvt.ADD('Update Service Request = ' ||
l_incident_id);
SELECT object_version_number
INTO l_inc_obj_version_number
FROM cs_incidents_all_b
WHERE incident_id = l_incident_id;
SELECT name
INTO l_sr_status
FROM cs_incident_statuses_vl
WHERE incident_subtype = 'INC'
AND CLOSE_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(start_date_active, SYSDATE)) AND
TRUNC(NVL(end_date_active, SYSDATE))
AND status_code = 'CLOSED';
SELECT name
INTO l_sr_status
FROM cs_incident_statuses_vl
WHERE incident_subtype = 'INC'
AND CLOSE_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(start_date_active, SYSDATE)) AND
TRUNC(NVL(end_date_active, SYSDATE))
AND ROWNUM < 2;
Cs_Servicerequest_Pub.Update_Status(p_api_version => 2.0,
p_init_msg_list => 'T',
p_commit => 'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_resp_appl_id => Fnd_Global.RESP_APPL_ID,
p_resp_id => Fnd_Global.RESP_ID,
p_user_id => Fnd_Global.USER_ID,
p_login_id => Fnd_Global.LOGIN_ID,
p_request_id => l_incident_id,
p_request_number => NULL,
p_object_version_number => l_inc_obj_version_number,
p_status_id => NULL,
-- p_status => 'CLOSED',
/* Fixed for bug#3416001
Hardcoding for parameter p_status has been removed.
*/
p_status => l_sr_status,
p_closed_date => SYSDATE,
p_audit_comments => NULL,
p_called_by_workflow => 'F',
p_workflow_process_id => NULL,
p_comments => NULL,
p_public_comment_flag => 'F',
x_interaction_id => l_interaction_id);
SELECT Line_Category_code, COUNT(*) line_count
FROM CSD_ESTIMATE_DETAILS_EXT_V
WHERE INCIDENT_ID = p_incident_Id
AND Line_Category_Code IN ('RETURN', 'ORDER')
GROUP BY Line_Category_Code;
SELECT COUNT(*) ro_count
FROM CSD_REPAIRS
WHERE INCIDENT_ID = p_incident_Id;
/* procedure name: Update_Line_Txn_Source */
/* Description: procedure used to update the source_code and */
/* source_id of the line transaction */
/*---------------------------------------------------------------*/
PROCEDURE Update_Line_Txn_Source(p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
p_incident_id IN NUMBER,
p_estimate_detail_line_id IN NUMBER,
p_repair_line_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
BEGIN
UPDATE CS_ESTIMATE_DETAILS
SET SOURCE_CODE = 'DR', SOURCE_ID = p_repair_line_id
WHERE INCIDENT_ID = p_incident_id
AND ESTIMATE_DETAIL_ID = p_estimate_detail_line_id;
END Update_Line_Txn_Source;
/* procedure name: Update_iro_product_txn */
/* Description: procedure used to update the product transaction */
/* table and process pick release and shipping */
/* transactions for internal ROs. */
/* p_api_version Standard in parameter */
/* p_commit Standard in parameter */
/* p_init_msg_list Standard in parameter */
/* p_validation_level Standard in parameter */
/* x_return_status Standard Out parameter */
/* x_msg_count Standard in parameter */
/* x_msg_data Standard in parameter , */
/* x_product_txn_rec in out record variable of type */
/* csd_process_pvt.product_txn_rec ) ; */
PROCEDURE update_iro_product_txn(p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
x_product_txn_rec IN OUT nocopy Csd_Process_Pvt.product_txn_rec,
x_return_status OUT nocopy VARCHAR2,
x_msg_count OUT nocopy NUMBER,
x_msg_data OUT nocopy VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_iro_product_txn';
l_module_name VARCHAR2(240) := 'csd.plsql.csd_process_pvt.update_iro_product_txn';
SELECT customer_id, account_id
FROM cs_incidents_all_b
WHERE incident_id = p_incident_id;
SELECT estimate_detail_id,
repair_line_id,
interface_to_om_flag,
book_sales_order_flag,
release_sales_order_flag,
ship_sales_order_flag,
object_version_number
FROM csd_product_transactions
WHERE product_transaction_id = p_prod_txn_id;
SELECT source_header_id, source_line_id, released_status
FROM wsh_delivery_details
WHERE source_header_id = p_order_header_id
AND Source_line_id = p_Order_line_id
AND SOURCE_CODE = 'OE' /*Fixed for bug#5846054*/
AND released_status = C_Staged_PickConfirmed;
SELECT source_header_id, source_line_id, released_status
FROM wsh_delivery_details
WHERE source_header_id = p_order_header_id
AND Source_line_id = p_Order_line_id
AND SOURCE_CODE = 'OE' /*Fixed for bug#5846054*/
AND released_status = C_Shipped;
SAVEPOINT update_iro_product_txn;
SELECT 'x'
INTO l_check
FROM fnd_lookups
WHERE lookup_type = 'CSD_PRODUCT_TXN_STATUS'
AND lookup_code = x_product_txn_rec.prod_txn_status;
SELECT incident_id
INTO l_incident_id
FROM csd_repairs
WHERE repair_line_id = l_repair_line_id;
SELECT picking_rule_id
INTO l_picking_rule_id
FROM wsh_picking_rules
WHERE picking_rule_id = l_picking_rule_id
AND SYSDATE BETWEEN
NVL(start_date_active, SYSDATE) AND
NVL(end_date_active, SYSDATE + 1);
SELECT 'Y'
INTO l_order_to_be_pickreleased
FROM wsh_delivery_details a
WHERE a.source_header_id =
x_product_txn_rec.order_header_id
AND a.source_line_id =
x_product_txn_rec.order_line_id
AND a.released_status IN
(c_ready_for_release, c_backordered)
AND ROWNUM < 2;
SELECT 'Y'
INTO l_order_to_Be_PickReleased
FROM wsh_Delivery_Details
WHERE source_header_id =
x_product_txn_rec.order_header_id
AND source_line_id =
x_product_txn_Rec.Order_line_id
AND SOURCE_CODE = 'OE' /*Fixed for bug#5846054*/
AND released_Status IN
(c_ready_for_release, c_backordered,
c_release_to_warehouse);
UPDATE csd_product_transactions
SET prod_txn_status = C_Status_Released,
release_sales_order_flag = C_Yes,
object_version_number = Object_Version_Number + 1,
Last_Update_Date = SYSDATE,
Last_Updated_By = Fnd_Global.User_Id,
Last_Update_login = Fnd_Global.Login_Id
WHERE order_header_id =
released_line_rec.source_header_id
AND order_line_id =
released_line_rec.source_line_id
AND NVL(release_sales_order_flag, C_No) = C_No;
'Product transaction records are updated with Released Status');
RO is not udpated as it is does not make sense to update RO record
when ever its product transactions are released. Not applicable to internal ROs.
update csd_repairs
set ro_txn_status = 'OM_RELEASED' ,
object_version_number = Object_Version_Number + 1,
Last_Update_Date = Sysdate,
Last_Updated_By = Fnd_Global.User_Id,
Last_Update_login = Fnd_Global.Login_Id
where repair_line_id = x_product_txn_rec.repair_line_id;
fnd_message.set_name('CSD','CSD_ERR_REPAIRS_UPDATE');
'No valid product transaction record found to update release status');
SELECT Released_Status
INTO l_Released_Status
FROM wsh_delivery_details
WHERE source_header_id =
l_order_rec.order_header_id
AND source_line_id = l_order_rec.order_line_id
AND released_status IN (C_Staged_PickConfirmed,
C_Release_to_warehouse)
AND SOURCE_CODE = 'OE'; /*Fixed for bug#5840654 */
SELECT 'Y'
INTO l_order_to_Be_Shipped
FROM wsh_Delivery_Details
WHERE source_header_id =
x_product_txn_rec.order_header_id
AND source_line_id =
x_product_txn_Rec.Order_line_id
AND SOURCE_CODE = 'OE' /*Fixed for bug#5846054*/
AND released_Status IN
(c_ready_for_release, c_backordered,
c_release_to_warehouse,
c_staged_pickconfirmed);
UPDATE csd_product_transactions
SET -- prod_txn_status = C_Status_Shipped ,
release_sales_order_flag = C_Yes,
ship_sales_order_flag = C_Yes,
object_version_number = Object_Version_Number + 1,
Last_Update_Date = SYSDATE,
Last_Updated_By = Fnd_Global.User_Id,
Last_Update_login = Fnd_Global.Login_Id
WHERE order_header_id =
shipped_line_rec.source_header_id
AND order_line_id =
shipped_line_rec.source_line_id
AND NVL(ship_sales_order_flag, C_No) = C_No;
'Product transaction records are updated with Shipped Status');
No need to update repairs block when ever its product transaction
line is shipped. One repair order line can have more then one product
transaction line and if we update RO when ever it is released or
shipped then ro_txn_Status will not be of much use.
Update csd_repairs
Set ro_txn_status = 'OM_SHIPPED' ,
object_version_number = Object_Version_Number + 1,
Last_Update_Date = Sysdate,
Last_Updated_By = Fnd_Global.User_Id,
Last_Update_login = Fnd_Global.Login_Id
Where repair_line_id = x_product_txn_rec.repair_line_id;
fnd_message.set_name('CSD','CSD_ERR_REPAIRS_UPDATE');
'No valid product transaction record found to update to shipped status');
ROLLBACK TO update_iro_product_txn;
ROLLBACK TO update_iro_product_txn;
ROLLBACK TO update_iro_product_txn;
END update_iro_product_txn;
SELECT cpt.product_Transaction_Id,
ced.Estimate_Detail_Id,
ced.order_header_Id,
ced.order_Line_Id
FROM csd_product_Transactions cpt, cs_estimate_Details ced
WHERE cpt.repair_line_id = p_Repair_Line_Id
AND cpt.estimate_detail_id = ced.estimate_detail_id;
SELECT dra.status,
dra.Quantity,
dra.Inventory_Item_Id,
drtvl.Repair_Type_Ref,
dra.Repair_Number,
fndl2.meaning
INTO l_RO_Status,
l_RO_Quantity,
l_Inventory_Item_id,
l_RO_Repair_Type_Ref,
l_RO_Number,
l_RO_Status_Meaning
FROM csd_repairs dra, fnd_lookups fndl2, csd_repair_types_vl drtvl
WHERE repair_line_id = p_Repair_line_id
and dra.repair_type_id = drtvl.repair_type_id
and dra.status = fndl2.lookup_code
and fndl2.lookup_type = 'CSD_REPAIR_STATUS';
SELECT Serial_Number_Control_COde
INTO l_Serial_num_COntrol_Code
FROM Mtl_System_Items_B
WHERE Inventory_Item_Id = l_Inventory_Item_Id
AND Organization_id = Cs_Std.Get_Item_Valdn_Orgzn_Id;
SELECT items.concatenated_segments
INTO l_Item_Name
FROM mtl_system_items_vl items, csd_repairs dra
WHERE dra.repair_line_id = p_Repair_Line_Id
and dra.inventory_item_id = items.inventory_item_id
and items.organization_id = cs_std.get_item_valdn_orgzn_id;
SELECT Name
INTO l_Service_Valdn_Org_Name
FROM Hr_Organization_Units
WHERE Organization_id =
Cs_Std.Get_Item_Valdn_Orgzn_Id;
SELECT COUNT(Product_Transaction_Id)
INTO l_Prod_Txn_Lines_Count
FROM csd_product_Transactions
WHERE Repair_Line_id = p_Repair_Line_Id;
Csd_Tasks_Pkg.INSERT_ROW(px_repair_task_id => x_repair_task_id,
p_TASK_ID => p_create_repair_task_rec.task_id,
p_OBJECT_VERSION_NUMBER => 1,
p_REPAIR_LINE_ID => p_create_repair_task_rec.repair_line_id,
p_APPLICABLE_QA_PLANS => l_applicable_qa_plans,
p_CREATED_BY => Fnd_Global.USER_ID,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => Fnd_Global.USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATE_LOGIN => Fnd_Global.USER_ID);
/* procedure name: update_repair_task */
/* description : procedure used to update DR specifc task in Depot tables */
/* Called from : Depot Repair Form to Update DR specifc Task */
/* Input Parm : p_api_version NUMBER Required Api Version number */
/* p_init_msg_list VARCHAR2 Optional Initializes message stack if fnd_api.g_true, */
/* default value is fnd_api.g_false */
/* p_commit VARCHAR2 Optional Commits in API if fnd_api.g_true, default */
/* fnd_api.g_false */
/* p_validation_level NUMBER Optional API uses this parameter to determine which */
/* validation steps must be done and which steps */
/* should be skipped. */
/* CREATE_REPAIR_TASK_REC RECORD Required Columns are in the Record CREATE_REPAIR_TASK_REC_TYPE */
/* Output Parm : x_return_status VARCHAR2 Return status after the call. The status can be*/
/* fnd_api.g_ret_sts_success (success) */
/* fnd_api.g_ret_sts_error (error) */
/* fnd_api.g_ret_sts_unexp_error (unexpected) */
/* x_msg_count NUMBER Number of messages in the message stack */
/* x_msg_data VARCHAR2 Message text if x_msg_count >= 1 */
/*-----------------------------------------------------------------------------------------------------------*/
PROCEDURE UPDATE_REPAIR_TASK(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
p_commit IN VARCHAR2 := Fnd_Api.g_false,
p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
p_update_repair_task_rec IN REPAIR_TASK_REC,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_REPAIR_TASK';
l_update_task_rec Csd_Process_Pvt.REPAIR_TASK_REC := p_update_repair_task_rec;
l_module_name VARCHAR2(240) := 'csd.plsql.csd_process_pvt.update_repair_task';
SAVEPOINT update_repair_task;
p_update_repair_task_rec.task_Id);
p_update_repair_task_rec.repair_line_id);
Csd_Process_Util.check_reqd_param(p_param_value => p_update_repair_task_rec.repair_task_Id,
p_param_name => 'Task Id',
p_api_name => l_api_name);
Csd_Process_Util.check_reqd_param(p_param_value => p_update_repair_task_rec.task_Id,
p_param_name => 'Task Id',
p_api_name => l_api_name);
Csd_Process_Util.check_reqd_param(p_param_value => p_update_repair_task_rec.repair_line_Id,
p_param_name => 'Repair Line Id',
p_api_name => l_api_name);
p_org_id => p_update_repair_task_rec.org_id,
p_context_values => p_update_repair_task_rec.context_values,
x_plan_txn_ids => l_plan_txn_ids);
Csd_Tasks_Pkg.UPDATE_ROW(px_REPAIR_TASK_ID => p_update_repair_task_rec.repair_task_id,
p_task_id => p_update_repair_task_rec.task_id,
p_OBJECT_VERSION_NUMBER => p_update_repair_task_rec.object_version_number,
p_REPAIR_LINE_ID => p_update_repair_task_rec.repair_line_id,
p_APPLICABLE_QA_PLANS => l_applicable_qa_plans,
p_CREATED_BY => Fnd_Global.USER_ID,
p_CREATION_DATE => SYSDATE,
p_LAST_UPDATED_BY => Fnd_Global.USER_ID,
p_LAST_UPDATE_DATE => SYSDATE,
p_LAST_UPDATE_LOGIN => Fnd_Global.USER_ID);
ROLLBACK TO update_repair_task;
ROLLBACK TO update_repair_task;
ROLLBACK TO update_repair_task;
END UPDATE_REPAIR_TASK;