The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select *
from MRP_ERO_RELEASE
where batch_id =p_batch_id
order by transaction_id ;
select requisition_number into lv_req_number from csp_repair_po_headers
where REQUISITION_HEADER_ID=X_requisition_header_id;
select requisition_number into lv_req_number from csp_repair_po_headers
where REQUISITION_HEADER_ID=X_requisition_header_id;
DELETE FROM MRP_ERO_RELEASE WHERE BATCH_ID=p_batch_id ;
Procedure update_iro (errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_repair_line_id IN NUMBER,
p_quantity IN NUMBER:= NULL,
p_promise_date IN DATE:= NULL)
IS
Cursor c1(p_repair_line_id number) is
select * 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 => l_Init_Msg_List,
P_Commit => l_Commit,
p_validation_level => l_validation_level,
P_REPAIR_LINE_ID => P_REPAIR_LINE_ID,
P_REPLN_Rec => Q_REPLN_Rec,
X_Return_Status => X_Return_Status,
X_Msg_Count => p_Msg_Count,
X_Msg_Data => X_Msg_Data);
END update_iro;
SELECT USER_ID
INTO l_user_id
FROM FND_USER
WHERE USER_NAME = p_user_name;
Select Transaction_id,Quantity,in_req_quantity,out_req_quantity,In_req_transaction_id,
Out_req_transaction_id,Batch_id,Inventory_item_id
Uom_code,Organization_id,Promise_date
from MRP_IRO_RELEASE
where batch_id =p_batch_id
and load_type = IRO_LOAD
AND In_req_transaction_id is not NULL;
Select distinct Transaction_id, Quantity,
Out_req_transaction_id, Batch_id, Inventory_item_id,
Uom_code, Organization_id, Promise_date ,deliver_to_location_id,
src_organization_id,LOAD_TYPE
from MRP_IRO_RELEASE
where batch_id =p_batch_id
and load_type =IRO_LOAD
and in_req_transaction_id is null ;
Delete from MRP_IRO_RELEASE mir
where batch_id =p_batch_id
And in_req_transaction_id is null
AND load_type = IRO_LOAD
And exists ( select 1 from mrp_iro_release mir1
Where mir1.batch_id =p_batch_id
And mir1.in_req_transaction_id is not null
And mir1.transaction_id = mir.transaction_id
);
Update mrp_org_transfer_release
set part_condition ='G'
where transaction_id= l_ro_release.Out_req_transaction_id;
select cust_account_id,account_number
into l_acct_id,l_acct_number
from hz_cust_accounts_all
where party_id = l_customer_id
and customer_type ='I'
and rownum < 2;
select party_number
into l_customer_NAME
from hz_parties --ra_customers
where party_id = l_customer_id
and rownum < 2;
select userenv('LANG')
Into v_lang from dual;
SELECT r.party_id
INTO l_customer_contact_id
FROM Hz_Parties sub,
Hz_Relationships r,
Hz_Parties obj
WHERE r.object_id = l_customer_id
--AND r.party_id = p_customer_contact_id
AND sub.status = 'A'
AND r.status = 'A'
AND obj.status = 'A'
AND r.subject_id = sub.party_id
AND r.object_id = obj.party_id
AND sub.party_type = 'PERSON'
AND obj.party_type = 'ORGANIZATION'
AND NVL(r.start_date, SYSDATE-1) <= SYSDATE
AND NVL(r.end_date, SYSDATE+1) > SYSDATE
AND ROWNUM < 2
ORDER BY r.LAST_UPDATE_DATE Desc;
select repair_type_id
Into P_REPAIR_TYPE
from CSD_REPAIR_TYPES_B
where repair_mode = 'WIP'
and internal_order_flag = 'Y'
and rownum < 2;
select lgr.currency_code
into l_currency_code
from
hr_organization_information hoi,
gl_ledgers lgr
where
hoi.organization_id = l_ro_release.Organization_id
AND (hoi.org_information_context || '') = 'Accounting Information'
AND to_number(decode(RTRIM(TRANSLATE(hoi.org_information1, '0123456789', ' ')), NULL, hoi.org_information1, -99999)) = lgr.ledger_id
AND lgr.object_type_code = 'L'
AND nvl(lgr.complete_flag, 'Y') = 'Y';
SELECT wip_job_schedule_interface_s.NEXTVAL
INTO l_group_id FROM dual;
Select To_Char(WIP_JOB_NUMBER_S.NEXTVAL)
into l_wip_job_name from dual;
LOG_MESSAGE('Inserting into wip_job_schedule_interface');
INSERT INTO wip_job_schedule_interface
(
last_update_date,
last_updated_by,
creation_date,
created_by,
load_type,
process_phase,
process_status,
group_id,
source_code,
source_line_id,
job_name,
organization_id,
status_type,
first_unit_start_date,
last_unit_completion_date,
start_quantity,
net_quantity,
class_code,
primary_item_id,
interface_id
)
VALUES
(
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
4,
2,
1,
l_group_id,
'MSC',
X_REPAIR_LINE_ID,
l_wip_job_name,
l_ro_release.Organization_id,
3,
SYSDATE,
l_ro_release.Promise_date,
l_ro_release.Quantity,
l_ro_release.Quantity,
'Rework',
l_ro_release.Inventory_item_id,
l_group_id
);
LOG_MESSAGE('Inserted into wip_job_schedule_interface group id '||to_char(l_group_id));
p_last_updated_by => l_user_id,
p_last_update_date => SYSDATE,
p_last_update_login => l_user_id,
p_repair_line_id => X_REPAIR_LINE_ID, --10960,
p_wip_entity_id => x_wipEntityID, --760063,
p_group_id => l_group_id, --required
p_organization_id => l_ro_release.Organization_id,
p_quantity => l_ro_release.Quantity,
p_INVENTORY_ITEM_ID => l_ro_release.Inventory_item_id,
p_ITEM_REVISION => null,
p_OBJECT_VERSION_NUMBER => 1,
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_quantity_completed => NULL,
p_job_name => l_wip_job_name,
p_source_type_code => 'MANUAL',
p_source_id1 => NULL,
p_ro_service_code_id => NULL,
x_return_status => l_return_status,
x_msg_count => X_Msg_Count,
x_msg_data => X_Msg_Data);
Update mrp_org_transfer_release
set part_condition ='B'
where transaction_id= l_ro_release.In_req_transaction_id;
Update mrp_org_transfer_release
set part_condition ='G'
where transaction_id= l_ro_release.out_req_transaction_id;
select repair_line_id into lv_repair_line_id
from csd_product_transactions
where action_type='MOVE_IN'
and REQ_HEADER_ID= l_req_header_id1;
/* call to update iro */
update_iro(errbuf=>errbuf,
retcode=>retcode,
p_repair_line_id=>lv_repair_line_id,
p_quantity=>l_ro_release.quantity,
p_promise_date=> l_ro_release.Promise_date) ;
DELETE FROM MRP_IRO_RELEASE WHERE BATCH_ID=p_batch_id ;
DELETE FROm MRP_ORG_TRANSFER_RELEASE WHERE BATCH_ID=p_batch_id;