[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT RT.transaction_id transaction_id,
RT.transaction_type transaction_type,
RT.source_doc_quantity source_doc_quantity,
RT.organization_id organization_id,
RT.primary_quantity primary_quantity,
nvl(RT.unit_landed_cost,0) unit_landed_cost,
RT.po_unit_price
FROM rcv_transactions RT
WHERE ((c_valuation_date is not null and transaction_date <= c_valuation_date)
OR c_valuation_date is null)
START WITH transaction_id = c_transaction_id
CONNECT BY parent_transaction_id = PRIOR transaction_id;
SELECT rt.source_document_code, rt.po_header_id, rt.organization_id,
nvl(poll.lcm_flag,'N')
INTO l_source_doc_code, l_po_header_id, l_rcv_organization_id,
l_lcm_flag
FROM rcv_transactions rt,
po_line_locations_all poll
WHERE rt.transaction_id = p_rcv_transaction_id
AND rt.po_line_location_id = poll.line_location_id;
SELECT POH.org_id, HOU.set_of_books_id
INTO l_po_org_id, l_po_sob_id
FROM po_headers_all POH, hr_operating_units HOU
WHERE POH.org_id = HOU.organization_id
AND POH.po_header_id = l_po_header_id;
SELECT operating_unit, set_of_books_id
INTO l_rcv_org_id, l_rcv_sob_id
FROM cst_organization_definitions cod
WHERE organization_id = l_rcv_organization_id;
SELECT transaction_id
INTO l_rcv_transaction_id
FROM (
SELECT RT.transaction_id transaction_id,
RT.parent_transaction_id parent_transaction_id,
RT.transaction_type
FROM rcv_transactions RT
START WITH transaction_id = p_rcv_transaction_id
CONNECT BY transaction_id = PRIOR parent_transaction_id)
WHERE ((transaction_type = 'RECEIVE' and parent_transaction_id=-1)
OR transaction_type = 'MATCH');
SELECT PARENT.transaction_type
INTO l_parent_txn_type
FROM rcv_transactions RT, rcv_transactions PARENT
WHERE RT.transaction_id = rec_txn.transaction_id
AND PARENT.transaction_id = RT.parent_transaction_id;
SELECT count(*)
INTO l_rae_count
FROM rcv_accounting_events RAE
WHERE RAE.rcv_transaction_id = rec_txn.transaction_id
AND RAE.organization_id = rec_txn.organization_id
AND RAE.event_type_id IN (1,2,3,4,5,6)
AND RAE.TRX_FLOW_HEADER_ID IS NOT NULL;
SELECT POD.destination_type_code
INTO l_destination_type_code
FROM po_distributions_all POD, rcv_transactions RT
WHERE POD.po_distribution_id = RT.po_distribution_id
AND RT.transaction_id = rec_txn.transaction_id;
SELECT (MMT.transaction_cost * rec_txn.source_doc_quantity)
INTO l_txn_price
FROM mtl_material_transactions MMT
WHERE MMT.rcv_transaction_id = rec_txn.transaction_id
AND MMT.organization_id = rec_txn.organization_id;
* Select from WT if the transaction is costed, and WCTI otherwise.
*/
BEGIN
l_stmt_num := 90; /*Bug#14584624: Get absolute value to fix the value of Return to Receiving */
SELECT (((nvl(WT.actual_resource_rate,0) *
decode(nvl(WT.primary_quantity,0),
0,decode(nvl(WT.actual_resource_rate,0),0,0,1),
abs(nvl(WT.primary_quantity,0))
)
)/rec_txn.primary_quantity)*
rec_txn.source_doc_quantity)
INTO l_txn_price
FROM wip_transactions WT
WHERE WT.rcv_transaction_id = rec_txn.transaction_id
AND WT.organization_id = rec_txn.organization_id;
SELECT (((nvl(WCTI.actual_resource_rate,0) *
decode(nvl(WCTI.primary_quantity,0),
0,decode(nvl(WCTI.actual_resource_rate,0),0,0,1),
abs(nvl(WCTI.primary_quantity,0))
)
)/rec_txn.primary_quantity)*
rec_txn.source_doc_quantity)
INTO l_txn_price
FROM wip_cost_txn_interface WCTI
WHERE WCTI.rcv_transaction_id = rec_txn.transaction_id
AND WCTI.organization_id = rec_txn.organization_id;
select nvl(sum((RAE.unit_price-RAE.prior_unit_price) * nvl(RAE.currency_conversion_rate,1)*
RAE.source_doc_quantity/RAE.primary_quantity * RAE.source_doc_quantity),0)
into l_retro_rae_price
from RCV_ACCOUNTING_EVENTS RAE
WHERE RAE.rcv_transaction_id = rec_txn.transaction_id
AND RAE.organization_id = rec_txn.organization_id
AND RAE.event_type_id IN (7,8)
AND ((p_valuation_date is not null
and RAE.transaction_date <= p_valuation_date)
OR p_valuation_date is null );
SELECT count(*),sum(decode(RAE.primary_quantity, 0, 0,
(RAE.unit_price * nvl(RAE.currency_conversion_rate,1)*
RAE.source_doc_quantity/RAE.primary_quantity * RAE.source_doc_quantity)
)
)
INTO l_rae_count,l_txn_price
FROM rcv_accounting_events RAE
WHERE RAE.rcv_transaction_id = rec_txn.transaction_id
AND RAE.organization_id = rec_txn.organization_id
AND RAE.event_type_id IN (1,2,3,4,5,6)
AND ((p_valuation_date is not null
and RAE.transaction_date <= p_valuation_date)
OR p_valuation_date is null );
Select count(*),sum((MMT.transaction_cost * rec_txn.source_doc_quantity))
into l_mmt_count,l_txn_price
from mtl_material_transactions MMT
where MMT.rcv_transaction_id = rec_txn.transaction_id
AND MMT.organization_id = rec_txn.organization_id;
SELECT count(*),
sum ( ( ( nvl(WT.actual_resource_rate,0) *
decode(nvl(WT.primary_quantity,0),
0,decode(nvl(WT.actual_resource_rate,0),0,0,1),
abs(nvl(WT.primary_quantity,0))
)
)/rec_txn.primary_quantity
)*rec_txn.source_doc_quantity
)
INTO l_WT_COUNT,
l_txn_price
FROM wip_transactions WT
WHERE WT.rcv_transaction_id = rec_txn.transaction_id
AND WT.organization_id = rec_txn.organization_id;
SELECT count(*),
sum ( ( (nvl(WCTI.actual_resource_rate,0) *
decode(nvl(WCTI.primary_quantity,0),
0,decode(nvl(WCTI.actual_resource_rate,0),0,0,1),
abs(nvl(WCTI.primary_quantity,0))
)
)/rec_txn.primary_quantity
)*rec_txn.source_doc_quantity
)
INTO l_wcti_count,
l_txn_price
FROM wip_cost_txn_interface WCTI
WHERE WCTI.rcv_transaction_id = rec_txn.transaction_id
AND WCTI.organization_id = rec_txn.organization_id;
SELECT DECODE (PO_DISTRIBUTION_ID, NULL, 0, 1)
INTO l_dist_flag
FROM RCV_TRANSACTIONS
WHERE TRANSACTION_ID = rec_txn.transaction_id;
/* Bug 6266340: Modified the Select clause to check for 0 primary quantity */
SELECT decode(RT.primary_quantity, 0, 0,
((nvl(RT.po_unit_price,PLL.price_override) * RT.source_doc_quantity +
PO_TAX_SV.GET_TAX('PO', POD.PO_DISTRIBUTION_ID) *
RT.SOURCE_DOC_QUANTITY/POD.QUANTITY_ORDERED) *
decode (nvl(PLL.match_option,'P'),
'R',NVL(RT.currency_conversion_rate,1),
'P',NVL(NVL(POD.rate,POH.rate),1)) *
(RT.source_doc_quantity/RT.primary_quantity)))
INTO l_txn_price
FROM rcv_transactions RT,
po_distributions_all POD,
po_line_locations_all PLL,
po_headers_all POH
WHERE RT.transaction_id = rec_txn.transaction_id
AND POD.po_distribution_id = RT.po_distribution_id
AND PLL.line_location_id = RT.po_line_location_id
AND POH.po_header_id = RT.po_header_id;
SELECT
NVL(SUM(PO_TAX_SV.get_tax('PO', POD.PO_DISTRIBUTION_ID)), 0)
INTO
l_tax
from
po_distributions_all pod,
po_line_locations_all pol,
rcv_transactions rt
where
rt.transaction_id = rec_txn.transaction_id
and rt.po_line_location_id = pol.line_location_id
and pod.line_location_id = pol.line_location_id;
/* Bug 6266340: Modified the Select clause to check for 0 primary quantity */
SELECT decode(RT.primary_quantity, 0, 0,
((nvl(RT.po_unit_price,PLL.price_override) * RT.source_doc_quantity +
RT.SOURCE_DOC_QUANTITY/PLL.QUANTITY * l_tax) *
decode (nvl(PLL.match_option,'P'),
'R',NVL(RT.currency_conversion_rate, 1),
'P',NVL(POH.rate, 1)) *
(RT.source_doc_quantity/RT.primary_quantity)))
INTO l_txn_price
FROM rcv_transactions RT,
po_line_locations_all PLL,
po_headers_all POH
WHERE RT.transaction_id = rec_txn.transaction_id
AND PLL.line_location_id = RT.po_line_location_id
AND POH.po_header_id = RT.po_header_id;
/* Bug 6266340: Modified the Select clause to check for 0 primary quantity */
SELECT sum(decode(RAE.primary_quantity, 0, 0,
(RAE.unit_price * nvl(RAE.currency_conversion_rate,1)*
RAE.source_doc_quantity/RAE.primary_quantity * RAE.source_doc_quantity)))
INTO l_txn_price
FROM rcv_accounting_events RAE
WHERE RAE.rcv_transaction_id = rec_txn.transaction_id
AND RAE.organization_id = rec_txn.organization_id
AND RAE.event_type_id IN (1,2,3,4,5,6);
SELECT nvl(sum(decode(rae.event_type_id,
15,rae.primary_quantity,
-1*rae.primary_quantity)*
(rae.unit_price-rae.prior_unit_price)),0)
INTO l_rae_price
FROM rcv_accounting_events rae
WHERE rae.event_type_id IN (15,16,17)
AND rae.rcv_transaction_id = l_rcv_transaction_id
AND((p_valuation_date is not null
and rae.transaction_date <= p_valuation_date)
OR p_valuation_date is null);
SELECT PARENT.transaction_type
INTO l_parent_txn_type
FROM rcv_transactions RT,
rcv_transactions PARENT
WHERE RT.transaction_id = rec_txn.transaction_id
AND PARENT.transaction_id = RT.parent_transaction_id;
DELETE_FAILED EXCEPTION;
DELETE FROM RCV_ACCOUNTING_EVENTS
WHERE RCV_TRANSACTION_ID = p_purge_in_rec.entity_ids(l_index);
DELETE FROM RCV_RECEIVING_SUB_LEDGER
WHERE RCV_TRANSACTION_ID = p_purge_in_rec.entity_ids(l_index);
RAISE DELETE_FAILED;
WHEN DELETE_FAILED THEN
ROLLBACK TO Purge;
select nvl(accrue_on_receipt_flag,'N'),
destination_type_code
into l_accrueOnRcptFlg,
l_destTypeCode
from po_distributions_all
where po_distribution_id = p_po_distribution_id;
select sum(nvl(mta.base_transaction_value, 0))
into l_encReversalAmt
from mtl_material_transactions mmt,
mtl_transaction_accounts mta,
rcv_transactions rt
where rt.po_distribution_id = p_po_distribution_id
and fnd_date.date_to_canonical(rt.transaction_date)
between nvl(p_start_txn_date,fnd_date.date_to_canonical(rt.transaction_date))
and nvl(p_end_txn_date,fnd_date.date_to_canonical(sysdate))
and mmt.rcv_transaction_id = rt.transaction_id
and mta.transaction_id = mmt.transaction_id
and mta.accounting_line_type = 15
and NVL(mta.gl_batch_id, 0) <> -1;
select sum(nvl(rrs.accounted_dr,0)-nvl(rrs.accounted_cr,0))
into l_encReversalAmt
from rcv_receiving_sub_ledger rrs,
rcv_transactions rt
where rt.po_distribution_id = p_po_distribution_id
and fnd_date.date_to_canonical(rt.transaction_date)
between nvl(p_start_txn_date,fnd_date.date_to_canonical(rt.transaction_date))
and nvl(p_end_txn_date,fnd_date.date_to_canonical(sysdate))
and rrs.rcv_transaction_id = rt.transaction_id
and rrs.actual_flag = 'E';