[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT m.inventory_item_id
INTO l_Inventory_Item_ID
FROM mtl_system_items_b m
WHERE inventory_item_Id = p_Inventory_Item_Id
AND m.enabled_flag = 'Y'
AND NVL(m.service_item_flag, 'N') = 'N'
AND m.serv_req_enabled_code = 'E'
AND m.organization_id =
Fnd_Profile.value('CS_INV_VALIDATION_ORG')
AND TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(m.start_date_active, SYSDATE)) AND
TRUNC(NVL(m.end_date_active, SYSDATE));
SELECT tbo.Txn_Billing_Type_ID
INTO l_Txn_Billing_Type_Id
FROM cs_transaction_Types_Vl tt,
cs_Txn_Billing_Types tbt,
cs_bus_process_txns bpt,
cs_Txn_Billing_OETxn_All tbo
WHERE tbt.txn_billing_type_id = p_Txn_Billing_Type_Id
AND tbt.transaction_type_id = tt.transaction_type_id
AND tbt.Billing_Type = 'M'
-- Changing To_Date TO Trunc
AND TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(tbt.start_date_active, SYSDATE)) AND
TRUNC(NVL(tbt.end_date_active, SYSDATE))
AND TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(tt.start_date_active, SYSDATE)) AND
TRUNC(NVL(tt.end_date_active, SYSDATE))
AND tt.depot_repair_flag = 'Y'
AND tt.line_order_Category_code = p_LineOrderCategoryCode
AND tt.transaction_type_Id = bpt.transaction_type_Id
AND bpt.business_process_id = p_BusinessProcessID
AND tbt.txn_billing_Type_Id = tbo.txn_billing_Type_Id
AND tbo.org_id = p_Operating_Unit_Id;
SELECT revision
INTO l_revision
FROM mtl_item_revisions
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = Fnd_Profile.value('CS_INV_VALIDATION_ORG')
AND revision = p_Revision;
SELECT a.serial_number, a.Instance_number
INTO x_serial_number, x_Instance_number
FROM csi_item_instances a,
mtl_system_items_b b,
csi_i_parties cip,
csi_install_parameters ip
WHERE TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(a.active_start_date, SYSDATE)) AND
TRUNC(NVL(a.active_end_date, SYSDATE))
AND b.enabled_flag = 'Y'
-- SU Commented following statement as following where clause depends on profile value
-- AND a.location_type_code in ('HZ_PARTY_SITES', 'HZ_LOCATIONS')
AND a.owner_party_source_table = 'HZ_PARTIES'
AND a.instance_id = cip.instance_id
AND cip.party_source_table = 'HZ_PARTIES'
AND b.inventory_item_id = a.inventory_item_id
AND b.contract_item_type_code IS NULL
AND b.serv_req_enabled_code = 'E'
AND TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(b.start_date_active, SYSDATE)) AND
TRUNC(NVL(b.end_date_active, SYSDATE))
AND b.organization_id = Cs_Std.get_item_valdn_orgzn_id
AND (cip.party_id = NVL(ip.internal_party_id, a.owner_party_id) OR
(cip.party_id = NVL(p_Party_ID, a.owner_party_id) AND
a.owner_party_account_id =
NVL(p_Account_id, a.owner_party_account_id)))
AND a.inventory_item_id = p_inventory_item_id
AND a.Instance_Id = p_Instance_Id
AND cip.relationship_type_code = 'OWNER';
SELECT Lot_Number
INTO l_lot_number
FROM MTL_LOT_NUMBERS
WHERE Inventory_Item_Id = p_inventory_item_id
AND Organization_Id = Cs_Std.get_item_valdn_orgzn_id
AND Lot_Number = p_Lot_Number;
SELECT Current_Status
INTO l_Current_Status
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
-- SU Should not check for current organization
-- AND current_organization_id = cs_std.get_item_valdn_orgzn_id
AND serial_number = p_Serial_Number;
SELECT lookup_code
INTO l_ReasonCode
FROM ar_lookups
WHERE lookup_type = 'CREDIT_MEMO_REASON'
AND lookup_code = p_ReasonCode
AND TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(start_date_active, SYSDATE)) AND
TRUNC(NVL(end_date_active, SYSDATE))
AND NVL(enabled_flag, 'Y') = 'Y';
SELECT lookup_code
INTO l_ReasonCode
FROM fnd_lookups
WHERE lookup_type = 'CSD_REASON'
AND Lookup_Code = p_ReasonCode
AND enabled_flag = 'Y'
AND sysdate BETWEEN nvl(start_date_active,sysdate-1)
AND nvl(end_date_active,sysdate+1) ;
SELECT Unit_of_measure
INTO l_Unit_Of_Measure
FROM mtl_item_uoms_view
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = Cs_Std.get_item_valdn_orgzn_id
AND UOM_Code = p_Unit_Of_Measure
AND uom_type =
(SELECT allowed_units_lookup_code
FROM mtl_system_items_b
WHERE organization_id = Cs_Std.get_item_valdn_orgzn_id
AND inventory_item_id = p_inventory_item_id);
SELECT ps.party_site_id
FROM csd_party_sites_v ps
WHERE ps.site_use_type = p_Site_Use_Type
AND ps.site_status = 'A'
AND ps.site_use_status = 'A'
AND ps.party_id = p_Party_Id
AND ps.Party_Site_ID = p_Party_Site_ID
UNION ALL
SELECT ps.party_site_id
FROM csd_party_sites_v ps
WHERE ps.site_use_type = p_Site_Use_Type
AND ps.Party_Site_Id = p_Party_Site_ID
AND ps.site_status = 'A'
AND ps.site_use_status = 'A'
AND ps.party_id IN
(SELECT d.sub_party_id
FROM csd_hz_rel_v d
WHERE d.obj_party_id = p_Party_ID
AND d.sub_status = 'A'
AND d.sub_party_type IN ('PERSON', 'ORGANIZATION'));
/* p_UpdateProductTrxn_Rec, Logic behind building product txn */
/* rec is that user may pass G_MISS_CHAR value for a varchar2 */
/* column in case user does not want to change existing value */
/* in such cases it is necessary to get database value for */
/* further processing of column value. Similarly for number */
/* and date columns. */
/* Parameters Required: */
/* p_UpdateProductTrxn_Rec IN user input values are stored in this record */
/* x_Product_Txn_Rec IN OUT database values are stored in this record*/
/*---------------------------------------------------------------------------*/
PROCEDURE Build_ProductTxnRec(p_Upd_ProdTxn_Rec IN Csd_Logistics_Pub.Upd_ProdTxn_Rec_Type,
x_Product_Txn_Rec IN OUT NOCOPY Csd_Process_Pvt.Product_Txn_Rec) IS
BEGIN
-- Action_Code
IF (p_Upd_ProdTxn_Rec.action_code <>
x_Product_Txn_Rec.action_code AND
p_Upd_ProdTxn_Rec.action_code <> Fnd_Api.G_MISS_CHAR)
THEN
x_Product_Txn_Rec.action_code := p_Upd_ProdTxn_Rec.action_code;
x_Product_Txn_Rec.Last_Updated_By := Fnd_Global.User_Id;
x_Product_Txn_Rec.Last_Update_Date := SYSDATE;
x_Product_Txn_Rec.Last_Update_Login := Fnd_Global.Login_Id;
SELECT Concatenated_Segments
INTO x_Concatenated_Segments
FROM mtl_system_items_kfv
WHERE Inventory_Item_Id = p_Inventory_item_Id
AND Organization_Id = Fnd_Profile.value('CS_INV_VALIDATION_ORG');
SELECT Current_Status
INTO l_Current_Status
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
-- SU:02/24: While doing Serial Number validation current organization should not be
-- hard coded to item validation organization. So please comment following statement
--AND current_organization_id = cs_std.get_item_valdn_orgzn_id
AND serial_number = p_Serial_Number;
/* structure UpdateProductTrxn_rec, which is an input parameter for */
/* wrapper API CSD_Process_PVt.Update_Product_Txn_Wrapr and set values */
/* in record structure Product_Txn_Rec which is an out parameter */
/* On Error: This procedure is built not to raise any exceptions, as no */
/* exceptions are expected in the body. */
/* Parameters Required: */
/* p_Upd_ProdTxn_Rec IN user input values are stored in this record */
/* x_Product_Txn_Rec IN OUT database values are stored in this record*/
/*---------------------------------------------------------------------------*/
PROCEDURE Set_ProductTrxnRec_Flags(p_Upd_ProdTxn_Rec IN Csd_Logistics_Pub.Upd_ProdTxn_Rec_Type,
x_Product_Txn_Rec IN OUT NOCOPY Csd_Process_Pvt.Product_Txn_Rec) IS
-- Define Local CONSTANTS
C_YES CONSTANT VARCHAR2(1) := 'Y';
/* record structure UpdateProductTrxn_Rec to make sure that values passed */
/* are valid values. This procedure should be called when it is determined */
/* that a specific attribute value can be changed by user. */
/* On Error: X_Return_Status variable will have the return status value */
/* X_Msg_Count will have the count of messages in message stack */
/* X_Msg_Data will have a value if X_Msg_Count has value 1 */
/* Parameters Required: */
/* p_Upd_ProdTxn_Rec IN user input values are stored in this record */
/* x_Product_Txn_Rec IN OUT database values are stored in this record*/
/* x_return_status OUT Standard API paramater */
/* x_msg_count OUT Standard API paramater */
/* x_msg_data OUT Standard API paramater */
/*---------------------------------------------------------------------------*/
PROCEDURE Validate_ProductTrxnRec(p_Upd_ProdTxn_Rec IN Csd_Logistics_Pub.Upd_ProdTxn_Rec_Type,
p_Product_Txn_Rec IN 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
-- Define local Variables
l_ItemAttributes Csd_Logistics_Util.ItemAttributes_Rec_Type;
SELECT sr.Customer_Id, dra.Currency_Code
FROM cs_incidents_b_sec sr, csd_repairs dra
WHERE dra.incident_id = sr.incident_id and dra.Repair_Line_Id = p_Repair_Line_Id;
SELECT Account_Id
FROM CS_INCIDENTS_VL_SEC
--- Csd_Incidents_V
WHERE Incident_Id = p_Incident_Id;
/* UpdateProductTrxn_Rec and Database values captured in Record structure */
/* Product_Txn_rec.This is because whether attributes values can be updated*/
/* depends on product transaction status value and action type values. */
/* These validations are done in the following API and error message is */
/* raised when an attribute value is not supposed to be changed. */
/* On Error : X_Return_Status variable will have the return status value */
/* X_Msg_Count will have the count of messages in message stack */
/* X_Msg_Data will have a value if X_Msg_Count has value 1 */
/* Parameters Required: */
/* p_Upd_ProdTxn_Rec IN user input values are stored in this record */
/* p_Product_Txn_Rec IN database values are stored in this record */
/* x_return_status OUT Standard API paramater */
/* x_msg_count OUT Standard API paramater */
/* x_msg_data OUT Standard API paramater */
/*---------------------------------------------------------------------------*/
PROCEDURE Compare_ProductTrxnRec(p_Upd_ProdTxn_Rec IN Csd_Logistics_Pub.Upd_ProdTxn_Rec_Type,
p_Product_Txn_Rec IN 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
-- Define local variables
l_ProdTxnStatus_Meaning VARCHAR2(80);
SELECT Meaning
FROM Fnd_Lookups
WHERE Lookup_Type = 'CSD_PRODUCT_TXN_STATUS'
AND Lookup_Code = p_ProdTxnStatus_Code;
SELECT serial_number_control_code,
Revision_Qty_Control_Code,
Lot_Control_Code,
NVL(Comms_NL_Trackable_Flag, 'N'),
RESERVABLE_TYPE
INTO x_ItemAttributes.serial_code,
x_ItemAttributes.Revision_Code,
x_ItemAttributes.Lot_Code,
x_ItemAttributes.IB_Flag,
x_itemAttributes.reservable_type
FROM mtl_system_items
WHERE inventory_item_id = p_Inventory_Item_Id
AND organization_id = p_Inv_Org_id;
SELECT customer_id, account_id
FROM cs_incidents_all_b
WHERE incident_id = p_incident_id;
SELECT incident_id
INTO l_incident_id
FROM CSD_REPAIRS
WHERE repair_line_id = p_repair_line_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;
/* Updates the prod txn record in Depot schema and charge line */
/* Parameters Required: */
/* p_product_txn_rec IN product transaction record */
/* x_estimate_detail_id OUT return status */
/*------------------------------------------------------------------------*/
PROCEDURE upd_prodtxn_n_chrgline
(
p_product_txn_rec IN OUT NOCOPY Csd_Process_Pvt.PRODUCT_TXN_REC,
p_prodtxn_db_attr IN Csd_Logistics_Util.PRODTXN_DB_ATTR_REC,
x_estimate_detail_id OUT NOCOPY NUMBER,
x_repair_line_id OUT NOCOPY NUMBER,
x_add_to_order_flag OUT NOCOPY VARCHAR2,
x_add_to_order_id OUT NOCOPY NUMBER,
x_transaction_type_id OUT NOCOPY NUMBER
) IS
l_mod_name VARCHAR2(2000) := 'csd.plsql.csd_logistics_util.upd_prodtxn_n_chrgline';
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);
Debug('At the Beginning of update_depot_prod_txn', l_mod_name, C_STATEMENT_LEVEL);
SELECT 'X'
INTO l_check
FROM fnd_lookups
WHERE lookup_type = 'CSD_PRODUCT_TXN_STATUS'
AND lookup_code = p_product_txn_rec.PROD_TXN_STATUS;
SELECT 'x'
INTO l_check
FROM cs_estimate_details
WHERE estimate_detail_id = l_est_detail_id
AND order_header_id IS NULL;
SELECT business_process_id
INTO l_bus_process_id
FROM cs_estimate_details
WHERE estimate_detail_id = l_est_detail_id;
SELECT transaction_type_id
INTO x_transaction_type_id
FROM cs_txn_billing_types
WHERE txn_billing_type_id =
p_product_txn_rec.txn_billing_type_id;
Debug('Call process_charge_lines to update charge lines ',
l_mod_name,
C_STATEMENT_LEVEL);
p_action => 'UPDATE',
p_Charges_Rec => l_Charges_Rec,
x_estimate_detail_id => l_tmp_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
Debug('Call csd_product_transactions_pkg.update_row to update the prod txn',
l_mod_name,
C_STATEMENT_LEVEL);
Csd_Product_Transactions_Pkg.UPDATE_ROW(p_PRODUCT_TRANSACTION_ID => p_product_txn_rec.PRODUCT_TRANSACTION_ID,
p_REPAIR_LINE_ID => l_REPAIR_LINE_ID,
p_ESTIMATE_DETAIL_ID => l_est_detail_id,
p_ACTION_TYPE => p_product_txn_rec.ACTION_TYPE,
p_ACTION_CODE => p_product_txn_rec.ACTION_CODE,
p_LOT_NUMBER => p_product_txn_rec.LOT_NUMBER,
p_SUB_INVENTORY => p_product_txn_rec.SUB_INVENTORY,
p_INTERFACE_TO_OM_FLAG => Fnd_Api.G_MISS_CHAR,
p_BOOK_SALES_ORDER_FLAG => Fnd_Api.G_MISS_CHAR,
p_RELEASE_SALES_ORDER_FLAG => Fnd_Api.G_MISS_CHAR,
p_SHIP_SALES_ORDER_FLAG => Fnd_Api.G_MISS_CHAR,
p_PROD_TXN_STATUS => Fnd_Api.G_MISS_CHAR,
p_PROD_TXN_CODE => p_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 => p_product_txn_rec.ATTRIBUTE1,
p_ATTRIBUTE2 => p_product_txn_rec.ATTRIBUTE2,
p_ATTRIBUTE3 => p_product_txn_rec.ATTRIBUTE3,
p_ATTRIBUTE4 => p_product_txn_rec.ATTRIBUTE4,
p_ATTRIBUTE5 => p_product_txn_rec.ATTRIBUTE5,
p_ATTRIBUTE6 => p_product_txn_rec.ATTRIBUTE6,
p_ATTRIBUTE7 => p_product_txn_rec.ATTRIBUTE7,
p_ATTRIBUTE8 => p_product_txn_rec.ATTRIBUTE8,
p_ATTRIBUTE9 => p_product_txn_rec.ATTRIBUTE9,
p_ATTRIBUTE10 => p_product_txn_rec.ATTRIBUTE10,
p_ATTRIBUTE11 => p_product_txn_rec.ATTRIBUTE11,
p_ATTRIBUTE12 => p_product_txn_rec.ATTRIBUTE12,
p_ATTRIBUTE13 => p_product_txn_rec.ATTRIBUTE13,
p_ATTRIBUTE14 => p_product_txn_rec.ATTRIBUTE14,
p_ATTRIBUTE15 => p_product_txn_rec.ATTRIBUTE15,
p_CONTEXT => p_product_txn_rec.CONTEXT,
p_OBJECT_VERSION_NUMBER => p_prodtxn_db_attr.object_version_num,
P_SOURCE_SERIAL_NUMBER => p_product_txn_rec.source_serial_number,
P_SOURCE_INSTANCE_ID => p_product_txn_rec.source_instance_id,
P_NON_SOURCE_SERIAL_NUMBER => p_product_txn_rec.non_source_serial_number,
P_NON_SOURCE_INSTANCE_ID => p_product_txn_rec.non_source_Instance_id,
P_REQ_HEADER_ID => p_product_txn_rec.Req_Header_Id,
P_REQ_LINE_ID => p_product_txn_rec.Req_Line_Id,
P_ORDER_HEADER_ID => p_product_txn_rec.Order_Header_Id,
P_ORDER_LINE_ID => p_product_txn_rec.Order_Line_Id,
P_PRD_TXN_QTY_RECEIVED => p_product_txn_rec.Prd_Txn_Qty_Received,
P_PRD_TXN_QTY_SHIPPED => p_product_txn_rec.Prd_Txn_Qty_Shipped,
P_SUB_INVENTORY_RCVD => p_product_txn_rec.Sub_Inventory_Rcvd,
P_LOT_NUMBER_RCVD => p_product_txn_rec.Lot_Number_Rcvd,
P_LOCATOR_ID => p_product_txn_rec.Locator_Id,
--R12 Development Changes
p_picking_rule_id => p_product_txn_rec.picking_rule_id,
P_PROJECT_ID => p_product_txn_rec.project_id,
P_TASK_ID => p_product_txn_rec.task_id,
P_UNIT_NUMBER => p_product_txn_rec.unit_number,
P_INTERNAL_PO_HEADER_ID => p_product_txn_rec.internal_po_header_id); -- swai: bug 6148019
Debug('Updated the prod txn id =' ||
p_product_txn_rec.PRODUCT_TRANSACTION_ID,
l_mod_name,
C_STATEMENT_LEVEL);
SELECT COUNT(*) p_count
FROM 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'
INTO l_dummy
FROM cs_estimate_details
WHERE estimate_detail_id = p_prodtxn_db_attr.est_detail_id
AND order_line_id IS NULL;
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 =
p_product_txn_rec.inventory_item_id;
SELECT 'x'
INTO l_dummy
FROM mtl_item_revisions
WHERE inventory_item_id =
p_product_txn_rec.inventory_item_id
AND organization_id =
Cs_Std.get_item_valdn_orgzn_id
AND revision = p_product_txn_rec.revision;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET prod_txn_status = 'SUBMITTED',
interface_to_om_flag = 'Y'
WHERE product_transaction_id =
p_product_txn_rec.PRODUCT_TRANSACTION_ID;
'CSD_ERR_PRD_TXN_UPDATE');
UPDATE CSD_REPAIRS
SET ro_txn_status = 'OM_SUBMITTED'
WHERE repair_line_id =
p_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 COUNT(*) p_count
FROM PJM_PROJECTS_ORG_V
WHERE project_id = l_project_id and inventory_organization_id = l_p_ship_from_org_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 b.order_header_id,
b.order_line_id,
a.booked_flag
INTO px_order_rec.order_header_id,
l_order_line_id,
l_booked_flag
FROM oe_order_lines_all a, cs_estimate_details b
WHERE a.line_id = b.order_line_id
AND b.estimate_detail_id = p_product_txn_rec.estimate_detail_id;
SELECT ship_from_org_id, unit_selling_price, org_id
INTO l_ship_from_org_id,
l_unit_selling_price,
px_order_rec.org_id
FROM oe_order_lines_all
WHERE line_id = l_order_line_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 projects fields
end if; -- end update 3rd party fields
end if; -- update OM line criteria
Debug('Update the prod txn status to BOOKED',
l_mod_name,
1);
Csd_Update_Programs_Pvt.prod_txn_status_upd(p_repair_line_id => p_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 =
p_product_txn_rec.REPAIR_LINE_ID;
'CSD_ERR_REPAIRS_UPDATE');
Debug('Update the prod txn status to BOOKED',
l_mod_name,
1);
Csd_Update_Programs_Pvt.prod_txn_status_upd(p_repair_line_id => p_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 =
p_product_txn_rec.REPAIR_LINE_ID;
'CSD_ERR_REPAIRS_UPDATE');
/* Adding order_header_id and order_line_id in the select list
for serial reservations change for R12, Vijay June 9th 2006 */
SELECT ship_from_org_id, header_id, line_id
INTO l_ship_from_org_id, l_order_header_id, l_order_line_id
FROM oe_order_lines_all oel,
cs_estimate_details ced
WHERE oel.line_id = ced.order_line_id
AND ced.estimate_detail_id =
p_product_txn_rec.estimate_detail_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 a.released_status,
b.order_header_id
INTO l_released_status,
l_order_header_id
FROM wsh_delivery_details a,
cs_estimate_details b
WHERE a.source_line_id = b.order_line_id
AND b.estimate_detail_id = p_product_txn_rec.estimate_detail_id; */
Select order header id from estimate table directly
*/
SELECT b.order_header_id
INTO l_order_header_id
FROM cs_estimate_details b
WHERE b.estimate_detail_id = p_product_txn_rec.estimate_detail_id;
SELECT count(*)
INTO l_eligible_lines_pick_release
FROM wsh_delivery_details a,
cs_estimate_details b
WHERE a.source_line_id = b.order_line_id
AND b.estimate_detail_id = p_product_txn_rec.estimate_detail_id
/*Fixed for bug#5846054
Added condition SOURCE_CODE = 'OE' while selecting
delivery details from wsh_delivery_details. As per
shipping team there can be multiple delivery lines
with different source code can be created from
inbound deliveries (WSH) and other is from order
management (OE). While doing the pick release Depot
should consider the source code as well.
*/
AND a.SOURCE_CODE = 'OE'
AND a.released_status in ('R','B');
/* SELECT a.released_status
INTO l_released_status
FROM wsh_delivery_details a,
cs_estimate_details b
WHERE a.source_line_id = b.order_line_id
AND b.estimate_detail_id = p_product_txn_rec.estimate_detail_id;*/
then it updates the ship line status
*/
l_eligible_lines_pick_release:=0;
SELECT count(*)
INTO l_eligible_lines_pick_release
FROM wsh_delivery_details a,
cs_estimate_details b
WHERE a.source_line_id = b.order_line_id
AND b.estimate_detail_id = p_product_txn_rec.estimate_detail_id
AND a.released_status in ('R','B','S');
IF (l_eligible_lines_pick_release = 0) THEN /*Bug#5049102 if all delivery are pick released then only update status */
IF (p_product_txn_rec.ACTION_TYPE IN
('SHIP', 'WALK_IN_ISSUE', 'SHIP_THIRD_PTY')) -- swai: 5931926 12.0.2
THEN
UPDATE CSD_PRODUCT_TRANSACTIONS
SET prod_txn_status = 'RELEASED',
release_sales_order_flag = 'Y'
WHERE product_transaction_id =
p_product_txn_rec.PRODUCT_TRANSACTION_ID;
'CSD_ERR_PRD_TXN_UPDATE');
UPDATE CSD_REPAIRS
SET ro_txn_status = 'OM_RELEASED'
WHERE repair_line_id =
p_product_txn_rec.REPAIR_LINE_ID;
'CSD_ERR_REPAIRS_UPDATE');
SELECT b.order_header_id,
b.order_line_id,
c.source_serial_number,
a.ordered_quantity
INTO px_order_rec.order_header_id,
px_order_rec.order_line_id,
px_order_rec.serial_number,
px_order_rec.shipped_quantity
FROM oe_order_lines_all a,
cs_estimate_details b,
CSD_PRODUCT_TRANSACTIONS c
WHERE a.line_id = b.order_line_id
AND b.estimate_detail_id = c.estimate_detail_id
AND b.estimate_detail_id = p_product_txn_rec.estimate_detail_id;
SELECT released_status
INTO l_released_status
FROM wsh_delivery_details
WHERE source_header_id =
px_order_rec.order_header_id
AND source_line_id = px_order_rec.order_line_id;
UPDATE CSD_PRODUCT_TRANSACTIONS
SET prod_txn_status = 'SHIPPED',
ship_sales_order_flag = 'Y'
WHERE product_transaction_id =
p_product_txn_rec.PRODUCT_TRANSACTION_ID;
'CSD_ERR_PRD_TXN_UPDATE');
UPDATE CSD_REPAIRS
SET ro_txn_status = 'OM_SHIPPED'
WHERE repair_line_id =
p_product_txn_rec.REPAIR_LINE_ID;
'CSD_ERR_REPAIRS_UPDATE');
UPDATE CSD_PRODUCT_TRANSACTIONS
SET prod_txn_status = 'SHIPPED',
ship_sales_order_flag = 'Y'
WHERE product_transaction_id =
p_product_txn_rec.PRODUCT_TRANSACTION_ID;
'CSD_ERR_PRD_TXN_UPDATE');
UPDATE CSD_REPAIRS
SET ro_txn_status = 'OM_SHIPPED'
WHERE repair_line_id =
p_product_txn_rec.REPAIR_LINE_ID;
'CSD_ERR_REPAIRS_UPDATE');
UPDATE CSD_PRODUCT_TRANSACTIONS
SET prod_txn_status = 'BOOKED',
book_sales_order_flag = 'Y'
WHERE product_transaction_id =
p_product_txn_rec.PRODUCT_TRANSACTION_ID;
'CSD_ERR_PRD_TXN_UPDATE');
UPDATE CSD_REPAIRS
SET ro_txn_status = 'OM_BOOKED'
WHERE repair_line_id =
p_product_txn_rec.REPAIR_LINE_ID;
'CSD_ERR_REPAIRS_UPDATE');
SELECT lookup_code
FROM oe_lookups
WHERE lookup_type = 'CANCEL_CODE'
AND lookup_code = 'Not provided';
SELECT org_id
FROM oe_order_headers_all
WHERE header_id = p_header_id;
l_Line_Tbl_Type(1).operation := OE_GLOBALS.G_OPR_UPDATE;
--Update the product transactions table with the cancelled status.
--
UPDATE CSD_PRODUCT_TRANSACTIONS
SET prod_txn_status = 'CANCELLED',
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1
WHERE product_transaction_id = p_prod_txn_id;
SELECT Serial_Number from MTL_SERIAL_NUMBERS
WHERE INVENTORY_ITEM_ID = p_inv_item_id AND
CURRENT_ORGANIZATION_ID = p_inv_org_id AND
RESERVATION_ID = p_reservation_Id;
l_from_serial_rsv_tbl.delete;
l_from_serial_rsv_tbl.DELETE;
'Calling update reservation api'
);
INV_RESERVATION_PUB.UPDATE_RESERVATION (
P_API_VERSION_NUMBER => 1,
P_INIT_MSG_LST => FND_API.G_TRUE,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => l_MSG_COUNT,
X_MSG_DATA => l_MSG_DATA,
p_original_rsv_rec => l_orig_rsv_rec,
p_to_rsv_rec => l_rsv_rec,
p_original_serial_number => l_from_serial_rsv_tbl,
p_to_serial_number => l_serial_rsv_tbl
);
l_serial_rsv_tbl.delete;
lx_serial_rsv_tbl.delete;
SELECT Serial_Number from MTL_SERIAL_NUMBERS
WHERE INVENTORY_ITEM_ID = p_inv_item_id AND
CURRENT_ORGANIZATION_ID = p_inv_org_id AND
RESERVATION_ID = p_reservation_Id;
l_serial_rsv_tbl.delete;
INV_RESERVATION_PUB.DELETE_RESERVATION (
P_API_VERSION_NUMBER => 1,
P_INIT_MSG_LST => FND_API.G_FALSE,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => l_MSG_COUNT,
X_MSG_DATA => l_MSG_DATA,
p_rsv_rec => l_rsv_rec,
p_serial_number => l_serial_rsv_tbl
);
dbg_print('After delete..');