[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select hcas.party_site_id
Into x_party_site_id
From hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
Where hcas.cust_acct_site_id = hcsu.cust_acct_site_id
And hcsu.site_use_id = p_cust_site_use_id ;
Select hpsu.party_site_use_id,
hps.party_id
Into x_party_site_use_id,
x_party_id
From Hz_Party_Sites hps,
Hz_Party_Site_uses hpsu,
Hz_Locations hl
Where hps.party_site_id = x_party_site_id
And hpsu.site_use_type = p_site_use_type
And hps.status = C_Site_Status_Active
And hps.location_id = hl.location_id
And hps.party_site_id = hpsu.party_site_id;
Select oeh.order_number order_number,
oeh.header_id order_header_id,
oeh.order_category_code,
oeh.booked_flag,
oeh.cust_po_number purchase_order_num,
NVL(oel.price_list_id,oeh.price_list_id) price_list_id,
oel.line_id ,
oel.inventory_item_id,
oel.line_type_id,
oel.order_quantity_uom,
prh.segment1 requisition_number ,
prh.requisition_header_Id req_Header_Id,
prl.line_num,
prl.requisition_Line_Id req_Line_Id,
prl.quantity requisition_quantity ,
prl.destination_organization_id ,
prl.destination_subinventory ,
prl.source_organization_id ,
prl.source_subinventory ,
prl.quantity,
oeh.transactional_curr_code currency_code,
prl.item_revision,
msi.serial_number_control_code,
msi.concatenated_segments
From Oe_Order_Headers_All oeh,
Oe_Order_Lines_all oel,
po_requisition_headers_all prh,
po_requisition_lines_all prl,
mtl_system_items_kfv msi
Where oeh.header_id = p_ISO_Header_Id_IN
And NVL(oeh.cancelled_flag,'N') = 'N'
And oeh.open_flag = 'Y'
And oeh.booked_flag = 'Y'
And oel.header_id = oeh.header_id
And oel.split_from_line_id is Null
And NVL(oel.cancelled_flag,'N') = 'N'
And oel.open_flag = 'Y'
And oel.booked_flag = 'Y'
And oel.inventory_item_id = msi.inventory_item_id
/*FP Fixed for bug#5368747
To get item attribute the join should be made with ship_from_org_id
from where the item will actually be shipped.
*/
/*And oel.sold_from_org_id = msi.organization_id */
And oel.ship_from_org_id = msi.organization_id
And prh.requisition_header_id = p_req_header_id_In
And prh.requisition_header_id = prl.requisition_header_id
And NVL(prl.cancel_flag,'N') = 'N'
And prh.requisition_header_id = oeh.source_document_id
And prl.requisition_line_id = oel.source_document_line_id
And Not Exists
( Select 'Found Record'
From csd_product_transactions
Where req_header_id = p_req_header_id_In
AND req_line_id > 0 )
And Not Exists
( Select 'Found Record'
From csd_product_transactions
Where order_header_id = p_ISO_header_id_In
AND order_line_id > 0 ) ;
Select oeh.order_number order_number,
oeh.header_id order_header_id,
oeh.order_category_code,
oeh.booked_flag,
NVL(oeh.invoice_to_org_id,oel.invoice_to_org_id) bill_to_site_use_id,
NVL(oeh.ship_to_org_id,oel.ship_to_org_id) ship_to_site_use_id,
oeh.sold_to_org_id cust_account_id,
oel.line_id ,
oel.line_number line_number,
oel.inventory_item_id,
oel.line_type_id,
oel.order_quantity_uom,
oel.ordered_quantity ,
hp.party_type,
hp.party_id,
prh.segment1 requisition_number ,
prl.line_num,
prl.destination_organization_id ,
prl.destination_subinventory ,
prl.source_organization_id ,
prl.source_subinventory ,
prl.quantity,
prl.Requisition_Header_Id Req_Header_ID,
prl.Requisition_Line_ID Req_Line_Id,
msi.serial_number_control_code,
msi.concatenated_segments
From Oe_Order_Headers_All oeh,
Oe_Order_Lines_all oel,
hz_parties hp,
hz_cust_accounts hca,
po_requisition_headers_all prh,
po_requisition_lines_all prl,
mtl_system_items_kfv msi
Where oeh.header_id = p_ISO_Header_Id_Out
And NVL(oeh.cancelled_flag,'N') = 'N'
And oeh.open_flag = 'Y'
And oeh.booked_flag = 'Y'
And oel.header_id = oeh.header_id
And Nvl(oel.cancelled_flag,'N') = 'N'
And oel.open_flag = 'Y'
And oel.booked_flag = 'Y'
And oel.split_from_line_id is Null
And oel.inventory_item_id = msi.inventory_item_id
/*FP Fixed for bug#5368747
To get item attribute the join should be made with ship_from_org_id
from where the item will actually be shipped.
*/
/*And oel.sold_from_org_id = msi.organization_id*/
And oel.ship_from_org_id = msi.organization_id
And oeh.sold_to_org_id = hca.cust_account_id
And hca.party_id = hp.party_id
And prh.requisition_header_id = p_req_header_id_Out
And prh.requisition_header_id = prl.requisition_header_id
And Nvl(prl.cancel_flag,'N') = 'N'
And prh.requisition_header_id = oeh.source_document_id
And prl.requisition_line_id = oel.source_document_line_id
And Not Exists
( Select 'Found Record'
From csd_product_transactions
Where req_header_id = p_req_header_id_Out
AND req_line_id > 0 )
And Not Exists
( Select 'Found Record'
From csd_product_transactions
Where order_header_id = p_ISO_header_id_Out
AND order_line_id > 0 ) ;
Select destination_organization_id
from po_requisition_lines_all
where requisition_header_id = p_requisition_header_id
and requisition_line_id = p_requisition_line_id;
Select incident_status_id Into l_inc_status_Id
From cs_incident_statuses_vl
Where incident_subtype = C_SR_SubType_INC
And seeded_flag = C_YES
And trunc(sysdate) between trunc(nvl(start_date_active,sysdate)) and trunc(nvl(end_date_active,sysdate))
And status_code = C_Status_Open ;
Select repair_mode,Name
into l_repair_mode,l_Repair_Type_Name
from csd_repair_types_vl
where repair_type_id = l_Repair_Type_Id ;
Select 'Y'
into l_serialized_flag
from mtl_system_items
where inventory_item_id = l_IRandISO_In_Rec.inventory_item_id
and organization_id = l_IRandISO_In_Rec.source_organization_id
and serial_number_control_code <> 1;
csd_product_transactions_pkg.Insert_Row(
px_PRODUCT_TRANSACTION_ID => l_Product_Trxn_Id, -- find out if this variable needs to be assigned a value
p_REPAIR_LINE_ID => l_Repair_Line_Id ,
p_ESTIMATE_DETAIL_ID => NULL,
p_ACTION_TYPE => C_ACTION_TYPE_MOVE_IN,
p_ACTION_CODE => C_ACTION_CODE_DEFECTIVES,
p_LOT_NUMBER => NULL,
p_SUB_INVENTORY => l_IRandISO_In_Rec.Source_SubInventory,
p_INTERFACE_TO_OM_FLAG => C_Yes,
p_BOOK_SALES_ORDER_FLAG => C_Yes,
p_RELEASE_SALES_ORDER_FLAG => NULL,
p_SHIP_SALES_ORDER_FLAG => NULL,
p_PROD_TXN_STATUS => C_PROD_TXN_STATUS_BOOKED ,
p_PROD_TXN_CODE => NULL, -- Need to identify what value to be passed
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.Login_Id,
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_CONTEXT => NULL ,
p_OBJECT_VERSION_NUMBER => 1,
p_Req_Header_Id => l_IRandISO_In_Rec.Req_Header_Id,
p_Req_Line_Id => l_IRandISO_In_Rec.Req_Line_Id,
p_Order_Header_Id => l_IRandISO_In_Rec.Order_Header_Id,
p_Order_Line_Id => l_IRandISO_In_Rec.Line_Id ,
p_Prd_Txn_Qty_Received => 0,
p_Prd_Txn_Qty_Shipped => 0 ,
p_Source_Serial_Number => NULL,
p_Source_Instance_Id => Null,
p_Non_Source_Serial_Number => NULL,
p_Non_Source_Instance_Id => Null,
p_Sub_Inventory_Rcvd => Null,
p_Lot_Number_Rcvd => Null,
p_Locator_Id => Null,
p_picking_rule_id => Null,
P_PROJECT_ID => Null,
P_TASK_ID => Null,
P_UNIT_NUMBER => Null);
Fnd_Log.String(Fnd_Log.Level_Error,C_Module || 'Create_InternalRO.Insert_Row',
'Error : While calling procedure csd_product_transactions_pkg.Insert_Row for Defectives');
Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || 'Create_InternalRO.Insert_Row',
'Success : While calling procedure csd_product_transactions_pkg.Insert_Row for Defectives');
csd_product_transactions_pkg.Insert_Row(
px_PRODUCT_TRANSACTION_ID => l_Product_Trxn_Id,
p_REPAIR_LINE_ID => l_Repair_Line_Id ,
p_ESTIMATE_DETAIL_ID => NULL,
p_ACTION_TYPE => C_ACTION_TYPE_MOVE_OUT,
p_ACTION_CODE => C_ACTION_CODE_USABLES,
p_LOT_NUMBER => NULL,
p_SUB_INVENTORY => l_IRandISO_Out_Rec.Destination_SubInventory,
p_INTERFACE_TO_OM_FLAG => C_YEs,
p_BOOK_SALES_ORDER_FLAG => C_YEs,
p_RELEASE_SALES_ORDER_FLAG => NULL,
p_SHIP_SALES_ORDER_FLAG => NULL,
p_PROD_TXN_STATUS => C_PROD_TXN_STATUS_BOOKED,
p_PROD_TXN_CODE => NULL , -- Need to identify what value to be passed
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.Login_Id,
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_CONTEXT => NULL ,
p_OBJECT_VERSION_NUMBER => 1,
p_Req_Header_Id => l_IRandISO_Out_Rec.Req_Header_Id,
p_Req_Line_Id => l_IRandISO_Out_Rec.Req_Line_Id,
p_Order_Header_Id => l_IRandISO_Out_Rec.Order_Header_Id,
p_Order_Line_Id => l_IRandISO_Out_Rec.Line_Id ,
p_Prd_Txn_Qty_Received => NULL, -- changing it from 0
p_Prd_Txn_Qty_Shipped => 0 ,
p_Source_Serial_Number => NULL,
p_Source_Instance_Id => Null,
p_Non_Source_Serial_Number => NULL,
p_Non_Source_Instance_Id => Null,
p_Sub_Inventory_Rcvd => Null,
p_Lot_Number_Rcvd => Null,
p_Locator_Id => Null,
p_picking_rule_id => Null,
P_PROJECT_ID => Null,
P_TASK_ID => Null,
P_UNIT_NUMBER => Null);
Fnd_Log.String(Fnd_Log.Level_Error,C_Module || 'csd_product_transactions_pkg.Insert_Row',
'Error : While calling procedure csd_product_transactions_pkg.Insert_Row for Defectives');
Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || 'Create_InternalRO.Insert_Row',
'Success : While calling procedure csd_product_transactions_pkg.Insert_Row for Usables');
csd_product_transactions_pkg.Insert_Row(
px_PRODUCT_TRANSACTION_ID => l_Product_Trxn_Id,
p_REPAIR_LINE_ID => l_Repair_Line_Id ,
p_ESTIMATE_DETAIL_ID => NULL,
p_ACTION_TYPE => C_ACTION_TYPE_MOVE_IN,
p_ACTION_CODE => C_ACTION_CODE_DEFECTIVES,
p_LOT_NUMBER => NULL,
p_SUB_INVENTORY => l_IRandISO_In_Rec.Source_SubInventory,
p_INTERFACE_TO_OM_FLAG => C_Yes,
p_BOOK_SALES_ORDER_FLAG => C_Yes,
p_RELEASE_SALES_ORDER_FLAG => NULL,
p_SHIP_SALES_ORDER_FLAG => NULL,
p_PROD_TXN_STATUS => C_PROD_TXN_STATUS_BOOKED,
p_PROD_TXN_CODE => NULL,-- Need to identify what value to be passed
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.Login_Id,
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_CONTEXT => NULL ,
p_OBJECT_VERSION_NUMBER => 1,
p_Req_Header_Id => l_IRandISO_In_Rec.Req_Header_Id,
p_Req_Line_Id => l_IRandISO_In_Rec.Req_Line_Id,
p_Order_Header_Id => l_IRandISO_In_Rec.Order_Header_Id,
p_Order_Line_Id => l_IRandISO_In_Rec.Line_Id ,
p_Prd_Txn_Qty_Received => 0,
p_Prd_Txn_Qty_Shipped => 0 ,
p_Source_Serial_Number => NULL,
p_Source_Instance_Id => Null,
p_Non_Source_Serial_Number => NULL,
p_Non_Source_Instance_Id => Null,
p_Sub_Inventory_Rcvd => Null,
p_Lot_Number_Rcvd => Null,
p_Locator_Id => Null,
p_picking_rule_id => Null,
P_PROJECT_ID => Null,
P_TASK_ID => Null,
P_UNIT_NUMBER => Null);
Fnd_Log.String(Fnd_Log.Level_Error,C_Module || 'csd_product_transactions_pkg.Insert_Row',
'Error : While calling procedure csd_product_transactions_pkg.Insert_Row 2 for Defectives');
Fnd_Log.String(Fnd_Log.Level_Error,C_Module || 'csd_product_transactions_pkg.Insert_Row',
'Success : While calling procedure csd_product_transactions_pkg.Insert_Row 2 for Defectives');
csd_product_transactions_pkg.Insert_Row(
px_PRODUCT_TRANSACTION_ID => l_Product_Trxn_Id,
p_REPAIR_LINE_ID => l_Repair_Line_Id ,
p_ESTIMATE_DETAIL_ID => NULL,
p_ACTION_TYPE => C_ACTION_TYPE_MOVE_OUT,
p_ACTION_CODE => C_ACTION_CODE_USABLES,
p_LOT_NUMBER => NULL,
p_SUB_INVENTORY => l_IRandISO_Out_Rec.Destination_SubInventory,
p_INTERFACE_TO_OM_FLAG => C_Yes,
p_BOOK_SALES_ORDER_FLAG => C_Yes,
p_RELEASE_SALES_ORDER_FLAG => NULL,
p_SHIP_SALES_ORDER_FLAG => NULL,
p_PROD_TXN_STATUS => C_PROD_TXN_STATUS_BOOKED,
p_PROD_TXN_CODE => NULL , -- Need to identify what value to be passed
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.Login_Id,
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_CONTEXT => NULL ,
p_OBJECT_VERSION_NUMBER => 1,
p_Req_Header_Id => l_IRandISO_Out_Rec.Req_Header_Id,
p_Req_Line_Id => l_IRandISO_Out_Rec.Req_Line_Id,
p_Order_Header_Id => l_IRandISO_Out_Rec.Order_Header_Id,
p_Order_Line_Id => l_IRandISO_Out_Rec.Line_Id ,
p_Prd_Txn_Qty_Received => NULL, -- changing it from 0
p_Prd_Txn_Qty_Shipped => 0 ,
p_Source_Serial_Number => NULL,
p_Source_Instance_Id => Null,
p_Non_Source_Serial_Number => NULL,
p_Non_Source_Instance_ID => Null,
p_Sub_Inventory_Rcvd => Null,
p_Lot_Number_Rcvd => Null,
p_Locator_Id => Null,
p_picking_rule_id => Null,
P_PROJECT_ID => Null,
P_TASK_ID => Null,
P_UNIT_NUMBER => Null);
Fnd_Log.String(Fnd_Log.Level_Error,C_Module || 'csd_product_transactions_pkg.Insert_Row',
'Error : While calling procedure csd_product_transactions_pkg.Insert_Row 2 for Usables');
Fnd_Log.String(Fnd_Log.Level_Error,C_Module || 'csd_product_transactions_pkg.Insert_Row',
'Error : While calling procedure csd_product_transactions_pkg.Insert_Row 2 for Usables');