[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/** The routine will update the existing charge line quantity*/
/** and then link it to OM line */
/*-----------------------------------------------------------------------*/
procedure after_under_receipt_prcs (p_repair_line_id IN NUMBER,
p_order_header_id IN NUMBER,
p_order_line_id IN NUMBER,
p_received_qty IN NUMBER
);
l_update_charge_err exception;
SELECT estimate_detail_id,
charge_line_type,
org_id,
transaction_inventory_org,
business_process_id,
transaction_type_id,
inventory_item_id,
return_reason_code,
incident_id,
no_charge_flag,
currency_code,
price_list_header_id,
contract_id,
coverage_id,
bill_to_party_id,
--bill_to_account_id,
ship_to_party_id,
ship_to_account_id,
ship_to_org_id
INTO l_Charge_Details_rec.estimate_detail_id,
l_Charge_Details_rec.charge_line_type,
l_Charge_Details_rec.org_id,
l_Charge_Details_rec.transaction_inventory_org,
l_Charge_Details_rec.business_process_id,
l_Charge_Details_rec.transaction_type_id,
l_Charge_Details_rec.inventory_item_id_in,
l_Charge_Details_rec.return_reason_code,
l_Charge_Details_rec.incident_id,
l_Charge_Details_rec.no_charge_flag,
l_Charge_Details_rec.currency_code,
l_Charge_Details_rec.price_list_id,
l_Charge_Details_rec.contract_id,
l_Charge_Details_rec.coverage_id,
l_Charge_Details_rec.bill_to_party_id,
-- l_Charge_Details_rec.bill_to_account_id,
l_Charge_Details_rec.ship_to_party_id,
l_Charge_Details_rec.ship_to_account_id,
l_Charge_Details_rec.ship_to_org_id
FROM cs_estimate_details
WHERE source_id = p_repair_line_id
AND order_header_id = p_order_header_id
AND order_line_id = p_order_line_id;
update cs_estimate_details
set order_line_id = null --, order_header_id = null
where estimate_detail_id = l_Charge_Details_rec.estimate_detail_id;
'Under-receipt: Calling update charges API to update the quantity');
savepoint update_charge;
CS_Charge_Details_PUB.Update_Charge_Details
(
p_api_version => 1.0,
p_init_msg_list => 'F',
p_commit => 'F',
p_validation_level => 100,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_object_version_number => x_object_version_number,
x_msg_data => x_msg_data,
p_transaction_control => 'T',
p_Charges_Rec => l_Charge_Details_rec_upd
--p_update_cost_detail => 'N'
);
raise l_update_charge_err;
update cs_estimate_details
set order_header_id = p_order_header_id, order_line_id = p_order_line_id
where estimate_detail_id = l_Charge_Details_rec.estimate_detail_id;
select oel2.line_id,
oel2.ordered_quantity,
oel2.order_quantity_uom
into l_Charge_Details_rec.order_line_id,
l_Charge_Details_rec.quantity_required,
l_Charge_Details_rec.unit_of_measure_code
from oe_order_lines_all oel1,
oe_order_lines_all oel2
where oel1.line_id = p_order_line_id
and oel1.line_set_id = oel2.line_set_id
and oel2.line_id <> p_order_line_id
and oel2.flow_status_code = 'AWAITING_RETURN';
select
CSD_PRODUCT_TRANSACTIONS_S1.nextval,
REPAIR_LINE_ID,
ACTION_TYPE,
ACTION_CODE,
LOT_NUMBER,
SUB_INVENTORY,
INTERFACE_TO_OM_FLAG,
BOOK_SALES_ORDER_FLAG,
RELEASE_SALES_ORDER_FLAG,
SHIP_SALES_ORDER_FLAG,
'BOOKED',
PROD_TXN_CODE,
SYSDATE,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CONTEXT,
1,
REQ_HEADER_ID ,
REQ_LINE_ID ,
ORDER_HEADER_ID ,
SOURCE_SERIAL_NUMBER ,
SOURCE_INSTANCE_ID ,
NON_SOURCE_SERIAL_NUMBER ,
NON_SOURCE_INSTANCE_ID ,
LOCATOR_ID ,
PICKING_RULE_ID,
PROJECT_ID,
TASK_ID,
UNIT_NUMBER,
INTERNAL_PO_HEADER_ID
into
l_prod_txns_rec.product_transaction_id,
l_prod_txns_rec.REPAIR_LINE_ID,
l_prod_txns_rec.ACTION_TYPE,
l_prod_txns_rec.ACTION_CODE,
l_prod_txns_rec.LOT_NUMBER,
l_prod_txns_rec.SUB_INVENTORY,
l_prod_txns_rec.INTERFACE_TO_OM_FLAG,
l_prod_txns_rec.BOOK_SALES_ORDER_FLAG,
l_prod_txns_rec.RELEASE_SALES_ORDER_FLAG,
l_prod_txns_rec.SHIP_SALES_ORDER_FLAG,
l_prod_txns_rec.PROD_TXN_STATUS,
l_prod_txns_rec.PROD_TXN_CODE,
l_prod_txns_rec.LAST_UPDATE_DATE,
l_prod_txns_rec.CREATION_DATE,
l_prod_txns_rec.LAST_UPDATED_BY,
l_prod_txns_rec.CREATED_BY,
l_prod_txns_rec.LAST_UPDATE_LOGIN,
l_prod_txns_rec.ATTRIBUTE1,
l_prod_txns_rec.ATTRIBUTE2,
l_prod_txns_rec.ATTRIBUTE3,
l_prod_txns_rec.ATTRIBUTE4,
l_prod_txns_rec.ATTRIBUTE5,
l_prod_txns_rec.ATTRIBUTE6,
l_prod_txns_rec.ATTRIBUTE7,
l_prod_txns_rec.ATTRIBUTE8,
l_prod_txns_rec.ATTRIBUTE9,
l_prod_txns_rec.ATTRIBUTE10,
l_prod_txns_rec.ATTRIBUTE11,
l_prod_txns_rec.ATTRIBUTE12,
l_prod_txns_rec.ATTRIBUTE13,
l_prod_txns_rec.ATTRIBUTE14,
l_prod_txns_rec.ATTRIBUTE15,
l_prod_txns_rec.CONTEXT,
l_prod_txns_rec.OBJECT_VERSION_NUMBER,
l_prod_txns_rec.REQ_HEADER_ID ,
l_prod_txns_rec.REQ_LINE_ID ,
l_prod_txns_rec.ORDER_HEADER_ID ,
l_prod_txns_rec.SOURCE_SERIAL_NUMBER ,
l_prod_txns_rec.SOURCE_INSTANCE_ID ,
l_prod_txns_rec.NON_SOURCE_SERIAL_NUMBER ,
l_prod_txns_rec.NON_SOURCE_INSTANCE_ID ,
l_prod_txns_rec.LOCATOR_ID ,
l_prod_txns_rec.PICKING_RULE_ID,
l_prod_txns_rec.PROJECT_ID,
l_prod_txns_rec.TASK_ID,
l_prod_txns_rec.UNIT_NUMBER,
l_prod_txns_rec.INTERNAL_PO_HEADER_ID
from csd_product_transactions
where estimate_detail_id = l_charge_details_rec_upd.estimate_detail_id;
'Under-receipt: before calling insert: product txn id :'||l_prod_txns_rec.product_transaction_id);
INSERT INTO CSD_PRODUCT_TRANSACTIONS(
PRODUCT_TRANSACTION_ID,
REPAIR_LINE_ID,
ESTIMATE_DETAIL_ID,
ACTION_TYPE,
ACTION_CODE,
LOT_NUMBER,
SUB_INVENTORY,
INTERFACE_TO_OM_FLAG,
BOOK_SALES_ORDER_FLAG,
RELEASE_SALES_ORDER_FLAG,
SHIP_SALES_ORDER_FLAG,
PROD_TXN_STATUS,
PROD_TXN_CODE,
LAST_UPDATE_DATE,
CREATION_DATE,
LAST_UPDATED_BY,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CONTEXT,
OBJECT_VERSION_NUMBER,
REQ_HEADER_ID ,
REQ_LINE_ID ,
ORDER_HEADER_ID ,
SOURCE_SERIAL_NUMBER ,
SOURCE_INSTANCE_ID ,
NON_SOURCE_SERIAL_NUMBER ,
NON_SOURCE_INSTANCE_ID ,
LOCATOR_ID ,
PICKING_RULE_ID,
PROJECT_ID,
TASK_ID,
UNIT_NUMBER,
INTERNAL_PO_HEADER_ID
) VALUES (
l_prod_txns_rec.product_transaction_id,
l_prod_txns_rec.REPAIR_LINE_ID,
l_prod_txns_rec.estimate_detail_id,
l_prod_txns_rec.ACTION_TYPE,
l_prod_txns_rec.ACTION_CODE,
l_prod_txns_rec.LOT_NUMBER,
l_prod_txns_rec.SUB_INVENTORY,
l_prod_txns_rec.INTERFACE_TO_OM_FLAG,
l_prod_txns_rec.BOOK_SALES_ORDER_FLAG,
l_prod_txns_rec.RELEASE_SALES_ORDER_FLAG,
l_prod_txns_rec.SHIP_SALES_ORDER_FLAG,
l_prod_txns_rec.PROD_TXN_STATUS,
l_prod_txns_rec.PROD_TXN_CODE,
l_prod_txns_rec.LAST_UPDATE_DATE,
l_prod_txns_rec.CREATION_DATE,
l_prod_txns_rec.LAST_UPDATED_BY,
l_prod_txns_rec.CREATED_BY,
l_prod_txns_rec.LAST_UPDATE_LOGIN,
l_prod_txns_rec.ATTRIBUTE1,
l_prod_txns_rec.ATTRIBUTE2,
l_prod_txns_rec.ATTRIBUTE3,
l_prod_txns_rec.ATTRIBUTE4,
l_prod_txns_rec.ATTRIBUTE5,
l_prod_txns_rec.ATTRIBUTE6,
l_prod_txns_rec.ATTRIBUTE7,
l_prod_txns_rec.ATTRIBUTE8,
l_prod_txns_rec.ATTRIBUTE9,
l_prod_txns_rec.ATTRIBUTE10,
l_prod_txns_rec.ATTRIBUTE11,
l_prod_txns_rec.ATTRIBUTE12,
l_prod_txns_rec.ATTRIBUTE13,
l_prod_txns_rec.ATTRIBUTE14,
l_prod_txns_rec.ATTRIBUTE15,
l_prod_txns_rec.CONTEXT,
l_prod_txns_rec.OBJECT_VERSION_NUMBER,
l_prod_txns_rec.REQ_HEADER_ID ,
l_prod_txns_rec.REQ_LINE_ID ,
l_prod_txns_rec.ORDER_HEADER_ID ,
l_prod_txns_rec.SOURCE_SERIAL_NUMBER ,
l_prod_txns_rec.SOURCE_INSTANCE_ID ,
l_prod_txns_rec.NON_SOURCE_SERIAL_NUMBER ,
l_prod_txns_rec.NON_SOURCE_INSTANCE_ID ,
l_prod_txns_rec.LOCATOR_ID ,
l_prod_txns_rec.PICKING_RULE_ID,
l_prod_txns_rec.PROJECT_ID,
l_prod_txns_rec.TASK_ID,
l_prod_txns_rec.UNIT_NUMBER,
l_prod_txns_rec.INTERNAL_PO_HEADER_ID) ;
WHEN l_update_charge_err THEN
-- write the error message to bulk receive log.
write_to_conc_log(x_msg_count,x_msg_data);
rollback to update_charge;
select serial_number_control_code,
comms_nl_trackable_flag
from mtl_system_items_kfv
where inventory_item_id = p_inventory_item_id
and organization_id = cs_std.get_item_valdn_orgzn_id;
Select
owner_party_id,
instance_id
from csi_item_instances
where serial_number = p_serial_number
and inventory_item_id = p_inventory_item_id;
Select
inventory_item_id
from mtl_serial_numbers
where serial_number = p_serial_number
and inventory_item_id = p_inventory_item_id;
SELECT relationship_type_code
INTO l_relationship
FROM csi_i_parties
WHERE instance_id = l_instance_id
AND party_id = p_party_id
AND SYSDATE BETWEEN NVL(active_start_date, SYSDATE - 1) AND NVL(active_end_date,SYSDATE+1)
AND relationship_type_code = NVL(l_crt_inst_party_relation,relationship_type_code)
AND ROWNUM < 2;
select party_type from hz_parties
where party_id = p_party_id;
Select hpu.party_site_use_id
from hz_party_sites hps,
hz_party_site_uses hpu
where
hps.party_id = p_party_id
and hps.party_site_id = hpu.party_site_id
and hpu.site_use_type = 'BILL_TO'
and hpu.primary_per_type = 'Y';
Select hpu.party_site_use_id
from hz_party_sites hps,
hz_party_site_uses hpu
where
hps.party_id = p_party_id
and hps.party_site_id = hpu.party_site_id
and hpu.site_use_type = 'SHIP_TO'
and hpu.primary_per_type = 'Y';
Select * from csd_bulk_receive_items_b
where bulk_receive_id = p_bulk_receive_id;
Select serial_number_control_code,
comms_nl_trackable_flag,
revision_qty_control_code
from mtl_system_items_kfv
where inventory_item_id = p_inventory_item_id
and organization_id = cs_std.get_item_valdn_orgzn_id;
Select price_list_header_id,
repair_mode,
business_process_id
from csd_repair_types_b
where repair_type_id = p_repair_type_id;
Select
owner_party_id,
instance_id
from csi_item_instances
where serial_number = p_serial_number
and inventory_item_id = p_inventory_item_id;
Select
revision
from mtl_serial_numbers
where serial_number = p_serial_number
and inventory_item_id = p_inventory_item_id;
Select ship_to_site_use_id
from cs_incidents_all_b
where incident_id = p_incident_id;
Select primary_uom_code
from mtl_system_items_kfv
where inventory_item_id = p_inventory_item_id
and organization_id = cs_std.get_item_valdn_orgzn_id;
Select customer_id,account_id,incident_date,
incident_severity_id,contract_id,contract_service_id
from csd_incidents_v
where incident_id = p_incident_id;
Select location_id
from csi_item_instances
where instance_id = p_instance_id;
select item_revision,lot_number
into l_revision,l_lot_num
from csd_bulk_receive_items_b
where bulk_receive_id = p_bulk_receive_id;
select * from csd_bulk_receive_items_b
where bulk_receive_id = p_bulk_receive_id;
Select instance_party_id,
object_version_number
from csi_i_parties
where instance_id = p_instance_id
and relationship_type_code = 'OWNER';
Select object_version_number from csi_item_instances
where instance_id = p_instance_id;
select * from csd_bulk_receive_items_b
where bulk_receive_id = p_bulk_receive_id;
Select ip_account_id,
object_version_number
from csi_ip_accounts
where instance_party_id = p_instance_party_id;
Select instance_id
from csi_item_instances
where serial_number = p_serial_number
and inventory_item_id = p_inventory_item_id;
SELECT bill_to_address,ship_to_address
FROM CSI_IP_ACCOUNTS
WHERE INSTANCE_PARTY_ID =
(SELECT instance_party_id FROM CSI_I_PARTIES
WHERE INSTANCE_ID=p_instance_id
AND relationship_type_code='OWNER');
csi_item_instance_pub.update_item_instance
(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_instance_rec,
p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
p_party_tbl => l_party_tbl,
p_account_tbl => l_account_tbl,
p_pricing_attrib_tbl => l_pricing_attrib_tbl,
p_org_assignments_tbl => l_org_assignments_tbl,
p_asset_assignment_tbl => l_asset_assignment_tbl,
p_txn_rec => l_txn_rec,
x_instance_id_lst => x_instance_id_lst,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
select
cib.customer_id,
cib.account_id, -- Fix for bug#5848406
cpt.estimate_quantity,
cpt.unit_of_measure,
cpt.inventory_item_id,
cpt.order_header_id,
cpt.order_line_id,
cpt.order_number,
cpt.serial_number,
mtl.concatenated_segments item_name,
-- subhat, 12.2 changes
cpt.revision,
cpt.lot_number
-- end 12.2 changes, subhat
from
csd_product_txns_v cpt,
cs_incidents_all_b cib,
csd_repairs cr,
mtl_system_items_kfv mtl
where cpt.repair_line_id = p_repair_line_id
and cr.repair_line_id = cpt.repair_line_id
and cib.incident_id = cr.incident_id
and cpt.order_header_id = p_order_header_id
and cpt.order_line_id = p_order_line_id
and mtl.inventory_item_id = cpt.inventory_item_id
and mtl.organization_id = cs_std.get_item_valdn_orgzn_id;
Select nvl(b.ship_from_org_id,a.ship_from_org_id)
from oe_order_headers_all a,
oe_order_lines_all b
where a.header_id = b.header_id
and a.header_id = p_order_header_id;
Select prod_txn_status
from csd_product_transactions
where repair_line_id = p_repair_line_id
and action_type = 'RMA';
Update csd_bulk_receive_items_b
set status = 'ERRORED'
where bulk_receive_id = p_bulk_autorcv_tbl(i).bulk_receive_id;
Update csd_bulk_receive_items_b
set status = 'ERRORED'
where bulk_receive_id = p_bulk_autorcv_tbl(i).bulk_receive_id;
CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE
( p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => 0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_internal_order_flag => 'N',
p_order_header_id => null,
p_repair_line_id => p_bulk_autorcv_tbl(i).repair_line_id);
Fnd_file.put_line(fnd_file.log,'Error : CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE failed');
Update csd_bulk_receive_items_b
set status = 'PROCESSED'
where bulk_receive_id = p_bulk_autorcv_tbl(i).bulk_receive_id;
Update csd_bulk_receive_items_b
set status = 'ERRORED'
where bulk_receive_id = p_bulk_autorcv_tbl(i).bulk_receive_id;
CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE
( p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => 0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_internal_order_flag => 'N',
p_order_header_id => null,
p_repair_line_id => p_bulk_autorcv_tbl(i).repair_line_id);
Fnd_file.put_line(fnd_file.log,'Error : CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE failed');
Update csd_bulk_receive_items_b
set status = 'PROCESSED'
where bulk_receive_id = p_bulk_autorcv_tbl(i).bulk_receive_id;
Select *
from csd_bulk_receive_items_b
where transaction_number = p_transaction_number;
Select incident_number
from cs_incidents_all_b
where incident_id = p_incident_id;
Select repair_number
,status
from csd_repairs
where repair_line_id = p_repair_line_id;
Select concatenated_segments
from mtl_system_items_kfv
where inventory_item_id = p_inventory_item_id;
select customer_id,
account_id,
bill_to_site_use_id,
ship_to_site_use_id,
inventory_item_id,
category_id,
contract_id,
problem_code,
customer_product_id
from CS_INCIDENTS_ALL_VL
where incident_id = p_incident_id;
lc_sql_string_ro varchar2(2000) :=' select sr.incident_id,cr.repair_line_id,cr.quantity,''N'',''N''' ||
' from cs_incidents_all_b sr,csd_repairs cr,csd_repair_types_b crt '||
' where cr.inventory_item_id = :p_inv_item_id '||
' and cr.status = ''O'' '||
' and cr.incident_id = sr.incident_id '||
' and sr.account_id = :p_acc_id '||
' and sr.customer_id = :p_party_id '||
' AND cr.repair_type_id = crt.repair_type_id '||
' and not exists ( '||
' select repair_line_id '||
' from csd_product_transactions cpt '||
' where crt.repair_type_ref <> ''ARR'' '||
' and cpt.repair_line_id = cr.repair_line_id '||
' and cpt.action_type = ''RMA'' '||
' and cpt.prod_txn_status in (''CANCELLED'' ,''RECEIVED'') '||
' UNION ALL '||
' SELECT repair_line_id '||
' from csd_product_transactions cpt1' ||
' where crt.repair_type_ref = ''ARR''' ||
' AND cpt1.repair_line_id = cr.repair_line_id '||
' AND cpt1.action_type = ''RMA'' '||
' AND ((cpt1.action_code = ''LOANER'' AND cpt1.prod_txn_status IN (''CANCELLED'',''RECEIVED''))AND ' ||
' (cpt1.action_code = ''CUST_PROD'' AND cpt1.prod_txn_status IN (''CANCELLED'',''RECEIVED'')) )) ';
l_ro_query_sql varchar2(2000) := ' select repair_line_id from csd_repairs where '||
' incident_id := :b_incident_id and '||
' status := ''O'' ' ;
select repair_line_id
bulk collect into l_in_progress_ro
from csd_bulk_receive_items_b
where transaction_number in
(
select argument1
from fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp
where fcp.concurrent_program_name = 'CSDBLKRCV'
and fcp.application_id = 512
and fcr.program_application_id = fcp.application_id
and fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.status_code <> 'C'
);
l_sql_string_tmp := 'select repair_line_id,incident_id,''N'',''N'' from (' ||lc_sql_string_ro||
' and cr.serial_number = :p_serial_number '||
' and cr.customer_product_id = :p_instance_id '||
' order by cr.creation_date desc ) where rownum = 1' ;
l_sql_string_tmp := ' select repair_line_id,incident_id,''N'',''N'' from ( '||
lc_sql_string_ro||' and cr.serial_number = :p_serial_number '||
' order by cr.creation_date desc ) where rownum = 1 ';
l_sql_string_tmp := ' select incident_id,repair_line_id,''N'',''N'' from ('||lc_sql_string_ro||
' and cr.quantity = :p_quantity order by sr.incident_id desc ) where rownum = 1';
select incident_id,repair_line_id, create_sr_flag,create_ro_flag
into x_sr_ro_rma_tbl(l_counter).incident_id,x_sr_ro_rma_tbl(l_counter).repair_line_id,
x_sr_ro_rma_tbl(l_counter).create_sr_flag,x_sr_ro_rma_tbl(l_counter).create_ro_flag
from (
select sr.incident_id,cr.repair_line_id,'N' create_sr_flag,'N' create_ro_flag
from csd_repairs cr,cs_incidents_all_b sr
where cr.inventory_item_id = x_sr_ro_rma_tbl(l_counter).inventory_item_id
and cr.status = 'O'
and cr.incident_id = sr.incident_id
and sr.account_id = x_sr_ro_rma_tbl(l_counter).cust_acct_id
and sr.customer_id = x_sr_ro_rma_tbl(l_counter).party_id
and not exists (
select repair_line_id
from csd_product_transactions cpt
where cpt.repair_line_id = cr.repair_line_id
and cpt.action_type = 'RMA'
and cpt.prod_txn_status in ('RECEIVED','CANCELLED'))
and cr.quantity = x_sr_ro_rma_tbl(l_counter).quantity
and cr.repair_line_id not in (
select * from TABLE(cast(get_num_in_list(l_repair_line_ids_in) as JTF_NUMBER_TABLE))
union all select * from table(cast(l_in_progress_ro as JTF_NUMBER_TABLE)))
order by cr.creation_date desc ) where rownum = 1;
SELECT sr.incident_id ,
cr.repair_line_id ,
'N' ,
'N'
into
x_sr_ro_rma_tbl(l_counter).incident_id,
x_sr_ro_rma_tbl(l_counter).repair_line_id,
x_sr_ro_rma_tbl(l_counter).create_sr_flag,
x_sr_ro_rma_tbl(l_counter).create_ro_flag
from csd_repairs cr,
csd_product_transactions cpt,
cs_incidents_all_b sr,
cs_estimate_details ced
where cr.incident_id = sr.incident_id
and sr.account_id = x_sr_ro_rma_tbl(l_counter).cust_acct_id
and sr.customer_id = x_sr_ro_rma_tbl(l_counter).party_id
and cpt.repair_line_id = cr.repair_line_id
and cpt.estimate_detail_id = ced.estimate_detail_id
and abs(ced.quantity_required) = x_sr_ro_rma_tbl(l_counter).quantity
and ced.inventory_item_id = x_sr_ro_rma_tbl(l_counter).inventory_item_id
and cpt.action_type = 'SHIP'
and cpt.action_code = 'LOANER'
and cpt.prod_txn_status = 'SHIPPED'
and not exists
( select 'Y' from csd_product_transactions cpt1
where cpt1.repair_line_id = cpt.repair_line_id
and cpt1.action_type = 'RMA'
and cpt1.action_code = 'LOANER'
and cpt1.prod_txn_status in ('RECEIVED','CANCELLED')
)
and cr.repair_line_id not in (
select * from TABLE(cast(get_num_in_list(l_repair_line_ids_in) as JTF_NUMBER_TABLE))
union all select * from table(cast(l_in_progress_ro as JTF_NUMBER_TABLE)));
l_sql_string_tmp := ' select incident_id,repair_line_id, quantity,''N'',''N'' from ('||lc_sql_string_ro||
' order by cr.creation_date desc ) where rownum = 1 ';*/
select incident_id,repair_line_id, create_sr_flag,create_ro_flag
into x_sr_ro_rma_tbl(l_counter).incident_id,x_sr_ro_rma_tbl(l_counter).repair_line_id,
x_sr_ro_rma_tbl(l_counter).create_sr_flag,x_sr_ro_rma_tbl(l_counter).create_ro_flag
from(
select sr.incident_id,cr.repair_line_id,'N' create_sr_flag,'N' create_ro_flag
from csd_repairs cr,cs_incidents_all_b sr
where cr.inventory_item_id = x_sr_ro_rma_tbl(l_counter).inventory_item_id
and cr.status = 'O'
and cr.incident_id = sr.incident_id
and sr.account_id = x_sr_ro_rma_tbl(l_counter).cust_acct_id
and sr.customer_id = x_sr_ro_rma_tbl(l_counter).party_id
and not exists (
select repair_line_id
from csd_product_transactions cpt
where cpt.repair_line_id = cr.repair_line_id
and cpt.action_type = 'RMA'
and cpt.prod_txn_status in ('RECEIVED','CANCELLED'))
and cr.repair_line_id not in (
select * from TABLE(cast(get_num_in_list(l_repair_line_ids_in) as JTF_NUMBER_TABLE))
union all select * from table(cast(l_in_progress_ro as JTF_NUMBER_TABLE)))
order by cr.creation_date desc) where rownum = 1;
SELECT sr.incident_id ,
cr.repair_line_id ,
'N' ,
'N'
INTO
x_sr_ro_rma_tbl(l_counter).incident_id,
x_sr_ro_rma_tbl(l_counter).repair_line_id,
x_sr_ro_rma_tbl(l_counter).create_sr_flag,
x_sr_ro_rma_tbl(l_counter).create_ro_flag
FROM csd_repairs cr,
csd_product_transactions cpt,
cs_incidents_all_b sr
WHERE cr.incident_id = sr.incident_id
AND sr.account_id = x_sr_ro_rma_tbl(l_counter).cust_acct_id
AND sr.customer_id = x_sr_ro_rma_tbl(l_counter).party_id
AND cpt.repair_line_id = cr.repair_line_id
AND cpt.source_serial_number = x_sr_ro_rma_tbl(l_counter).serial_number
AND cpt.action_type = 'SHIP'
AND cpt.action_code = 'LOANER'
AND cpt.prod_txn_status = 'SHIPPED'
AND NOT EXISTS
( SELECT 'Y' FROM csd_product_transactions cpt1
WHERE cpt1.repair_line_id = cpt.repair_line_id
AND cpt1.action_type = 'RMA'
AND cpt1.action_code = 'LOANER'
AND cpt1.prod_txn_status in ('RECEIVED','CANCELLED')
)
and cr.repair_line_id not in (
select * from TABLE(cast(get_num_in_list(l_repair_line_ids_in) as JTF_NUMBER_TABLE))
union all select * from table(cast(l_in_progress_ro as JTF_NUMBER_TABLE)));
l_ro_query_sql := 'select repair_line_id from ( '||l_ro_query_sql||
' ) where rownum = 1 ';
select cpt.prod_txn_status,
cpt.source_serial_number,
cpt.source_instance_id,
abs(ced.quantity_required) rma_quantity,
ool.header_id,
ool.line_id,
ool.inventory_item_id
bulk collect into
l_matching_rma_tbl
from csd_product_transactions cpt,
cs_estimate_details ced,
oe_order_lines_all ool,
csd_repairs cr,
csd_repair_types_b crtb
where
cpt.repair_line_id = p_repair_line_id and
cpt.action_type = 'RMA' and
crtb.repair_type_id = cr.repair_type_id and
cpt.action_code = decode(crtb.repair_type_ref,'RR','CUST_PROD','ARR','CUST_PROD','E','EXCHANGE','AE','EXCHANGE','CUST_PROD') and
cpt.prod_txn_status <> 'RECEIVED' and
cpt.estimate_detail_id = ced.estimate_detail_id and
ced.order_header_id = ool.header_id and
ced.order_line_id = ool.line_id and
cr.repair_line_id = cpt.repair_line_id and
decode(cr.serial_number,null,'-1',cr.serial_number) = decode(cpt.source_serial_number,null,'-1',cpt.source_serial_number);
select cpt.prod_txn_status,
cpt.product_transaction_id,
ced.estimate_detail_id,
ced.inventory_item_id,
ced.incident_id,
ced.invoice_to_org_id,
ced.ship_to_org_id,
ced.org_id,
ced.transaction_inventory_org
into l_prod_txn_rec.prod_txn_status,
l_prod_txn_rec.product_transaction_id,
l_prod_txn_rec.estimate_detail_id,
l_prod_txn_rec.inventory_item_id,
l_prod_txn_rec.incident_id,
l_prod_txn_rec.invoice_to_org_id,
l_prod_txn_rec.ship_to_org_id,
l_prod_txn_rec.organization_id,
l_prod_txn_rec.inventory_org_id
from csd_product_transactions cpt,
cs_estimate_details ced
where cpt.repair_line_id = p_repair_line_id and
cpt.action_type = 'RMA' and
cpt.action_code in ('CUST_PROD','EXCHANGE') and
cpt.prod_txn_status <> 'RECEIVED' and
cpt.estimate_detail_id = ced.estimate_detail_id and
nvl(cpt.interface_to_om_flag,'N') = 'N' and
rownum < 2;
select cpt.prod_txn_status,
cpt.source_serial_number,
cpt.source_instance_id,
abs(ced.quantity_required) rma_quantity,
ool.header_id,
ool.line_id,
ool.inventory_item_id
bulk collect into
l_matching_rma_tbl
from csd_product_transactions cpt,
cs_estimate_details ced,
oe_order_lines_all ool,
csd_repairs cr
where
cpt.repair_line_id = p_repair_line_id and
cpt.action_type = 'RMA' and
cpt.action_code = 'LOANER' and
cpt.prod_txn_status <> 'RECEIVED' and
cpt.estimate_detail_id = ced.estimate_detail_id and
ced.order_header_id = ool.header_id and
ced.order_line_id = ool.line_id and
cr.repair_line_id = cpt.repair_line_id ;
select 'Y'
into x_rma_found
from csd_product_transactions cpt,
cs_estimate_details ced
where cpt.repair_line_id = p_repair_line_id and
cpt.action_type = 'RMA' and
cpt.action_code = 'LOANER' and
cpt.prod_txn_status <> 'RECEIVED' and
cpt.estimate_detail_id = ced.estimate_detail_id and
nvl(cpt.interface_to_om_flag,'N') = 'N' and
rownum < 2;
select cpt.action_type,cpt.action_code,
cpt.picking_rule_id,cpt.project_id,
cpt.task_id,cpt.unit_number,
ced.inventory_item_id,ced.unit_of_measure_code,
ced.contract_id,ced.coverage_id,
ced.price_list_header_id,ced.txn_billing_type_id,
ced.business_process_id,ced.currency_code,
ced.ship_to_party_id,ced.ship_to_account_id,
ced.return_reason_code,ced.contract_line_id
into l_prod_txn_rec.action_type,l_prod_txn_rec.action_code,
l_prod_txn_rec.picking_rule_id,l_prod_txn_rec.project_id,
l_prod_txn_rec.task_id,l_prod_txn_rec.unit_number,
l_prod_txn_rec.inventory_item_id,l_prod_txn_rec.unit_of_measure_code,
l_prod_txn_rec.contract_id,l_prod_txn_rec.coverage_id,
l_prod_txn_rec.price_list_id,l_prod_txn_rec.txn_billing_type_id,
l_prod_txn_rec.business_process_id,l_prod_txn_rec.currency_code,
l_prod_txn_rec.ship_to_party_id,l_prod_txn_rec.ship_to_account_id,
l_prod_txn_rec.return_reason,l_prod_txn_rec.contract_line_id
from csd_product_transactions cpt,
cs_estimate_details ced
where cpt.repair_line_id = p_repair_line_id and
ced.estimate_detail_id = cpt.estimate_detail_id and
cpt.action_type IN ('RMA','RMA_THIRD_PARTY') and
abs(ced.quantity_required) = p_rma_quantity
and rownum < 2;
update csd_repairs
set quantity = (p_rma_quantity + p_new_rma_qty)
where repair_line_id = p_repair_line_id
and quantity = p_rma_quantity;
select ced.order_line_id,
ced.order_header_id
into x_order_line_id,
x_order_header_id
from cs_estimate_details ced,csd_product_transactions cpt
where cpt.product_transaction_id = l_prod_txn_rec.product_transaction_id
and ced.estimate_detail_id = cpt.estimate_detail_id;
select serial_number_control_code
into x_sr_ro_rma_tbl(i).serial_control_flag
from mtl_system_items_b
where inventory_item_id = x_sr_ro_rma_tbl(i).inventory_item_id
and organization_id = FND_PROFILE.VALUE('ORG_ID');
if p_mode = 'UPDATE' then
l_count := 1;
for k in (select bulk_receive_id,serial_number,inventory_item_id,quantity
from csd_bulk_receive_items_b where bulk_receive_id in
(select * from table(cast(get_num_in_list(l_bulk_receive_ids)as JTF_NUMBER_TABLE)) ) )
LOOP
if (k.bulk_receive_id = x_sr_ro_rma_tbl(l_count).bulk_receive_id and
nvl(k.serial_number,1) = nvl(x_sr_ro_rma_tbl(l_count).serial_number,1) and
k.inventory_item_id = x_sr_ro_rma_tbl(l_count).inventory_item_id and
k.quantity = x_sr_ro_rma_tbl(l_count).quantity) THEN
-- in the warning or unplanned receipts UI, no data was changed.
-- no need to call the matching program for this rec.
x_sr_ro_rma_tbl.DELETE(l_count);
select
cib.customer_id,
cib.account_id,
cpt.estimate_quantity,
cpt.unit_of_measure,
cpt.inventory_item_id,
cpt.order_header_id,
cpt.order_line_id,
cpt.order_number,
cpt.serial_number,
mtl.concatenated_segments item_name,
cpt.revision,
cpt.lot_number
from
csd_product_txns_v cpt,
cs_incidents_all_b cib,
csd_repairs cr,
mtl_system_items_kfv mtl
where cpt.repair_line_id = p_repair_line_id
and cr.repair_line_id = cpt.repair_line_id
and cib.incident_id = cr.incident_id
and cpt.order_header_id = p_order_header_id
and cpt.order_line_id = p_order_line_id
and mtl.inventory_item_id = cpt.inventory_item_id
and mtl.organization_id = cs_std.get_item_valdn_orgzn_id;
Select nvl(b.ship_from_org_id,a.ship_from_org_id)
from oe_order_headers_all a,
oe_order_lines_all b
where a.header_id = b.header_id
and a.header_id = p_order_header_id;
Select prod_txn_status
from csd_product_transactions
where repair_line_id = p_repair_line_id
and action_type = 'RMA';
select cbr.bulk_receive_id,
cbr.repair_line_id,
ced.order_line_id,
ced.order_header_id,
cbr.under_receipt_flag,
cbr.quantity,
null,
null,
null,
null,
cbr.serial_number,
cbr.rcv_attribute_category,
cbr.rcv_attribute1,
cbr.rcv_attribute2,
cbr.rcv_attribute3,
cbr.rcv_attribute4,
cbr.rcv_attribute5,
cbr.rcv_attribute6,
cbr.rcv_attribute7,
cbr.rcv_attribute8,
cbr.rcv_attribute9,
cbr.rcv_attribute10,
cbr.rcv_attribute11,
cbr.rcv_attribute12,
cbr.rcv_attribute13,
cbr.rcv_attribute14,
cbr.rcv_attribute15
bulk collect into
l_bulk_autorcv_tbl
from csd_bulk_receive_items_b cbr,
csd_product_transactions cpt,
cs_estimate_details ced
where cbr.transaction_number = p_transaction_number
and cbr.repair_line_id = cpt.repair_line_id
and cpt.action_type = 'RMA'
and cpt.estimate_detail_id = ced.estimate_detail_id;
Update csd_bulk_receive_items_b
set status = 'ERRORED'
where transaction_number = p_transaction_number;
Update csd_bulk_receive_items_b
set status = 'ERRORED'
where bulk_receive_id = l_bulk_autorcv_tbl(i).bulk_receive_id;
'Calling CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE');
CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE
( p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => 0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_internal_order_flag => 'N',
p_order_header_id => null,
p_repair_line_id => l_bulk_autorcv_tbl(i).repair_line_id);
'Error in CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE: Return status '||l_return_status);
Fnd_file.put_line(fnd_file.log,'Error : CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE failed');
Update csd_bulk_receive_items_b
set status = 'PROCESSED'
where bulk_receive_id = l_bulk_autorcv_tbl(i).bulk_receive_id;
CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE
( p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => 0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_internal_order_flag => 'N',
p_order_header_id => null,
p_repair_line_id => l_bulk_autorcv_tbl(i).repair_line_id);
'Error in CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE: Return status '||l_return_status);
Fnd_file.put_line(fnd_file.log,'Error : CSD_UPDATE_PROGRAMS_PVT.RECEIPTS_UPDATE failed');
'No errors during receiving,update the line as processed: id = '||l_bulk_autorcv_tbl(i).bulk_receive_id);
Update csd_bulk_receive_items_b
set status = 'PROCESSED'
where bulk_receive_id = l_bulk_autorcv_tbl(i).bulk_receive_id;
update csd_bulk_receive_items_b
set status = 'ERRORED'
where transaction_number = p_transaction_number
and status = 'NEW';
select cpt.prod_txn_status
into l_rma_status
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
and ced.order_header_id = l_order_header_id
and ced.order_line_id = l_order_line_id;
select cpt.prod_txn_status,
cpt.product_transaction_id,
ced.estimate_detail_id,
ced.inventory_item_id,
ced.incident_id,
ced.invoice_to_org_id,
ced.ship_to_org_id,
ced.org_id,
ced.transaction_inventory_org,
ced.business_process_id,
ced.txn_billing_type_id,
null bill_to_party_id,
null bill_to_account_id,
ced.order_header_id,
ced.order_line_id,
cpt.project_id,
cpt.unit_number
into l_prod_txn_rec.prod_txn_status,
l_prod_txn_rec.product_transaction_id,
l_prod_txn_rec.estimate_detail_id,
l_prod_txn_rec.inventory_item_id,
l_prod_txn_rec.incident_id,
l_prod_txn_rec.invoice_to_org_id,
l_prod_txn_rec.ship_to_org_id,
l_prod_txn_rec.organization_id,
l_prod_txn_rec.inventory_org_id,
l_prod_txn_rec.business_process_id,
l_prod_txn_rec.txn_billing_type_id,
l_prod_txn_rec.bill_to_party_id,
l_prod_txn_rec.bill_to_account_id,
l_prod_txn_rec.order_header_id,
l_prod_txn_rec.order_line_id,
l_prod_txn_rec.project_id,
l_prod_txn_rec.unit_number
from csd_product_transactions cpt,
cs_estimate_details ced
where cpt.repair_line_id = p_repair_line_id and
cpt.action_type = 'RMA' and
cpt.action_code = 'CUST_PROD' and
cpt.prod_txn_status <> 'RECEIVED' and
cpt.estimate_detail_id = ced.estimate_detail_id and
nvl(ced.order_header_id,-1) = nvl(l_order_header_id,-1) and
nvl(ced.order_line_id,-1) = nvl(l_order_line_id,-1) and
rownum < 2;
select cpt.prod_txn_status,
cpt.product_transaction_id,
ced.estimate_detail_id,
ced.inventory_item_id,
ced.incident_id,
ced.invoice_to_org_id,
ced.ship_to_org_id,
ced.org_id,
ced.transaction_inventory_org,
ced.business_process_id,
ced.txn_billing_type_id,
null bill_to_party_id,
null bill_to_account_id,
ced.order_header_id,
ced.order_line_id,
cpt.project_id
into l_prod_txn_rec.prod_txn_status,
l_prod_txn_rec.product_transaction_id,
l_prod_txn_rec.estimate_detail_id,
l_prod_txn_rec.inventory_item_id,
l_prod_txn_rec.incident_id,
l_prod_txn_rec.invoice_to_org_id,
l_prod_txn_rec.ship_to_org_id,
l_prod_txn_rec.organization_id,
l_prod_txn_rec.inventory_org_id,
l_prod_txn_rec.business_process_id,
l_prod_txn_rec.txn_billing_type_id,
l_prod_txn_rec.bill_to_party_id,
l_prod_txn_rec.bill_to_account_id,
l_prod_txn_rec.order_header_id,
l_prod_txn_rec.order_line_id,
l_prod_txn_rec.project_id
from csd_product_transactions cpt,
cs_estimate_details ced
where cpt.repair_line_id = p_repair_line_id and
cpt.action_type = 'RMA' and
cpt.action_code = 'LOANER' and
cpt.prod_txn_status <> 'RECEIVED' and
cpt.estimate_detail_id = ced.estimate_detail_id and
nvl(ced.order_header_id,-1) = nvl(l_order_header_id,-1) and
nvl(ced.order_line_id,-1) = nvl(l_order_line_id,-1) and
rownum < 2;
Select max(ced.order_header_id)
into l_prod_txn_rec.add_to_order_id
from csd_product_transactions cpt,
cs_estimate_details ced,
oe_order_headers_all ooh,
oe_order_types_v oot,
cs_incidents_all_b sr
where cpt.estimate_detail_id = ced.estimate_detail_id
and cpt.repair_line_id = p_repair_line_id
and ced.order_header_id is not null
and ced.interface_to_oe_flag = 'Y'
and ooh.open_flag = 'Y'
and nvl(ooh.cancelled_flag,'N') = 'N'
and ooh.header_id = ced.order_header_id
and ooh.order_type_id = oot.order_type_id
and ced.incident_id = sr.incident_id
and ooh.sold_to_org_id = sr.account_id
and oot.order_category_code in ('MIXED','RETURN');
'Calling csd_process_pvt.update_product_txn to book the order');
csd_process_pvt.update_product_txn
( p_api_version => 1,
p_commit => FND_API.G_FALSE,
p_init_msg_list => FND_API.G_TRUE,
p_validation_level=> fnd_api.g_valid_level_full,
x_product_txn_rec => l_prod_txn_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
select ced.order_header_id,ced.order_line_id
into px_order_header_id,px_order_line_id
from csd_product_transactions cpt,
cs_estimate_details ced
where cpt.repair_line_id = p_repair_line_id
and ced.estimate_detail_id = cpt.estimate_detail_id
and ced.estimate_detail_id = l_prod_txn_rec.estimate_detail_id
and cpt.action_type = 'RMA';
select cpt.action_type,cpt.action_code,
cpt.picking_rule_id,cpt.project_id,
cpt.task_id,cpt.unit_number,
ced.inventory_item_id,ced.unit_of_measure_code,
ced.contract_id,ced.coverage_id,
ced.price_list_header_id,ced.txn_billing_type_id,
ced.business_process_id,ced.currency_code,
ced.ship_to_party_id,ced.ship_to_account_id,
ced.return_reason_code,ced.contract_line_id
into l_prod_txn_rec.action_type,l_prod_txn_rec.action_code,
l_prod_txn_rec.picking_rule_id,l_prod_txn_rec.project_id,
l_prod_txn_rec.task_id,l_prod_txn_rec.unit_number,
l_prod_txn_rec.inventory_item_id,l_prod_txn_rec.unit_of_measure_code,
l_prod_txn_rec.contract_id,l_prod_txn_rec.coverage_id,
l_prod_txn_rec.price_list_id,l_prod_txn_rec.txn_billing_type_id,
l_prod_txn_rec.business_process_id,l_prod_txn_rec.currency_code,
l_prod_txn_rec.ship_to_party_id,l_prod_txn_rec.ship_to_account_id,
l_prod_txn_rec.return_reason,l_prod_txn_rec.contract_line_id
from csd_product_transactions cpt,
cs_estimate_details ced
where cpt.repair_line_id = p_repair_line_id and
ced.estimate_detail_id = cpt.estimate_detail_id and
cpt.action_type = 'SHIP' and
rownum < 2;
select incident_id
into l_incident_id
from (
select incident_id
from cs_incidents_all_b
where customer_id = p_party_id
and account_id = p_account_id
and status_flag = 'O'
order by incident_id desc
)
where rownum = 1;