[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
period_set_name
, period_type
, conversion_option
, conversion_type
INTO
l_period_set_name
, l_period_type
, l_conversion_option
, l_conversion_type
FROM
mtl_stat_type_usages
WHERE
legal_entity_id = p_movement_transaction.entity_org_id
AND stat_type = p_movement_transaction.stat_type;
SELECT end_date
INTO
l_last_dayofperiod
FROM
GL_PERIODS
WHERE period_name = p_movement_transaction.period_name
AND period_set_name = l_period_set_name
AND period_type = l_period_type;
SELECT
SUM(ratl.extended_amount)
FROM
RA_CUSTOMER_TRX_ALL rat
, RA_CUSTOMER_TRX_LINES_ALL ratl
, RA_CUST_TRX_TYPES_ALL ratt
, oe_order_lines_all oola
WHERE rat.customer_trx_id = ratl.customer_trx_id
AND rat.cust_trx_type_id = ratt.cust_trx_type_id
AND rat.org_id = ratt.org_id
AND ratt.type NOT IN ('CM','DM')
AND NOT EXISTS
(SELECT null
FROM oe_price_adjustments
WHERE (line_id = oola.line_id
OR (line_id IS NULL AND modifier_level_code = 'ORDER'))
AND TO_CHAR(price_adjustment_id)= NVL(ratl.interface_line_attribute11, '-9999')
AND header_id = oola.header_id)
AND ratl.line_type = 'LINE'
AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
AND ratl.sales_order = p_order_number
AND ratl.interface_line_attribute6 = to_char(oola.line_id)
AND oola.top_model_line_id = p_order_line_id
AND ratl.interface_line_context <> 'INTERCOMPANY';
SELECT
SUM(ratl.extended_amount)
FROM
RA_CUSTOMER_TRX_ALL rat
, RA_CUSTOMER_TRX_LINES_ALL ratl
, RA_CUST_TRX_TYPES_ALL ratt
, oe_order_lines_all oola
WHERE rat.customer_trx_id = ratl.customer_trx_id
AND rat.cust_trx_type_id = ratt.cust_trx_type_id
AND rat.org_id = ratt.org_id
AND ((ratt.type IN ('CM','DM'))
OR (NVL(ratl.interface_line_attribute11, '-9999') --Fix bug 2423946
IN (SELECT TO_CHAR(price_adjustment_id)
FROM oe_price_adjustments
WHERE header_id = oola.header_id
AND (line_id = oola.line_id
OR (line_id IS NULL AND modifier_level_code = 'ORDER')) )))
AND ratl.line_type = 'LINE'
AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
AND ratl.quantity_credited IS NULL
AND ratl.sales_order = p_order_number
AND ratl.interface_line_attribute6 = to_char(oola.line_id)
AND oola.top_model_line_id = p_order_line_id
AND ratl.interface_line_context <> 'INTERCOMPANY';
SELECT
SUM(ratl.extended_amount)
FROM
RA_CUSTOMER_TRX_ALL rat
, RA_CUSTOMER_TRX_LINES_ALL ratl
, RA_CUST_TRX_TYPES_ALL ratt --add in for fixing bug 2447381
, oe_order_lines_all oola
WHERE rat.customer_trx_id = ratl.customer_trx_id
AND rat.cust_trx_type_id = ratt.cust_trx_type_id
AND rat.org_id = ratt.org_id
AND ratt.type NOT IN ('CM','DM')
AND NOT EXISTS
(SELECT null
FROM oe_price_adjustments
WHERE (line_id = oola.line_id
OR (line_id IS NULL AND modifier_level_code = 'ORDER'))
AND TO_CHAR(price_adjustment_id)= NVL(ratl.interface_line_attribute11, '-9999')
AND header_id = oola.header_id)
AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
AND ratl.line_type = 'LINE' --yawang
AND ratl.sales_order = p_order_number
AND ratl.interface_line_attribute6 = to_char(oola.line_id)
AND oola.top_model_line_id = p_order_line_id
AND rat.complete_flag = 'Y'
AND ratl.interface_line_context <> 'INTERCOMPANY';
SELECT
SUM(ratl.extended_amount)
FROM
RA_CUSTOMER_TRX_ALL rat
, RA_CUSTOMER_TRX_LINES_ALL ratl
, RA_CUST_TRX_TYPES_ALL ratt
, oe_order_lines_all oola
WHERE rat.customer_trx_id = ratl.customer_trx_id
AND rat.cust_trx_type_id = ratt.cust_trx_type_id
AND rat.org_id = ratt.org_id
AND ((ratt.type IN ('CM','DM'))
OR (NVL(ratl.interface_line_attribute11, '-9999') --Fix bug 2423946
IN (SELECT TO_CHAR(price_adjustment_id)
FROM oe_price_adjustments
WHERE header_id = oola.header_id
AND (line_id = oola.line_id
OR (line_id IS NULL AND modifier_level_code = 'ORDER')) )))
AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
AND ratl.line_type = 'LINE' --yawang
AND ratl.sales_order = p_order_number
AND ratl.interface_line_attribute6 =
to_char(oola.line_id)
AND oola.top_model_line_id = p_order_line_id
AND rat.complete_flag = 'Y'
AND ratl.interface_line_context <> 'INTERCOMPANY';
SELECT
SUM(ratl.extended_amount)
FROM
RA_CUSTOMER_TRX_ALL rat
, RA_CUSTOMER_TRX_LINES_ALL ratl
, RA_CUST_TRX_TYPES_ALL ratt
, oe_order_lines_all oola
WHERE rat.customer_trx_id = ratl.customer_trx_id
AND rat.cust_trx_type_id = ratt.cust_trx_type_id
AND rat.org_id = ratt.org_id
AND ratt.type IN ('CM','DM')
AND ratl.line_type = 'LINE' --yawang
AND ratl.quantity_credited IS NOT NULL
AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
AND NVL(ratl.org_id,0) = NVL(p_org_id,0)
AND ratl.interface_line_attribute6 = to_char(oola.line_id)
AND ratl.sales_order = p_order_number
AND oola.top_model_line_id = p_order_line_id
AND ratl.interface_line_context <> 'INTERCOMPANY';
SELECT
SUM(ratl.extended_amount)
FROM
RA_CUSTOMER_TRX_LINES_ALL ratl
, oe_order_lines_all oola
WHERE ratl.line_type = 'LINE'
AND ratl.interface_line_attribute6 = to_char(oola.line_id)
AND ratl.sales_order = p_order_number
AND oola.top_model_line_id = p_order_line_id
AND ratl.interface_line_context = 'INTERCOMPANY';
SELECT
SUM(NVL(aila.amount,rctla.extended_amount))
FROM
AP_INVOICES_ALL aia
, AP_INVOICE_LINES_ALL aila
, RA_CUSTOMER_TRX_LINES_ALL rctla
, ra_customer_trx_all rcta
, oe_order_lines_all oola
WHERE aia.invoice_id = aila.invoice_id
AND aia.cancelled_date IS NULL
AND aila.line_type_lookup_code = 'ITEM'
AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
AND aila.reference_1 = TO_CHAR(rctla.customer_trx_line_id)
AND rctla.customer_trx_id = rcta.customer_trx_id
AND (rcta.trx_number||'-'||rcta.org_id = aia.invoice_num
OR rcta.trx_number = aia.invoice_num)
AND rctla.sales_order = p_order_number
AND rctla.interface_line_attribute6 = to_char(oola.line_id)
AND oola.top_model_line_id = p_order_line_id
AND nvl(aila.discarded_flag, 'N') <> 'Y'
AND NOT EXISTS (SELECT 'Unreleased holds exist'
FROM ap_holds_all aha
WHERE aha.invoice_id = aia.invoice_id
AND aha.release_lookup_code is null)
AND EXISTS (SELECT 'Invoice is approved'
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_id = aia.invoice_id
AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
SELECT
SUM(NVL(ADJUSTED_AMOUNT_PER_PQTY,0))
FROM
oe_price_adjustments
WHERE line_id = p_line_id
AND modifier_level_code = 'LINE'
AND list_line_type_code = 'FREIGHT_CHARGE';
SELECT DISTINCT
TO_NUMBER(interface_line_attribute6)
FROM
ra_customer_trx_lines_all
WHERE sales_order = to_char(p_movement_transaction.order_number)
AND line_type = 'LINE'
AND customer_trx_id IN
(SELECT customer_trx_id
FROM ra_customer_trx_lines_all
WHERE interface_line_attribute6 = to_char(p_movement_transaction.order_line_id)
AND sales_order = to_char(p_movement_transaction.order_number))
ORDER BY TO_NUMBER(interface_line_attribute6);
SELECT
item_type_code
, top_model_line_id
FROM
oe_order_lines_all
WHERE line_id = p_movement_transaction.order_line_id;
SELECT
SUM(NVL(ratl.quantity_invoiced,0))
FROM
RA_CUSTOMER_TRX_ALL rat
, RA_CUSTOMER_TRX_LINES_ALL ratl
, RA_CUST_TRX_TYPES_ALL ratt
WHERE rat.customer_trx_id = ratl.customer_trx_id
AND rat.cust_trx_type_id = ratt.cust_trx_type_id
AND rat.org_id = ratt.org_id
AND ratt.type NOT IN ('CM','DM')
AND NVL(ratl.interface_line_attribute11, '-9999') --Fix bug 2423946
NOT IN (SELECT TO_CHAR(price_adjustment_id)
FROM oe_price_adjustments
WHERE header_id = p_movement_transaction.order_header_id
AND (line_id = l_line_id
OR (line_id IS NULL AND modifier_level_code = 'ORDER')))
AND ratl.line_type = 'LINE'
AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
AND ratl.sales_order = to_char(p_movement_transaction.order_number)
AND ratl.interface_line_attribute6 = l_line_id
AND ratl.interface_line_context <> 'INTERCOMPANY';
SELECT
count(transaction_id)
FROM
rcv_transactions rt
, oe_order_lines_all oola
WHERE rt.oe_order_line_id = oola.line_id
AND oola.reference_line_id = to_char(p_movement_transaction.order_line_id)
AND rt.mvt_stat_status = 'PROCESSED'
AND rt.source_document_code = 'RMA';
SELECT
SUM(NVL(ratl.extended_amount,0))
, SUM(NVL(ratl.quantity_credited,0))
FROM
RA_CUSTOMER_TRX_ALL rat
, RA_CUSTOMER_TRX_LINES_ALL ratl
, RA_CUST_TRX_TYPES_ALL ratt
, OE_ORDER_LINES_ALL oola
, RCV_TRANSACTIONS rt
WHERE rat.customer_trx_id = ratl.customer_trx_id
AND rat.cust_trx_type_id = ratt.cust_trx_type_id
AND rat.org_id = ratt.org_id
AND ratt.type IN ('CM','DM')
AND ratl.line_type = 'LINE' --yawang
AND ratl.quantity_credited IS NOT NULL
AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
-- AND NVL(ratl.org_id,0) = NVL(p_movement_transaction.org_id,0)
AND ratl.interface_line_attribute6 = to_char(oola.line_id)
AND oola.reference_line_id = to_char(p_movement_transaction.order_line_id)
AND rt.oe_order_line_id = oola.line_id
AND rt.mvt_stat_status = 'PROCESSED'
AND rt.transaction_type = 'DELIVER'
AND ratl.interface_line_context <> 'INTERCOMPANY'
AND oola.line_id NOT IN (SELECT order_line_id
FROM mtl_movement_statistics
WHERE entity_org_id = p_movement_transaction.entity_org_id
AND zone_code = p_movement_transaction.zone_code
AND usage_type = p_movement_transaction.usage_type
AND stat_type = p_movement_transaction.stat_type
AND document_source_type = 'RMA'
AND rcv_transaction_id = rt.transaction_id);
SELECT
COUNT(transaction_id)
FROM
rcv_transactions
WHERE transaction_type = 'RETURN TO VENDOR'
AND parent_transaction_id = p_movement_transaction.rcv_transaction_id
AND mvt_stat_status = 'PROCESSED';
SELECT
SUM(quantity)
FROM
rcv_transactions
WHERE po_header_id = p_movement_transaction.po_header_id
AND transaction_type = 'RETURN TO VENDOR';
SELECT
SUM(quantity)
FROM
rcv_transactions rt
WHERE po_header_id = p_movement_transaction.po_header_id
AND transaction_type = 'RETURN TO VENDOR'
AND mvt_stat_status = 'PROCESSED'
AND transaction_id NOT IN (SELECT rcv_transaction_id
FROM mtl_movement_statistics
WHERE document_source_type = 'RTV'
AND po_header_id = rt.po_header_id
AND entity_org_id = p_movement_transaction.entity_org_id
AND zone_code = p_movement_transaction.zone_code
AND usage_type = p_movement_transaction.usage_type
AND stat_type = p_movement_transaction.stat_type);
SELECT
SUM(aila.amount)
, SUM(aila.quantity_invoiced)
FROM
AP_INVOICES_ALL aia,
AP_INVOICE_LINES_ALL aila
WHERE aia.invoice_id = aila.invoice_id
AND aia.invoice_type_lookup_code in ('CREDIT','DEBIT')
AND aila.rcv_transaction_id = l_parent_transaction_id
AND aila.line_type_lookup_code = 'ITEM'
AND NVL(aila.quantity_invoiced,0) < 0
AND aia.cancelled_date IS NULL
AND nvl(aila.discarded_flag, 'N') <> 'Y'
AND NOT EXISTS (SELECT 'Unreleased holds exist'
FROM ap_holds_all aha
WHERE aha.invoice_id = aia.invoice_id
AND aha.release_lookup_code is null)
AND EXISTS (SELECT 'Invoice is approved'
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_id = aia.invoice_id
AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
SELECT
SUM(aila.amount) --yawang
, SUM(aila.quantity_invoiced)
FROM
PO_HEADERS_ALL pha
, PO_DISTRIBUTIONS_ALL pda
, AP_INVOICES_ALL aia
, AP_INVOICE_LINES_ALL aila
WHERE pha.po_header_id = pda.po_header_id
AND aia.invoice_id = aila.invoice_id
AND pda.po_header_id = aila.po_header_id /*Bug 7446311 Joined to imporve performance*/
AND pda.po_distribution_id = aila.po_distribution_id
AND aia.invoice_type_lookup_code in ('CREDIT','DEBIT')
AND aia.cancelled_date IS NULL
AND aila.line_type_lookup_code = 'ITEM' --yawang, limit for good cost only
AND NVL(aila.quantity_invoiced,0) < 0
AND pha.po_header_id = p_movement_transaction.po_header_id
AND pda.line_location_id = p_movement_transaction.po_line_location_id
AND nvl(aila.discarded_flag, 'N') <> 'Y'
AND NOT EXISTS (SELECT 'Unreleased holds exist'
FROM ap_holds_all aha
WHERE aha.invoice_id = aia.invoice_id
AND aha.release_lookup_code is null)
AND EXISTS (SELECT 'Invoice is approved'
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_id = aia.invoice_id
AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
SELECT end_date
INTO
l_last_dayofperiod
FROM
GL_PERIODS
WHERE period_name = x_movement_transaction.period_name
AND period_set_name = p_stat_typ_transaction.period_set_name
AND period_type = p_stat_typ_transaction.period_type;
SELECT
uom_code
FROM
ra_customer_trx_lines_all
WHERE customer_trx_line_id = l_movement_transaction.customer_trx_line_id;
SELECT
UOM.UOM_CODE
FROM
AP_INVOICE_DISTRIBUTIONS_ALL AID,MTL_UNITS_OF_MEASURE UOM
WHERE AID.invoice_id = l_movement_transaction.invoice_id
AND AID.distribution_line_number = l_movement_transaction.distribution_line_number
AND AID.MATCHED_UOM_LOOKUP_CODE=UOM.UNIT_OF_MEASURE;
SELECT
NVL(invoice_currency_code, -1)
INTO
l_invoice_currency
FROM
AP_INVOICES_ALL
WHERE
invoice_id = l_movement_transaction.invoice_id ;
SELECT
SUM(extended_amount)
FROM
ra_customer_trx_lines_all
WHERE (sales_order = to_char(p_movement_transaction.order_number)
OR sales_order IS NULL) --for manual invoice
AND line_type = 'FREIGHT'
AND customer_trx_id IN
(SELECT customer_trx_id
FROM ra_customer_trx_lines_all
WHERE interface_line_attribute6 = to_char(p_movement_transaction.order_line_id)
AND sales_order = to_char(p_movement_transaction.order_number));
SELECT
rat.trx_date
, rat.batch_id
, NVL(rat.exchange_rate,1)
, rat.exchange_rate_type
, rat.exchange_date
, NVL(rat.invoice_currency_code,l_movement_transaction.currency_code)
FROM
RA_CUSTOMER_TRX_ALL rat
WHERE rat.customer_trx_id = x_movement_transaction.invoice_id;
SELECT
MAX(ratl.customer_trx_line_id)
, MAX(rat.customer_trx_id)
, SUM(ratl.extended_amount)
, SUM(NVL(ratl.quantity_invoiced,l_movement_transaction.transaction_quantity))
FROM
RA_CUSTOMER_TRX_ALL rat
, RA_CUSTOMER_TRX_LINES_ALL ratl
, RA_CUST_TRX_TYPES_ALL ratt
WHERE rat.customer_trx_id = ratl.customer_trx_id
AND rat.cust_trx_type_id = ratt.cust_trx_type_id
AND rat.org_id = ratt.org_id
AND ratt.type NOT IN ('CM','DM')
AND NVL(ratl.interface_line_attribute11, '-9999') --Fix bug 2423946
NOT IN (SELECT TO_CHAR(price_adjustment_id)
FROM oe_price_adjustments
WHERE header_id = l_movement_transaction.order_header_id
AND (line_id = l_movement_transaction.order_line_id
OR (line_id IS NULL AND modifier_level_code = 'ORDER')))
AND ratl.line_type = 'LINE'
AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
AND ratl.sales_order = to_char(l_movement_transaction.order_number)
AND ratl.interface_line_attribute6 =
to_char(l_movement_transaction.order_line_id)
AND ratl.interface_line_context <> 'INTERCOMPANY';
SELECT
MAX(ratl.customer_trx_line_id)
, MAX(rat.customer_trx_id)
, SUM(ratl.extended_amount)
, SUM(NVL(ratl.quantity_credited,l_movement_transaction.transaction_quantity))
FROM
RA_CUSTOMER_TRX_ALL rat
, RA_CUSTOMER_TRX_LINES_ALL ratl
, RA_CUST_TRX_TYPES_ALL ratt
WHERE rat.customer_trx_id = ratl.customer_trx_id
AND rat.cust_trx_type_id = ratt.cust_trx_type_id
AND rat.org_id = ratt.org_id
AND ratt.type IN ('CM','DM')
AND ratl.line_type = 'LINE' --yawang
AND ratl.quantity_credited IS NOT NULL
AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
--AND ratl.sales_order = to_char(l_movement_transaction.order_number)
AND ratl.interface_line_attribute1 = to_char(l_movement_transaction.order_number)
AND NVL(ratl.org_id,0) = NVL(l_movement_transaction.org_id,0)
AND ratl.interface_line_attribute6 =
to_char(l_movement_transaction.order_line_id)
AND ratl.interface_line_context <> 'INTERCOMPANY';
SELECT
SUM(ratl.extended_amount)
FROM
RA_CUSTOMER_TRX_ALL rat
, RA_CUSTOMER_TRX_LINES_ALL ratl
, RA_CUST_TRX_TYPES_ALL ratt
WHERE rat.customer_trx_id = ratl.customer_trx_id
AND rat.cust_trx_type_id = ratt.cust_trx_type_id
AND rat.org_id = ratt.org_id
AND ((ratt.type IN ('CM','DM'))
OR (NVL(ratl.interface_line_attribute11, '-9999') --Fix bug 2423946
IN (SELECT TO_CHAR(price_adjustment_id)
FROM oe_price_adjustments
WHERE header_id = l_movement_transaction.order_header_id
AND (line_id = l_movement_transaction.order_line_id
OR (line_id IS NULL AND modifier_level_code = 'ORDER')) )))
AND ratl.line_type = 'LINE' --yawang
AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
AND ratl.quantity_credited IS NULL
AND ratl.sales_order = to_char(l_movement_transaction.order_number)
AND ratl.interface_line_attribute6 =
to_char(l_movement_transaction.order_line_id)
AND ratl.interface_line_context <> 'INTERCOMPANY';
SELECT
MAX(ratl.customer_trx_line_id)
, MAX(rat.customer_trx_id)
, SUM(ratl.extended_amount)
, SUM(NVL(ratl.quantity_invoiced,l_movement_transaction.transaction_quantity))
FROM
RA_CUSTOMER_TRX_ALL rat
, RA_CUSTOMER_TRX_LINES_ALL ratl
, RA_CUST_TRX_TYPES_ALL ratt --add in for fixing bug 2447381
WHERE rat.customer_trx_id = ratl.customer_trx_id
--AND ratl.quantity_invoiced = ratl.quantity_ordered
AND rat.cust_trx_type_id = ratt.cust_trx_type_id
AND rat.org_id = ratt.org_id
AND ratt.type NOT IN ('CM','DM')
AND NVL(ratl.interface_line_attribute11, '-9999') --Fix bug 2423946
NOT IN (SELECT TO_CHAR(price_adjustment_id)
FROM oe_price_adjustments
WHERE header_id = l_movement_transaction.order_header_id
AND (line_id = l_movement_transaction.order_line_id
OR (line_id IS NULL AND modifier_level_code = 'ORDER')))
AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
AND ratl.line_type = 'LINE' --yawang
AND ratl.sales_order = to_char(l_movement_transaction.order_number)
AND ratl.interface_line_attribute6 =
to_char(l_movement_transaction.order_line_id)
AND rat.complete_flag = 'Y'
AND ratl.interface_line_context <> 'INTERCOMPANY';
SELECT
SUM(ratl.extended_amount)
FROM
RA_CUSTOMER_TRX_ALL rat
, RA_CUSTOMER_TRX_LINES_ALL ratl
, RA_CUST_TRX_TYPES_ALL ratt
WHERE rat.customer_trx_id = ratl.customer_trx_id
--AND ratl.quantity_invoiced = ratl.quantity_ordered
AND rat.cust_trx_type_id = ratt.cust_trx_type_id
AND rat.org_id = ratt.org_id
AND ((ratt.type IN ('CM','DM'))
OR (NVL(ratl.interface_line_attribute11, '-9999') --Fix bug 2423946
IN (SELECT TO_CHAR(price_adjustment_id)
FROM oe_price_adjustments
WHERE header_id = l_movement_transaction.order_header_id
AND (line_id = l_movement_transaction.order_line_id
OR (line_id IS NULL AND modifier_level_code = 'ORDER')) )))
AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
AND ratl.line_type = 'LINE' --yawang
AND ratl.sales_order = to_char(l_movement_transaction.order_number)
AND ratl.interface_line_attribute6 =
to_char(l_movement_transaction.order_line_id)
AND rat.complete_flag = 'Y'
AND ratl.interface_line_context <> 'INTERCOMPANY';
SELECT
MAX(ratl.customer_trx_line_id)
, MAX(rat.customer_trx_id)
, SUM(ratl.extended_amount)
, SUM(NVL(ratl.quantity_invoiced,l_movement_transaction.transaction_quantity))
FROM
RA_CUSTOMER_TRX_ALL rat
, RA_CUSTOMER_TRX_LINES_ALL ratl
WHERE rat.customer_trx_id = ratl.customer_trx_id
AND ratl.line_type = 'LINE'
AND ratl.sales_order = to_char(l_movement_transaction.order_number)
AND ratl.interface_line_attribute6 =
to_char(l_movement_transaction.order_line_id)
AND ratl.interface_line_context = 'INTERCOMPANY';
SELECT
MAX(aia.invoice_id)
, MAX(aila.line_number)
, SUM(NVL(aila.amount, 0))
, SUM(NVL(aila.quantity_invoiced,0))
FROM
AP_INVOICES_ALL aia
, AP_INVOICE_LINES_ALL aila
, RA_CUSTOMER_TRX_LINES_ALL rctla
, ra_customer_trx_all rcta
WHERE aia.invoice_id = aila.invoice_id
AND aia.cancelled_date IS NULL
AND aila.line_type_lookup_code = 'ITEM'
AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
AND aila.reference_1 = TO_CHAR(rctla.customer_trx_line_id)
AND rctla.customer_trx_id = rcta.customer_trx_id
AND (rcta.trx_number||'-'||rcta.org_id = aia.invoice_num
OR rcta.trx_number = aia.invoice_num)
AND rctla.sales_order = to_char(l_movement_transaction.order_number)
AND rctla.interface_line_attribute6 =
to_char(l_movement_transaction.order_line_id)
AND nvl(aila.discarded_flag, 'N') <> 'Y'
AND NOT EXISTS (SELECT 'Unreleased holds exist'
FROM ap_holds_all aha
WHERE aha.invoice_id = aia.invoice_id
AND aha.release_lookup_code is null)
AND EXISTS (SELECT 'Invoice is approved'
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_id = aia.invoice_id
AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
SELECT
SUM(NVL(rctla.extended_amount,0)) InvoiceAmount
,SUM(NVL(rctla.quantity_invoiced,0)) InvoicedQuantity
FROM
AP_INVOICES_ALL aia
, RA_CUSTOMER_TRX_LINES_ALL rctla
WHERE aia.invoice_id = x_movement_transaction.invoice_id
AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
AND rctla.sales_order = to_char(l_movement_transaction.order_number)
AND rctla.interface_line_attribute6 =
to_char(l_movement_transaction.order_line_id);
SELECT
SUM(NVL(rctla.extended_amount,0)) InvoiceAmount
, SUM(NVL(rctla.quantity_credited,l_movement_transaction.transaction_quantity)) InvoicedQuantity
FROM
AP_INVOICES_ALL aia
, RA_CUSTOMER_TRX_LINES_ALL rctla
WHERE aia.invoice_id = x_movement_transaction.invoice_id
AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
AND rctla.interface_line_attribute1 = to_char(l_movement_transaction.order_number)
AND rctla.interface_line_attribute6 =
to_char(l_movement_transaction.order_line_id);
SELECT
ap.invoice_currency_code
, NVL(ap.exchange_rate,NVL(rctl.exchange_rate, 1))
, NVL(ap.exchange_rate_type,rctl.exchange_rate_type)
, NVL(ap.exchange_date,rctl.exchange_date)
, ap.batch_id
, ap.invoice_date
FROM
AP_INVOICES_ALL ap
, RA_CUSTOMER_TRX_ALL rctl
WHERE ap.invoice_id = x_movement_transaction.invoice_id
AND ap.reference_1 = rctl.customer_trx_id;
SELECT
rat.trx_date
, rat.batch_id
, NVL(rat.exchange_rate,1)
, rat.exchange_rate_type
, rat.exchange_date
, NVL(rat.invoice_currency_code,l_movement_transaction.currency_code)
FROM
RA_CUSTOMER_TRX_ALL rat
, RA_CUSTOMER_TRX_LINES_ALL ratl
WHERE rat.customer_trx_id = ratl.customer_trx_id
--AND ratl.quantity_invoiced = ratl.quantity_ordered
AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'
AND ratl.interface_line_attribute6 = to_char(l_movement_transaction.order_line_id)
AND ratl.customer_trx_line_id = x_movement_transaction.customer_trx_line_id;
SELECT
ap.invoice_currency_code
, NVL(ap.exchange_rate,1)
, ap.exchange_rate_type
, ap.exchange_date
, ap.batch_id
, ap.invoice_date
FROM
AP_INVOICES_ALL ap
WHERE ap.invoice_id = x_movement_transaction.invoice_id;
SELECT
SUM(aila.amount)
, MAX(aila.line_number)
, MAX(aia.invoice_id)
, SUM(aila.quantity_invoiced)
FROM
ap_invoices_all aia
, ap_invoice_lines_all aila
WHERE aia.invoice_id = aila.invoice_id
AND aia.invoice_type_lookup_code in ('STANDARD','MIXED')
AND aia.cancelled_date IS NULL
AND aila.line_type_lookup_code = 'ITEM'
AND aila.match_type = 'ITEM_TO_PO'
AND aila.po_line_location_id = l_movement_transaction.po_line_location_id
-- Bug 5655040. Commented as condition is modified and few more conditioned
-- added to whereclause to check hold and disregard status.
--AND NOT EXISTS (SELECT 1
-- FROM ap_invoice_distributions_all aida
-- WHERE aida.invoice_id = aia.invoice_id
-- AND aida.invoice_line_number = aila.line_number
-- AND NVL(aida.match_status_flag,'N') <> 'A');
AND NOT EXISTS (SELECT 'Unreleased holds exist'
FROM ap_holds_all aha
WHERE aha.invoice_id = aia.invoice_id
AND aha.release_lookup_code is null)
AND EXISTS (SELECT 'Invoice is approved'
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_id = aia.invoice_id
AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
SELECT
SUM(aila.amount)
FROM
ap_invoices_all aia
, ap_invoice_lines_all aila
WHERE aia.invoice_id = aila.invoice_id
AND aia.cancelled_date IS NULL
AND aila.line_type_lookup_code = 'ITEM'
AND aila.match_type = 'PRICE_CORRECTION'
AND aila.po_line_location_id = l_movement_transaction.po_line_location_id
-- Bug 5655040. Commented as condition is modified and few more conditioned
-- added to whereclause to check hold and disregard status.
--AND NOT EXISTS (SELECT 1
-- FROM ap_invoice_distributions_all aida
-- WHERE aida.invoice_id = aia.invoice_id
-- AND aida.invoice_line_number = aila.line_number
-- AND NVL(aida.match_status_flag,'N') <> 'A');
AND NOT EXISTS (SELECT 'Unreleased holds exist'
FROM ap_holds_all aha
WHERE aha.invoice_id = aia.invoice_id
AND aha.release_lookup_code is null)
AND EXISTS (SELECT 'Invoice is approved'
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_id = aia.invoice_id
AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
SELECT
SUM(aila.quantity_invoiced)
, SUM(aila.amount)
FROM
ap_invoices_all aia
, ap_invoice_lines_all aila
WHERE aia.invoice_id = aila.invoice_id
AND aia.cancelled_date IS NULL
AND aila.line_type_lookup_code = 'ITEM'
AND aila.match_type = 'QTY_CORRECTION'
AND aila.po_line_location_id = l_movement_transaction.po_line_location_id
-- Bug 5655040. Commented as condition is modified and few more conditioned
-- added to whereclause to check hold and disregard status.
--AND NOT EXISTS (SELECT 1
-- FROM ap_invoice_distributions_all aida
-- WHERE aida.invoice_id = aia.invoice_id
-- AND aida.invoice_line_number = aila.line_number
-- AND NVL(aida.match_status_flag,'N') <> 'A');
AND NOT EXISTS (SELECT 'Unreleased holds exist'
FROM ap_holds_all aha
WHERE aha.invoice_id = aia.invoice_id
AND aha.release_lookup_code is null)
AND EXISTS (SELECT 'Invoice is approved'
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_id = aia.invoice_id
AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
SELECT
SUM(aila.amount)
, MAX(aila.line_number)
, MAX(aia.invoice_id)
, SUM(aila.quantity_invoiced)
FROM
ap_invoices_all aia
, ap_invoice_lines_all aila
WHERE aia.invoice_id = aila.invoice_id
AND aia.invoice_type_lookup_code in ('CREDIT','DEBIT')
AND aia.cancelled_date IS NULL
AND aila.line_type_lookup_code = 'ITEM'
AND aila.match_type = 'ITEM_TO_PO'
AND NVL(aila.quantity_invoiced,0) < 0
AND aila.po_line_location_id = l_movement_transaction.po_line_location_id
-- Bug 5655040. Commented as condition is modified and few more conditioned
-- added to whereclause to check hold and disregard status.
--AND NOT EXISTS (SELECT 1
-- FROM ap_invoice_distributions_all aida
-- WHERE aida.invoice_id = aia.invoice_id
-- AND aida.invoice_line_number = aila.line_number
-- AND NVL(aida.match_status_flag,'N') <> 'A');
AND NOT EXISTS (SELECT 'Unreleased holds exist'
FROM ap_holds_all aha
WHERE aha.invoice_id = aia.invoice_id
AND aha.release_lookup_code is null)
AND EXISTS (SELECT 'Invoice is approved'
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_id = aia.invoice_id
AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
SELECT
sum(aila.amount)
, MAX(aila.line_number)
, MAX(aia.invoice_id)
, sum(aila.quantity_invoiced)
FROM
ap_invoices_all aia
, ap_invoice_lines_all aila
WHERE aia.invoice_id = aila.invoice_id
AND aia.invoice_type_lookup_code in ('STANDARD','MIXED')
AND aia.cancelled_date IS NULL
AND aila.line_type_lookup_code = 'ITEM'
AND aila.match_type = 'ITEM_TO_RECEIPT'
AND aila.rcv_transaction_id = l_movement_transaction.rcv_transaction_id
-- Bug 5655040. Commented as condition is modified and few more conditioned
-- added to whereclause to check hold and disregard status.
--AND NOT EXISTS (SELECT 1
-- FROM ap_invoice_distributions_all aida
-- WHERE aida.invoice_id = aia.invoice_id
-- AND aida.invoice_line_number = aila.line_number
-- AND NVL(aida.match_status_flag,'N') <> 'A');
AND NOT EXISTS (SELECT 'Unreleased holds exist'
FROM ap_holds_all aha
WHERE aha.invoice_id = aia.invoice_id
AND aha.release_lookup_code is null)
AND EXISTS (SELECT 'Invoice is approved'
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_id = aia.invoice_id
AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
SELECT
SUM(aila.amount)
FROM
ap_invoices_all aia
, ap_invoice_lines_all aila
WHERE aia.invoice_id = aila.invoice_id
AND aia.cancelled_date IS NULL
AND aila.line_type_lookup_code = 'ITEM'
AND aila.match_type = 'PRICE_CORRECTION'
AND aila.rcv_transaction_id = l_movement_transaction.rcv_transaction_id
-- Bug 5655040. Commented as condition is modified and few more conditioned
-- added to whereclause to check hold and disregard status.
--AND NOT EXISTS (SELECT 1
-- FROM ap_invoice_distributions_all aida
-- WHERE aida.invoice_id = aia.invoice_id
-- AND aida.invoice_line_number = aila.line_number
-- AND NVL(aida.match_status_flag,'N') <> 'A');
AND NOT EXISTS (SELECT 'Unreleased holds exist'
FROM ap_holds_all aha
WHERE aha.invoice_id = aia.invoice_id
AND aha.release_lookup_code is null)
AND EXISTS (SELECT 'Invoice is approved'
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_id = aia.invoice_id
AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
SELECT
SUM(aila.quantity_invoiced)
, SUM(aila.amount)
FROM
ap_invoices_all aia
, ap_invoice_lines_all aila
WHERE aia.invoice_id = aila.invoice_id
AND aia.cancelled_date IS NULL
AND aila.line_type_lookup_code = 'ITEM'
AND aila.match_type = 'QTY_CORRECTION'
AND aila.rcv_transaction_id = l_movement_transaction.rcv_transaction_id
-- Bug 5655040. Commented as condition is modified and few more conditioned
-- added to whereclause to check hold and disregard status.
--AND NOT EXISTS (SELECT 1
-- FROM ap_invoice_distributions_all aida
-- WHERE aida.invoice_id = aia.invoice_id
-- AND aida.invoice_line_number = aila.line_number
-- AND NVL(aida.match_status_flag,'N') <> 'A');
AND NOT EXISTS (SELECT 'Unreleased holds exist'
FROM ap_holds_all aha
WHERE aha.invoice_id = aia.invoice_id
AND aha.release_lookup_code is null)
AND EXISTS (SELECT 'Invoice is approved'
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_id = aia.invoice_id
AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
SELECT
sum(aila.amount)
, MAX(aila.line_number)
, MAX(aia.invoice_id)
, sum(aila.quantity_invoiced)
FROM
ap_invoices_all aia
, ap_invoice_lines_all aila
WHERE aia.invoice_id = aila.invoice_id
AND aia.invoice_type_lookup_code in ('CREDIT','DEBIT')
AND aia.cancelled_date IS NULL
AND aila.line_type_lookup_code = 'ITEM'
AND aila.match_type = 'ITEM_TO_RECEIPT'
AND NVL(aila.quantity_invoiced,0) < 0
AND aila.rcv_transaction_id = l_parent_transaction_id
-- Bug 5655040. Commented as condition is modified and few more conditioned
-- added to whereclause to check hold and disregard status.
--AND NOT EXISTS (SELECT 1
-- FROM ap_invoice_distributions_all aida
-- WHERE aida.invoice_id = aia.invoice_id
-- AND aida.invoice_line_number = aila.line_number
-- AND NVL(aida.match_status_flag,'N') <> 'A');
AND NOT EXISTS (SELECT 'Unreleased holds exist'
FROM ap_holds_all aha
WHERE aha.invoice_id = aia.invoice_id
AND aha.release_lookup_code is null)
AND EXISTS (SELECT 'Invoice is approved'
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_id = aia.invoice_id
AND NVL(aida.match_status_flag, 'N') NOT in ('N', 'S'));
SELECT
item_type_code
, top_model_line_id
FROM
oe_order_lines_all
WHERE line_id = l_movement_transaction.order_line_id;
SELECT
SUM(quantity)
FROM
rcv_transactions
WHERE po_header_id = l_movement_transaction.po_header_id
AND transaction_type = 'RETURN TO VENDOR';
SELECT ordered_quantity
INTO l_total_rma_qty
FROM oe_order_lines_all
WHERE line_id = l_movement_transaction.order_line_id;
SELECT parent_transaction_id
INTO l_parent_transaction_id
FROM rcv_transactions
WHERE transaction_id = l_movement_transaction.rcv_transaction_id;
SELECT
glp.period_name
FROM
gl_periods glp
, gl_ledger_le_v gllv
WHERE gllv.period_set_name = glp.period_set_name
AND gllv.legal_entity_id = p_legal_entity_id
AND gllv.ledger_category_code = 'PRIMARY'
AND glp.period_type = gllv.accounted_period_type
AND NVL(glp.adjustment_period_flag,'N') = 'N'
AND trunc(p_period_date) BETWEEN trunc(glp.start_date) AND trunc(glp.end_date);
SELECT
period_name
FROM
GL_PERIODS
WHERE period_set_name = l_stat_typ_transaction.period_set_name
AND l_movement_transaction.transaction_date between
(start_date) and (end_date)
AND start_date >= l_stat_typ_transaction.start_date
AND end_date <= l_stat_typ_transaction.end_date
AND period_type = l_stat_typ_transaction.period_type
AND NVL(adjustment_period_flag,'N') = 'N';
SELECT
period_name
FROM
GL_PERIODS
WHERE period_set_name = l_stat_typ_transaction.period_set_name
AND trunc(l_movement_transaction.transaction_date) between
trunc(start_date) and trunc(end_date)
AND period_type = l_stat_typ_transaction.period_type
AND start_date >= l_stat_typ_transaction.start_date
AND NVL(adjustment_period_flag,'N') = 'N';
SELECT initial_pickup_date
INTO l_transaction_date
FROM wsh_delivery_details_ob_grp_v wdd
, wsh_new_deliveries_ob_grp_v wnd
, wsh_delivery_assignments wda
WHERE wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_line_id = x_movement_transaction.order_line_id
AND wda.delivery_detail_id = x_movement_transaction.picking_line_detail_id
AND wdd.organization_id = x_movement_transaction.organization_id
AND nvl(wnd.customer_id,wdd.customer_id) = x_movement_transaction.ship_to_customer_id
AND rownum = 1;
SELECT transaction_date
INTO l_transaction_date
FROM MTL_MATERIAL_TRANSACTIONS MMT
WHERE MMT.transaction_id = x_movement_transaction.mtl_transaction_id;
SELECT transaction_date
INTO l_transaction_date
FROM rcv_transactions
WHERE transaction_id = x_movement_transaction.rcv_transaction_id;
SELECT to_number(to_char(LAST_DAY(add_months(l_transaction_date,1)),'DD'))
INTO
l_no_days
FROM DUAL;