[Home] [Help]
328: CURSOR l_cto_amt IS
329: SELECT
330: SUM(ratl.extended_amount)
331: FROM
332: RA_CUSTOMER_TRX_ALL rat
333: , RA_CUSTOMER_TRX_LINES_ALL ratl
334: , RA_CUST_TRX_TYPES_ALL ratt
335: , oe_order_lines_all oola
336: WHERE rat.customer_trx_id = ratl.customer_trx_id
355: CURSOR l_cto_cm_amt IS
356: SELECT
357: SUM(ratl.extended_amount)
358: FROM
359: RA_CUSTOMER_TRX_ALL rat
360: , RA_CUSTOMER_TRX_LINES_ALL ratl
361: , RA_CUST_TRX_TYPES_ALL ratt
362: , oe_order_lines_all oola
363: WHERE rat.customer_trx_id = ratl.customer_trx_id
437: CURSOR l_cto_amt_drp IS
438: SELECT
439: SUM(ratl.extended_amount)
440: FROM
441: RA_CUSTOMER_TRX_ALL rat
442: , RA_CUSTOMER_TRX_LINES_ALL ratl
443: , RA_CUST_TRX_TYPES_ALL ratt --add in for fixing bug 2447381
444: , oe_order_lines_all oola
445: WHERE rat.customer_trx_id = ratl.customer_trx_id
465: CURSOR l_cto_cm_amt_drp IS
466: SELECT
467: SUM(ratl.extended_amount)
468: FROM
469: RA_CUSTOMER_TRX_ALL rat
470: , RA_CUSTOMER_TRX_LINES_ALL ratl
471: , RA_CUST_TRX_TYPES_ALL ratt
472: , oe_order_lines_all oola
473: WHERE rat.customer_trx_id = ratl.customer_trx_id
549: CURSOR l_cto_amt_rma IS
550: SELECT
551: SUM(ratl.extended_amount)
552: FROM
553: RA_CUSTOMER_TRX_ALL rat
554: , RA_CUSTOMER_TRX_LINES_ALL ratl
555: , RA_CUST_TRX_TYPES_ALL ratt
556: , oe_order_lines_all oola
557: WHERE rat.customer_trx_id = ratl.customer_trx_id
689: FROM
690: AP_INVOICES_ALL aia
691: , AP_INVOICE_LINES_ALL aila
692: , RA_CUSTOMER_TRX_LINES_ALL rctla
693: , ra_customer_trx_all rcta
694: , oe_order_lines_all oola
695: WHERE aia.invoice_id = aila.invoice_id
696: AND aia.cancelled_date IS NULL
697: AND aila.line_type_lookup_code = 'ITEM'
871: CURSOR c_invoiced_line_qty IS
872: SELECT
873: SUM(NVL(ratl.quantity_invoiced,0))
874: FROM
875: RA_CUSTOMER_TRX_ALL rat
876: , RA_CUSTOMER_TRX_LINES_ALL ratl
877: , RA_CUST_TRX_TYPES_ALL ratt
878: WHERE rat.customer_trx_id = ratl.customer_trx_id
879: AND rat.cust_trx_type_id = ratt.cust_trx_type_id
1012: SELECT
1013: SUM(NVL(ratl.extended_amount,0))
1014: , SUM(NVL(ratl.quantity_credited,0))
1015: FROM
1016: RA_CUSTOMER_TRX_ALL rat
1017: , RA_CUSTOMER_TRX_LINES_ALL ratl
1018: , RA_CUST_TRX_TYPES_ALL ratt
1019: , OE_ORDER_LINES_ALL oola
1020: , RCV_TRANSACTIONS rt
2014: , rat.exchange_rate_type
2015: , rat.exchange_date
2016: , NVL(rat.invoice_currency_code,l_movement_transaction.currency_code)
2017: FROM
2018: RA_CUSTOMER_TRX_ALL rat
2019: WHERE rat.customer_trx_id = x_movement_transaction.invoice_id;
2020:
2021: -- Cursor for SO invoices which are non credit/debit memos.
2022:
2026: , MAX(rat.customer_trx_id)
2027: , SUM(ratl.extended_amount)
2028: , SUM(NVL(ratl.quantity_invoiced,l_movement_transaction.transaction_quantity))
2029: FROM
2030: RA_CUSTOMER_TRX_ALL rat
2031: , RA_CUSTOMER_TRX_LINES_ALL ratl
2032: , RA_CUST_TRX_TYPES_ALL ratt
2033: WHERE rat.customer_trx_id = ratl.customer_trx_id
2034: AND rat.cust_trx_type_id = ratt.cust_trx_type_id
2055: , MAX(rat.customer_trx_id)
2056: , SUM(ratl.extended_amount)
2057: , SUM(NVL(ratl.quantity_credited,l_movement_transaction.transaction_quantity))
2058: FROM
2059: RA_CUSTOMER_TRX_ALL rat
2060: , RA_CUSTOMER_TRX_LINES_ALL ratl
2061: , RA_CUST_TRX_TYPES_ALL ratt
2062: WHERE rat.customer_trx_id = ratl.customer_trx_id
2063: AND rat.cust_trx_type_id = ratt.cust_trx_type_id
2078: CURSOR l_sum_cm_arc IS
2079: SELECT
2080: SUM(ratl.extended_amount)
2081: FROM
2082: RA_CUSTOMER_TRX_ALL rat
2083: , RA_CUSTOMER_TRX_LINES_ALL ratl
2084: , RA_CUST_TRX_TYPES_ALL ratt
2085: WHERE rat.customer_trx_id = ratl.customer_trx_id
2086: AND rat.cust_trx_type_id = ratt.cust_trx_type_id
2108: , MAX(rat.customer_trx_id)
2109: , SUM(ratl.extended_amount)
2110: , SUM(NVL(ratl.quantity_invoiced,l_movement_transaction.transaction_quantity))
2111: FROM
2112: RA_CUSTOMER_TRX_ALL rat
2113: , RA_CUSTOMER_TRX_LINES_ALL ratl
2114: , RA_CUST_TRX_TYPES_ALL ratt --add in for fixing bug 2447381
2115: WHERE rat.customer_trx_id = ratl.customer_trx_id
2116: --AND ratl.quantity_invoiced = ratl.quantity_ordered
2136: CURSOR l_sum_cm_drparc IS
2137: SELECT
2138: SUM(ratl.extended_amount)
2139: FROM
2140: RA_CUSTOMER_TRX_ALL rat
2141: , RA_CUSTOMER_TRX_LINES_ALL ratl
2142: , RA_CUST_TRX_TYPES_ALL ratt
2143: WHERE rat.customer_trx_id = ratl.customer_trx_id
2144: --AND ratl.quantity_invoiced = ratl.quantity_ordered
2166: , MAX(rat.customer_trx_id)
2167: , SUM(ratl.extended_amount)
2168: , SUM(NVL(ratl.quantity_invoiced,l_movement_transaction.transaction_quantity))
2169: FROM
2170: RA_CUSTOMER_TRX_ALL rat
2171: , RA_CUSTOMER_TRX_LINES_ALL ratl
2172: WHERE rat.customer_trx_id = ratl.customer_trx_id
2173: AND ratl.line_type = 'LINE'
2174: AND ratl.sales_order = to_char(l_movement_transaction.order_number)
2194: --FROM
2195: -- AP_INVOICES_ALL aia
2196: --, AP_INVOICE_DISTRIBUTIONS_ALL aida
2197: --, RA_CUSTOMER_TRX_LINES_ALL rctla
2198: --, ra_customer_trx_all rcta
2199: --WHERE aia.invoice_id = aida.invoice_id
2200: -- AND (NVL(aida.match_status_flag,'N') = 'A'
2201: -- OR (NVL(aida.match_status_flag,'N') = 'T'
2202: -- AND aia.wfapproval_status = 'NOT REQUIRED'))
2218: FROM
2219: AP_INVOICES_ALL aia
2220: , AP_INVOICE_LINES_ALL aila
2221: , RA_CUSTOMER_TRX_LINES_ALL rctla
2222: , ra_customer_trx_all rcta
2223: WHERE aia.invoice_id = aila.invoice_id
2224: AND aia.cancelled_date IS NULL
2225: AND aila.line_type_lookup_code = 'ITEM'
2226: AND aia.reference_1 = TO_CHAR(rctla.customer_trx_id)
2279: , ap.batch_id
2280: , ap.invoice_date
2281: FROM
2282: AP_INVOICES_ALL ap
2283: , RA_CUSTOMER_TRX_ALL rctl
2284: WHERE ap.invoice_id = x_movement_transaction.invoice_id
2285: AND ap.reference_1 = rctl.customer_trx_id;
2286:
2287: -- Cursor to get all the invoice information for drop shipments after fetching
2295: , rat.exchange_rate_type
2296: , rat.exchange_date
2297: , NVL(rat.invoice_currency_code,l_movement_transaction.currency_code)
2298: FROM
2299: RA_CUSTOMER_TRX_ALL rat
2300: , RA_CUSTOMER_TRX_LINES_ALL ratl
2301: WHERE rat.customer_trx_id = ratl.customer_trx_id
2302: --AND ratl.quantity_invoiced = ratl.quantity_ordered
2303: AND NVL(UPPER(ratl.sales_order_source),'ORDER ENTRY') = 'ORDER ENTRY'