[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT type
INTO l_rule_type
FROM ra_rules
WHERE rule_id = p_line_rec.accounting_rule_id(p_index);
SELECT BILL_SEQUENCE_ID
INTO l_bill_seq_id
FROM BOM_BILL_OF_MATERIALS
WHERE ORGANIZATION_ID = l_org_id
AND ASSEMBLY_ITEM_ID = p_line_rec.inventory_item_id(p_index)
AND ALTERNATE_BOM_DESIGNATOR IS NULL;
Select ORGANIZATION_CODE
Into l_org_code
From ORG_ORGANIZATION_DEFINITIONS
Where ORGANIZATION_ID = l_org_id;
SELECT INVENTORY_ITEM_SEGMENT_1
, INVENTORY_ITEM_SEGMENT_2
, INVENTORY_ITEM_SEGMENT_3
, INVENTORY_ITEM_SEGMENT_4
, INVENTORY_ITEM_SEGMENT_5
, INVENTORY_ITEM_SEGMENT_6
, INVENTORY_ITEM_SEGMENT_7
, INVENTORY_ITEM_SEGMENT_8
, INVENTORY_ITEM_SEGMENT_9
, INVENTORY_ITEM_SEGMENT_10
, INVENTORY_ITEM_SEGMENT_11
, INVENTORY_ITEM_SEGMENT_12
, INVENTORY_ITEM_SEGMENT_13
, INVENTORY_ITEM_SEGMENT_14
, INVENTORY_ITEM_SEGMENT_15
, INVENTORY_ITEM_SEGMENT_16
, INVENTORY_ITEM_SEGMENT_17
, INVENTORY_ITEM_SEGMENT_18
, INVENTORY_ITEM_SEGMENT_19
, INVENTORY_ITEM_SEGMENT_20
, INVENTORY_ITEM
INTO l_segment_array(1)
, l_segment_array(2)
, l_segment_array(3)
, l_segment_array(4)
, l_segment_array(5)
, l_segment_array(6)
, l_segment_array(7)
, l_segment_array(8)
, l_segment_array(9)
, l_segment_array(10)
, l_segment_array(11)
, l_segment_array(12)
, l_segment_array(13)
, l_segment_array(14)
, l_segment_array(15)
, l_segment_array(16)
, l_segment_array(17)
, l_segment_array(18)
, l_segment_array(19)
, l_segment_array(20)
, l_inventory_item
FROM OE_LINES_IFACE_ALL
WHERE order_source_id = p_line_rec.order_source_id(p_index)
AND orig_sys_document_ref = p_line_rec.orig_sys_document_ref(p_index)
AND orig_sys_line_ref = p_line_rec.orig_sys_line_ref(p_index)
AND org_id = p_line_rec.org_id(p_index)
AND (nvl(orig_sys_shipment_ref,fnd_api.g_miss_char)
= nvl(p_line_rec.orig_sys_shipment_ref(p_index),fnd_api.g_miss_char)
OR -- added to fix bug 5394064
p_line_rec.orig_sys_shipment_ref(p_index) = 'OE_ORDER_LINES_ALL'||p_line_rec.line_id(p_index)||'.'||'1')
-- Bug 2764130 : there should be only one row for this doc/line ref
-- combination. If there are multiple rows, it will be errored out in
-- the duplicate check in procedure Entity.
AND rownum = 1;
SELECT inventory_item_id
INTO l_inventory_item_id_int
FROM mtl_system_items_vl
WHERE concatenated_segments = l_inventory_item
AND customer_order_enabled_flag = 'Y'
AND bom_item_type in (1,2,4)
AND organization_id = OE_BULK_ORDER_PVT.G_ITEM_ORG;
SELECT inventory_item_id
INTO l_inventory_item_id_gen
FROM mtl_cross_references
WHERE cross_reference_type = p_line_rec.item_identifier_type(p_index)
AND (organization_id = OE_BULK_ORDER_PVT.G_ITEM_ORG
OR organization_id IS NULL)
AND cross_reference = p_line_rec.ordered_item(p_index)
AND (inventory_item_id = l_inventory_item_id_int
OR l_inventory_item_id_int IS NULL);
SELECT 'VALID'
INTO l_dummy
FROM MTL_SUBINVENTORIES_TRK_VAL_V
WHERE organization_id = p_ship_from_org_id
AND secondary_inventory_name = p_subinventory;
select 'Y'
into l_dummy
from mtl_subinventories_trk_val_v sub
where sub.organization_id = p_ship_from_org_id
and sub.secondary_inventory_name = p_subinventory
and (fnd_profile.value('INV:EXPENSE_TO_ASSET_TRANSFER') = 1
OR
(fnd_profile.value('INV:EXPENSE_TO_ASSET_TRANSFER') <> 1
and nvl(p_order_source_id, -1) <> 10
)
OR
(fnd_profile.value('INV:EXPENSE_TO_ASSET_TRANSFER') <> 1
and nvl(p_order_source_id, -1) = 10
and 'N' = (select inventory_asset_flag
from mtl_system_items
where inventory_item_id = p_inventory_item_id
and organization_id = p_ship_from_org_id
)
)
OR
(fnd_profile.value('INV:EXPENSE_TO_ASSET_TRANSFER') <> 1
and nvl(p_order_source_id, -1) = 10
and 'Y' = (select inventory_asset_flag
from mtl_system_items
where inventory_item_id = p_inventory_item_id
and organization_id = p_ship_from_org_id
)
and sub.asset_inventory = 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 msi.internal_order_enabled_flag = 'Y'
AND sysdate <= nvl( org.disable_date, sysdate)
AND org.organization_id= p_ship_from_org_id
AND rownum=1;
SELECT null
INTO l_dummy
FROM mtl_system_items msi,
hr_all_organization_units org
WHERE msi.inventory_item_id = p_inventory_item_id
AND org.organization_id= msi.organization_id
AND msi.internal_order_enabled_flag = 'Y'
AND sysdate <= nvl( org.date_to, sysdate)
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 sysdate <= nvl( org.disable_date, sysdate)
AND org.organization_id= p_ship_from_org_id
AND rownum=1;
SELECT null
INTO l_dummy
FROM mtl_system_items msi,
hr_all_organization_units org
WHERE msi.inventory_item_id = p_inventory_item_id
AND org.organization_id= msi.organization_id
AND sysdate <= nvl( org.date_to, sysdate)
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 msi.customer_order_enabled_flag = 'Y'
AND sysdate <= nvl( org.disable_date, sysdate)
AND org.organization_id= p_ship_from_org_id
AND rownum=1;
SELECT null
INTO l_dummy
FROM mtl_system_items msi,
hr_all_organization_units org
WHERE msi.inventory_item_id = p_inventory_item_id
AND org.organization_id= msi.organization_id
AND msi.customer_order_enabled_flag = 'Y'
AND sysdate <= nvl( org.date_to, sysdate)
AND org.organization_id= p_ship_from_org_id
AND rownum=1;
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_Bulk_Order_PVT.G_ITEM_ORG
AND citems.customer_item_id = p_ordered_item_id
AND citems.customer_id = p_sold_to_org_id
AND citems.inactive_flag = 'N'
AND cxref.inactive_flag = 'N';
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_Bulk_Order_PVT.G_ITEM_ORG
AND citems.customer_item_id = p_ordered_item_id
AND citems.customer_id = p_sold_to_org_id;
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_Bulk_Order_PVT.G_ITEM_ORG
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
AND (items.organization_id = sitems.organization_id
OR items.org_independent_flag = 'Y'); /*Bug 1636532*/
select unit_selling_price, payment_term_id
into l_unit_selling_price, l_payment_term_id
from oe_order_lines_all
where line_id = p_line_rec.line_id(p_line_index);
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 count(*)
INTO l_count
FROM wsh_carrier_services wsh,
wsh_org_carrier_services wsh_org
WHERE wsh_org.organization_id = p_line_rec.ship_from_org_id(l_index)
AND wsh.carrier_service_id = wsh_org.carrier_service_id
AND wsh.ship_method_code = p_line_rec.shipping_method_code(l_index)
AND wsh_org.enabled_flag = 'Y';
SELECT count(*)
INTO l_count
FROM wsh_carrier_ship_methods
WHERE ship_method_code = p_line_rec.shipping_method_code(l_index)
AND organization_id = p_line_rec.ship_from_org_id(l_index);
OE_BULK_MSG_PUB.Update_Msg_Context(p_attribute_code => 'SHIPPING_METHOD');
OE_BULK_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
SELECT primary_uom_code
INTO l_uom
FROM mtl_system_items
WHERE inventory_item_id=p_line_rec.inventory_item_id(l_index)
AND organization_id=nvl(p_line_rec.ship_from_org_id(l_index),
OE_Bulk_Order_PVT.G_ITEM_ORG);
SELECT type
INTO l_rule_type
FROM ra_rules
WHERE rule_id = p_line_rec.accounting_rule_id(l_index);
SELECT 'VALID'
INTO l_dummy
FROM ZX_EXEMPTIONS_V
WHERE EXEMPT_CERTIFICATE_NUMBER = p_line_rec.tax_exempt_number(l_index)
AND EXEMPT_REASON_CODE = p_line_rec.tax_exempt_reason_code(l_index)
AND nvl(site_use_id,nvl(p_line_rec.ship_to_org_id(l_index),p_line_rec.invoice_to_org_id(l_index))) =
nvl(p_line_rec.ship_to_org_id(l_index),p_line_rec.invoice_to_org_id(l_index))
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 EXEMPTION_STATUS_CODE = 'PRIMARY'
AND TRUNC(NVL(p_line_rec.request_date(l_index),sysdate))
BETWEEN TRUNC(EFFECTIVE_FROM)
AND TRUNC(NVL(EFFECTIVE_TO,NVL(p_line_rec.request_date(l_index),sysdate)))
AND ROWNUM = 1;
SELECT 'VALID'
INTO l_dummy
FROM TAX_EXEMPTIONS_V
WHERE TAX_EXEMPT_NUMBER = p_line_rec.tax_exempt_number(l_index)
AND TAX_EXEMPT_REASON_CODE=p_line_rec.tax_exempt_reason_code(l_index)
AND SHIP_TO_SITE_USE_ID = nvl(p_line_rec.ship_to_org_id(l_index),
p_line_rec.invoice_to_org_id(l_index))
AND BILL_TO_CUSTOMER_ID = p_line_rec.sold_to_org_id(l_index)
AND STATUS_CODE = 'PRIMARY'
AND TAX_CODE = p_line_rec.tax_code(l_index)
AND TRUNC(NVL(p_line_rec.request_date(l_index),sysdate))
BETWEEN TRUNC(START_DATE)
AND TRUNC(NVL(END_DATE,NVL(p_line_rec.request_date(l_index),sysdate)))
AND ROWNUM = 1;
SELECT 'VALID'
INTO l_dummy
FROM AR_VAT_TAX V
WHERE V.TAX_CODE = p_line_rec.tax_code(l_index)
AND V.SET_OF_BOOKS_ID = l_sob_id
AND NVL(V.ENABLED_FLAG,'Y')='Y'
AND NVL(V.TAX_CLASS,'O')='O'
AND NVL(V.DISPLAYED_FLAG,'Y')='Y'
AND TRUNC(p_line_rec.tax_date(l_index))
BETWEEN TRUNC(V.START_DATE) AND
TRUNC(NVL(V.END_DATE, p_line_rec.tax_date(l_index)))
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(l_index)
AND V.SET_OF_BOOKS_ID = P.SET_OF_BOOKS_ID
AND NVL(V.ENABLED_FLAG,'Y')='Y'
AND NVL(V.TAX_CLASS,'O')='O'
AND NVL(V.DISPLAYED_FLAG,'Y')='Y'
AND TRUNC(p_line_rec.tax_date(l_index))
BETWEEN TRUNC(V.START_DATE) AND
TRUNC(NVL(V.END_DATE, p_line_rec.tax_date(l_index)))
AND ROWNUM = 1;
SELECT 'VALID'
INTO l_dummy
FROM ZX_OUTPUT_CLASSIFICATIONS_V
WHERE LOOKUP_CODE = p_line_rec.tax_code(l_index)
-- AND LOOKUP_TYPE = 'ZX_OUTPUT_CLASSIFICATIONS'
AND ENABLED_FLAG ='Y'
AND ORG_ID IN (p_line_rec.org_id(l_index), -99)
AND TRUNC(p_line_rec.tax_date(l_index)) BETWEEN
TRUNC(START_DATE_ACTIVE) AND
TRUNC(NVL(END_DATE_ACTIVE, p_line_rec.tax_date(l_index)))
AND ROWNUM = 1;
SELECT alot.prefqc_grade
FROM op_alot_prm alot, ic_item_mst item, op_cust_mst cust
WHERE item.item_id = p_opm_item_id
and alot.cust_id = cust.cust_id
and item.alloc_class = alot.alloc_class
and alot.delete_mark = 0
and cust.of_ship_to_site_use_id = p_line_rec.ship_to_org_id(p_index);
SELECT alot.prefqc_grade
FROM op_alot_prm alot, ic_item_mst item
WHERE item.item_id = p_opm_item_id
and alot.cust_id IS NULL
and item.alloc_class = alot.alloc_class
and alot.delete_mark = 0;
SELECT tax_calculation_flag
INTO G_INV_TAX_CALC_FLAG
FROM ra_cust_trx_types
WHERE cust_trx_type_id = G_INV_TRN_TYPE_ID;