[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 hcas.party_site_id
INTO l_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
INTO l_party_site_use_id
FROM Hz_Party_Sites hps,
Hz_Party_Site_uses hpsu,
Hz_Locations hl
WHERE hps.party_site_id = l_party_site_id
AND hpsu.site_use_type = p_site_use_type
AND hps.status = 'A'
AND hps.location_id = hl.location_id
AND hps.party_site_id = hpsu.party_site_id;
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;
SELECT /*+ CHOOSE */
oeh.order_number rma_number,
oeh.header_id rma_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,
oeh.cust_po_number purchase_order_num,
oeh.transactional_curr_code,
oeh.SOLD_TO_CONTACT_ID CONTACT_ID ,
oel.line_id ,
oel.line_number rma_line_number,
oel.inventory_item_id,
oel.item_revision,
oel.price_list_id,
oel.shipped_quantity,
oel.line_type_id,
oel.order_quantity_uom,
rcv.organization_id,
rcv.quantity received_quantity,
rcv.subinventory received_subinventory,
rcv.transaction_date received_date,
rcv.transaction_id,
rcv.last_updated_by who_col,
rcv.subinventory,
hp.party_type,
hp.party_id,
haou.name org_name
FROM RCV_TRANSACTIONS rcv,
OE_ORDER_LINES_ALL oel,
OE_ORDER_HEADERS_ALL oeh,
hz_parties hp,
hz_cust_accounts hca,
hr_all_organization_units haou
WHERE rcv.oe_order_line_id = oel.line_id
AND rcv.transaction_type = 'DELIVER'
AND rcv.source_document_code = 'RMA'
AND oel.header_id = oeh.header_id
AND oel.sold_to_org_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND rcv.organization_id = haou.organization_id
AND rcv.organization_id = p_inv_org_id
AND rcv.subinventory = p_sub_inv
AND not exists
( select 'X' from csd_repairs cra
where cra.original_source_line_id = oel.line_id
and cra.original_source_header_id = oel.header_id)
AND not exists
( select 'x' from cs_estimate_details ced
where ced.order_header_id = oel.header_id
and ced.order_line_id = oel.line_id);
SELECT serial_num
from rcv_serial_transactions
where transaction_id = p_txn_id;
Select repair_type_id,
repair_mode
into l_repair_type_id,
l_repair_mode
from csd_repair_types_vl
where repair_type_ref = 'SR'
and seeded_flag = 'Y';
Select 'X'
into l_dummy
from mtl_parameters
where organization_id = p_inventory_org_id;
Select 'X'
into l_dummy
from mtl_secondary_inventories
where organization_id = p_inventory_org_id
and secondary_inventory_name = p_subinventory_name ;
Select incident_id
into l_incident_id
from csd_repairs cra
where cra.original_source_header_id = c1.rma_header_id
-- and cra.original_source_line_id = c1.line_id
and rownum = 1;
Select 'X'
into l_dummy
from cs_incidents_all_b cia,
cs_incident_statuses cis
where cia.incident_status_id = cis.incident_status_id
and cis.status_code = 'OPEN'
and incident_id = l_incident_id;
l_service_request_rec.last_update_program_code := 'CSD_REPAIR_ORDER_FORM';
Select 'x'
into l_dummy
from mtl_system_items
where inventory_item_id = c1.inventory_item_id
and organization_id = c1.organization_id
and serial_number_control_code <> 1;
l_rep_line_tbl.delete;
Select
instance_id
into l_instance_id
from csi_item_instances
where serial_number = C2.serial_num
and inventory_item_id = C1.inventory_item_id
and owner_party_id = C1.party_id;
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 => l_repair_line_id,
p_EVENT_CODE => 'RR',
p_EVENT_DATE => C1.received_date,
p_QUANTITY => l_rep_line_tbl(i).QUANTITY,
p_PARAMN1 => C1.transaction_id,
p_PARAMN2 => C1.rma_line_number,
p_PARAMN3 => C1.organization_id,
p_PARAMN4 => NULL,
p_PARAMN5 => NULL,
p_PARAMN6 => C1.rma_header_id,
p_PARAMN7 => null,
p_PARAMN8 => null,
p_PARAMN9 => null,
p_PARAMN10 => null,
p_PARAMC1 => C1.subinventory,
p_PARAMC2 => C1.rma_number,
p_PARAMC3 => C1.org_name,
p_PARAMC4 => null,
p_PARAMC5 => null,
p_PARAMC6 => null,
p_PARAMC7 => null,
p_PARAMC8 => null,
p_PARAMC9 => null,
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 => null,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data );
SELECT acct_role.party_id party_id, C.contact_point_id,
C.contact_point_type,C.primary_flag
FROM hz_contact_points C,
HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
and C.owner_table_name(+)='HZ_PARTIES'
and C.contact_point_type(+)='PHONE'
and C.status(+)='A'
and C.primary_flag(+)='Y'
and acct_role.party_id=C.owner_table_id(+) ;