[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT tax_calculation_flag
INTO l_calculate_tax_flag
FROM RA_CUST_TRX_TYPES
WHERE CUST_TRX_TYPE_ID = l_line_type_rec.cust_trx_type_id;
SELECT order_date_type_code
INTO l_order_date_type_code
FROM oe_order_headers
WHERE header_id = p_header_id;
SELECT 'EXISTS'
FROM oe_workflow_assignments a
WHERE a.line_type_id = p_line_rec.line_type_id
AND nvl(a.item_type_code,nvl(l_new_wf_item_type,'-99')) = nvl(l_new_wf_item_type,'-99')
AND a.process_name = lprocessname
AND a.order_type_id = lorder_type_id
ORDER BY a.item_type_code ;
SELECT order_type_id
FROM oe_order_headers
WHERE header_id = p_line_rec.header_id ;
SELECT 'EXISTS'
FROM oe_workflow_assignments a
WHERE a.line_type_id = p_line_rec.line_type_id
AND a.item_type_code = l_new_wf_item_type
AND a.order_type_id = lorder_type_id ;
SELECT null
INTO l_dummy
FROM mtl_system_items msi,
org_organization_definitions org
WHERE msi.inventory_item_id = p_inventory_item_id
AND org.organization_id= msi.organization_id
AND org.organization_id= p_ship_from_org_id
AND org.set_of_books_id= ( SELECT fsp.set_of_books_id
FROM financials_system_parameters fsp)
AND ROWNUM=1 ;
SELECT null
INTO l_dummy
FROM mtl_system_items msi,
org_organization_definitions org
WHERE msi.inventory_item_id = p_inventory_item_id
AND org.organization_id= msi.organization_id
AND org.organization_id= p_ship_from_org_id
AND rownum=1;
SELECT null
INTO l_dummy
FROM mtl_system_items msi,
org_organization_definitions org
WHERE msi.inventory_item_id = p_inventory_item_id
AND org.organization_id= msi.organization_id
AND org.organization_id= p_ship_from_org_id
AND rownum=1;
SELECT 'VALID'
INTO l_dummy
FROM mtl_task_v
WHERE project_id = p_project_id
AND task_id = p_task_id;
SELECT NVL(PROJECT_CONTROL_LEVEL,0)
INTO l_project_control_level
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = p_ship_from_org_id;
SELECT 'valid'
INTO l_dummy
FROM mtl_system_items_vl
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID');
SELECT 'valid'
INTO l_dummy
FROM mtl_customer_items citems
,mtl_customer_item_xrefs cxref
,mtl_system_items_vl sitems
WHERE citems.customer_item_id = cxref.customer_item_id
AND cxref.inventory_item_id = sitems.inventory_item_id
AND sitems.inventory_item_id = p_inventory_item_id
AND sitems.organization_id =
OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID')
AND citems.customer_item_id = p_ordered_item_id
AND citems.customer_id = p_sold_to_org_id
AND rownum =1;
SELECT 'valid'
INTO l_dummy
FROM mtl_cross_reference_types types
, mtl_cross_references items
, mtl_system_items_vl sitems
WHERE types.cross_reference_type = items.cross_reference_type
AND items.inventory_item_id = sitems.inventory_item_id
AND sitems.organization_id =
OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID')
AND sitems.inventory_item_id = p_inventory_item_id
AND items.cross_reference_type = p_item_identifier_type
AND items.cross_reference = p_ordered_item;
SELECT inventory_item_id
INTO l_ref_inventory_item_id
FROM oe_order_lines
WHERE line_id = p_reference_line_id;
SELECT nvl(fulfilled_quantity, 0)
, nvl(shippable_flag, 'N')
, invoice_interface_status_code
, nvl(shipped_quantity, 0)
INTO l_ref_fulfilled_quantity
, l_ref_shippable_flag
, l_ref_inv_iface_status
, l_ref_shipped_quantity
FROM oe_order_lines
WHERE line_id = p_reference_line_id;
SELECT nvl(returnable_flag,'Y')
INTO l_returnable_flag
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
and organization_id = nvl(p_ship_from_org_id,
oe_sys_parameters.value_wnps('MASTER_ORGANIZATION_ID'));
SELECT nvl(booked_flag,'N')
INTO l_booked_flag
FROM oe_order_lines
WHERE line_id = p_reference_line_id
and line_category_code = 'ORDER';
SELECT 'VALID'
FROM oe_ship_to_orgs_v
WHERE site_use_id = p_ship_to_org_id
AND status = 'A'
AND customer_id = p_sold_to_org_id
AND ROWNUM = 1
UNION ALL
SELECT /*MOAC_SQL_NO_CHANGE*/ 'VALID'
FROM oe_ship_to_orgs_v osto
WHERE site_use_id = p_ship_to_org_id
AND status = 'A'
AND EXISTS
(
SELECT 1 FROM
HZ_CUST_ACCT_RELATE hcar
WHERE hcar.cust_account_id = osto.customer_id AND
hcar.RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
AND hcar.ship_to_flag = 'Y'
)
AND ROWNUM = 1;
Select 'VALID'
Into l_dummy
From oe_ship_to_orgs_v
Where customer_id = p_sold_to_org_id
AND site_use_id = p_ship_to_org_id
AND status = 'A';
/*Select /*MOAC_SQL_NO_CHANGE 'VALID'
Into l_dummy
From oe_ship_to_orgs_v
WHERE site_use_id = p_ship_to_org_id
AND status = 'A' AND
customer_id in (
Select p_sold_to_org_id from dual
union
select CUST_ACCOUNT_ID from
HZ_CUST_ACCT_RELATE
where RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
/* added the following condition to fix the bug 2002486
and ship_to_flag = 'Y')
and rownum = 1;*/
SELECT 'VALID'
INTO l_dummy
FROM oe_ship_to_orgs_v
WHERE site_use_id = p_ship_to_org_id
AND ROWNUM = 1;
SELECT 'VALID'
INTO l_dummy
FROM oe_deliver_to_orgs_v
WHERE customer_id = p_sold_to_org_id
AND site_use_id = p_deliver_to_org_id
AND status = 'A';
SELECT /* MOAC_SQL_CHANGE */ 'VALID'
Into l_dummy
FROM HZ_CUST_SITE_USES_ALL SITE,
HZ_CUST_ACCT_SITES ACCT_SITE
WHERE SITE.SITE_USE_ID = p_deliver_to_org_id
AND SITE.SITE_USE_CODE ='DELIVER_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.CUST_ACCOUNT_ID in (
SELECT p_sold_to_org_id FROM DUAL
UNION
SELECT CUST_ACCOUNT_ID FROM
HZ_CUST_ACCT_RELATE_ALL R WHERE
R.ORG_ID = ACCT_SITE.ORG_ID
AND R.RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
and R.ship_to_flag = 'Y')
AND ROWNUM = 1;
SELECT 'VALID'
INTO l_dummy
FROM HZ_CUST_SITE_USES SITE
WHERE SITE.SITE_USE_ID =p_deliver_to_org_id;
oe_debug_pub.add('Cannot insert external line to set',2);
SELECT purchasing_enabled_flag
INTO l_purchasing_enabled_flag
FROM mtl_system_items msi,
org_organization_definitions org
WHERE msi.inventory_item_id = p_line_rec.inventory_item_id
AND org.organization_id= msi.organization_id
AND sysdate <= nvl( org.disable_date, sysdate)
AND org.organization_id = nvl(p_line_rec.ship_from_org_id,
OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID'))
AND org.set_of_books_id= ( SELECT fsp.set_of_books_id
FROM financials_system_parameters fsp);
SELECT tracking_quantity_ind,
secondary_uom_code,
secondary_default_ind
FROM mtl_system_items
WHERE organization_id = discrete_org_id
AND inventory_item_id = discrete_item_id;
SELECT cust_acct.cust_account_id,
cust_Acct.party_id,
acct_site.party_site_id,
site_use.org_id
FROM
HZ_CUST_SITE_USES_ALL site_use,
HZ_CUST_ACCT_SITES_ALL acct_site,
HZ_CUST_ACCOUNTS_ALL cust_Acct
WHERE site_use.site_use_id = p_site_org_id
AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
and acct_site.cust_account_id = cust_acct.cust_account_id;
Select /*MOAC_SQL_NO_CHANGE*/ 'VALID' a
From oe_invoice_to_orgs_v
WHERE site_use_id = p_line_rec.invoice_to_org_id
AND status = 'A'
AND customer_id = p_line_rec.sold_to_org_id
and rownum =1
UNION ALL
SELECT /*MOAC_SQL_NO_CHANGE*/ 'VALID' a
FROM oe_invoice_to_orgs_v oito
WHERE oito.site_use_id = p_line_rec.invoice_to_org_id
AND oito.status = 'A' AND
EXISTS
(
select 1 from HZ_CUST_ACCT_RELATE hcar
where hcar.CUST_ACCOUNT_ID = oito.customer_id
and hcar.RELATED_CUST_ACCOUNT_ID = p_line_rec.sold_to_org_id
/* added the following condition to fix the bug 2002486 */
and hcar.bill_to_flag = 'Y'
)
and rownum = 1 ;
SELECT 'VALID'
INTO l_dummy
FROM MTL_SUBINVENTORIES_TRK_VAL_V
WHERE organization_id = p_line_rec.ship_from_org_id
AND secondary_inventory_name = p_line_rec.subinventory;
,p_input_quantity => p_line_rec.auto_selected_quantity
,p_uom_code => p_line_rec.order_quantity_uom
,x_return_status => l_return_status
);
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'TASK_ID');
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
/*IF p_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
IF (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_OPTION OR
p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CLASS)
--AND p_line_rec.line_id <> p_line_rec.ato_line_id
THEN
FND_MESSAGE.SET_NAME('ONT', 'OE_VAL_PROJ_UPD');
SELECT /* MOAC_SQL_CHANGE */ 'VALID'
INTO l_dummy
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_CUST_SITE_USES_ALL SHIP
WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_line_rec.ship_to_contact_id
AND ACCT_ROLE.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID = SHIP.CUST_ACCT_SITE_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND SHIP.SITE_USE_ID = p_line_rec.ship_to_org_id
AND SHIP.STATUS = 'A'
AND ROWNUM = 1;
SELECT /* MOAC_SQL_CHANGE */ 'VALID'
INTO l_dummy
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_CUST_SITE_USES_ALL DELI
WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_line_rec.deliver_to_contact_id
AND ACCT_ROLE.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ACCT_SITE.CUST_ACCT_SITE_ID = DELI.CUST_ACCT_SITE_ID
AND DELI.SITE_USE_ID = p_line_rec.deliver_to_org_id
AND DELI.STATUS = 'A'
AND ROWNUM = 1;
Select 'VALID'
Into l_dummy
From oe_invoice_to_orgs_v
Where customer_id = p_line_rec.sold_to_org_id
And site_use_id = p_line_rec.invoice_to_org_id;
/*Select MOAC_SQL_NO_CHANGE 'VALID'
Into l_dummy
From oe_invoice_to_orgs_v
WHERE site_use_id = p_line_rec.invoice_to_org_id
AND status = 'A' AND
customer_id in (
Select p_line_rec.sold_to_org_id from dual
union
select CUST_ACCOUNT_ID from
HZ_CUST_ACCT_RELATE
where RELATED_CUST_ACCOUNT_ID = p_line_rec.sold_to_org_id
/* added the following condition to fix the bug 2002486
and bill_to_flag = 'Y')
and rownum = 1;*/
SELECT 'VALID'
INTO l_dummy
From oe_invoice_to_orgs_v
WHERE site_use_id = p_line_rec.invoice_to_org_id
AND ROWNUM = 1;
SELECT /* MOAC_SQL_CHANGE */ 'VALID'
INTO l_dummy
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_CUST_SITE_USES_ALL INV
WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_line_rec.invoice_to_contact_id
AND ACCT_ROLE.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ACCT_SITE.CUST_ACCT_SITE_ID = INV.CUST_ACCT_SITE_ID
AND INV.SITE_USE_ID = p_line_rec.invoice_to_org_id
AND ROWNUM = 1;
SELECT 'VALID'
INTO l_dummy
FROM AR_VAT_TAX V,
AR_SYSTEM_PARAMETERS P
WHERE V.TAX_CODE = p_line_rec.tax_code
AND V.SET_OF_BOOKS_ID = P.SET_OF_BOOKS_ID
AND NVL(V.TAX_CLASS,'O')='O'
AND NVL(V.DISPLAYED_FLAG,'Y')='Y'
AND ROWNUM = 1;
SELECT 'VALID'
INTO l_dummy
FROM AR_VAT_TAX V
WHERE V.TAX_CODE = p_line_rec.tax_code
AND V.SET_OF_BOOKS_ID = l_sob_id
AND NVL(V.TAX_CLASS,'O')='O'
AND NVL(V.DISPLAYED_FLAG,'Y')='Y'
AND ROWNUM = 1;
SELECT 'VALID'
INTO l_dummy
FROM ZX_OUTPUT_CLASSIFICATIONS_V lk
WHERE lk.lookup_code = p_line_rec.tax_code
--AND lk.lookup_type = 'ZX_OUTPUT_CLASSIFICATIONS'
AND lk.ENABLED_FLAG ='Y'
AND lk.ORG_ID IN (p_line_rec.org_id, -99)
AND TRUNC(p_line_rec.tax_date) BETWEEN TRUNC(lk.START_DATE_ACTIVE)
AND TRUNC(NVL(lk.END_DATE_ACTIVE, p_line_rec.tax_date))
AND ROWNUM = 1;
/* SELECT 'VALID'
INTO l_dummy
FROM OE_TAX_EXEMPTIONS_QP_V
WHERE TAX_EXEMPT_NUMBER = p_line_rec.tax_exempt_number
AND TAX_EXEMPT_REASON_CODE=p_line_rec.tax_exempt_reason_code
AND SHIP_TO_ORG_ID = nvl(p_line_rec.ship_to_org_id,
p_line_rec.invoice_to_org_id)
AND BILL_TO_CUSTOMER_ID = p_line_rec.sold_to_org_id
AND TAX_CODE = p_line_rec.tax_code
AND STATUS_CODE = 'PRIMARY'
AND ROWNUM = 1;*/
SELECT 'VALID'
INTO l_dummy
FROM ZX_EXEMPTIONS_V
WHERE EXEMPT_CERTIFICATE_NUMBER = p_line_rec.tax_exempt_number
AND EXEMPT_REASON_CODE = p_line_rec.tax_exempt_reason_code
AND nvl(site_use_id,nvl(p_line_rec.ship_to_org_id,
p_line_rec.invoice_to_org_id))
= nvl(p_line_rec.ship_to_org_id,
p_line_rec.invoice_to_org_id)
AND nvl(cust_account_id, l_bill_to_cust_acct_id) = l_bill_to_cust_acct_id
AND nvl(PARTY_SITE_ID,nvl(l_ship_to_party_site_id, l_bill_to_party_site_id))=
nvl(l_ship_to_party_site_id, l_bill_to_party_site_id)
AND org_id = l_org_id
AND party_id = l_bill_to_party_id
-- AND nvl(LEGAL_ENTITY_ID,-99) IN (nvl(l_legal_entity_id, legal_entity_id), -99)
AND EXEMPTION_STATUS_CODE = 'PRIMARY'
-- **** Check with OM team whether the join based on date is required or not ****
-- AND TRUNC(NVL(p_line_rec.request_date,sysdate))
-- BETWEEN TRUNC(EFFECTIVE_FROM)
-- AND TRUNC(NVL(EFFECTIVE_TO,NVL(p_line_rec.request_date,sysdate)))
AND ROWNUM = 1;
SELECT primary_uom_code
INTO l_uom
FROM mtl_system_items
WHERE inventory_item_id = p_line_rec.inventory_item_id
AND organization_id = nvl(p_line_rec.ship_from_org_id,
OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID'));
SELECT count(*)
INTO l_uom_count
FROM mtl_item_uoms_view
WHERE inventory_item_id = p_line_rec.inventory_item_id
AND uom_code = p_line_rec.order_quantity_uom
AND organization_id = nvl(p_line_rec.ship_from_org_id,
OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID'));
select list_type_code
into l_list_type_code
from qp_list_headers_vl
where list_header_id = p_line_rec.price_list_id;
SELECT name ,sold_to_org_id , price_list_id
INTO l_agreement_name,l_sold_to_org,l_price_list_id
FROM oe_agreements_v
WHERE agreement_id = p_line_rec.agreement_id;
SELECT name
INTO l_price_list_name
FROM qp_List_headers_vl
WHERE list_header_id = p_line_rec.price_list_id;
SELECT name
INTO l_price_list_name
FROM QP_List_headers_vl
WHERE list_header_id = l_price_list_id;
SELECT 'VALID'
INTO l_dummy
FROM dual
WHERE exists(
select 'x' from
HZ_CUST_ACCT_RELATE
where RELATED_CUST_ACCOUNT_ID = p_line_rec.sold_to_org_id
AND CUST_ACCOUNT_ID = l_sold_to_org
);
-- modified by lkxu: to select from qp_list_headers_vl instead
-- of from qp_price_lists_v to select only PRL type list headers.
SELECT name
INTO l_price_list_name
FROM qp_list_headers_vl
WHERE list_header_id = p_line_rec.price_list_id
AND list_type_code = 'PRL';
/*IF p_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
IF (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_OPTION) OR
(p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CLASS) OR
(p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_KIT) OR
(p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_SERVICE AND
p_line_rec.service_reference_line_id IS NOT NULL AND
p_line_rec.service_reference_line_id <> FND_API.G_MISS_NUM)
THEN
IF (NOT OE_GLOBALS.EQUAL(p_line_rec.line_number,null)) THEN
l_return_status := FND_API.G_RET_STS_ERROR;
p_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE AND
p_line_rec.ordered_quantity = 0
THEN
oe_debug_pub.add
('qty of a configuration related line 0'|| p_line_rec.item_type_code, 1);
SELECT OE_GLOBALS.G_ITEM_KIT
INTO l_item_type_code
FROM mtl_system_items
WHERE organization_id
= OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID')
AND inventory_item_id = p_line_rec.inventory_item_id
AND pick_components_flag = 'Y';