[Home] [Help]
161: */
162:
163: /* Bug#6649580
164: * As part of bug#5443196 fix to improve the performance of 'Pay on Receipt'
165: * concurrent request, index RCV_TRANSACTIONS_N16 was changed to function based
166: * index (SOURCE_DOCUMENT_CODE, NVL(INVOICE_STATUS_CODE,'NA'),TRANSACTION_TYPE).
167: * But in the following cursor C_receipt_txns, there is no nvl on
168: * rts.invoice_status_code and this index is not effectively used and results in
169: * performance issue.
171: * in 1159 bug#4732594 fix.
172: */
173:
174: CURSOR C_receipt_txns IS
175: SELECT /*+ INDEX (rts RCV_TRANSACTIONS_N16) */
176: rts.rowid rcv_txn_rowid,
177: rts.transaction_id,
178: rts.po_header_id,
179: rts.po_release_id,
221: po_lines_all pls,
222: po_line_locations_all plls,
223: rcv_shipment_headers rsh,
224: rcv_shipment_lines rsl,
225: rcv_transactions rts
226: WHERE rts.shipment_header_id = rsh.shipment_header_id
227: AND rts.po_header_id = phs.po_header_id
228: --AND rts.po_release_id = prs.po_release_id(+) /*Bug 5443196*/
229: AND rts.po_line_location_id = plls.line_location_id
249: AND rts.transaction_date <= sysdate - l_aging_period
250: AND rts.po_release_id IS null -- Bug 5443196
251: AND nvl(plls.lcm_flag, 'N') = 'N' -- Bug 7758359: Added this condition so that LCM Receipts are not picked up.
252: UNION
253: SELECT /*+ INDEX (rts RCV_TRANSACTIONS_N16) */
254: rts.rowid rcv_txn_rowid,
255: rts.transaction_id,
256: rts.po_header_id,
257: rts.po_release_id,
299: po_lines_all pls,
300: po_line_locations_all plls,
301: rcv_shipment_headers rsh,
302: rcv_shipment_lines rsl,
303: rcv_transactions rts
304: WHERE rts.shipment_header_id = rsh.shipment_header_id
305: AND rts.po_header_id = phs.po_header_id
306: AND rts.po_release_id = prs.po_release_id
307: AND rts.po_line_location_id = plls.line_location_id
396: po_lines pls,
397: po_line_locations plls,
398: rcv_shipment_headers rsh,
399: rcv_shipment_lines rsl,
400: rcv_transactions rts
401: WHERE rts.shipment_header_id = rsh.shipment_header_id
402: AND rts.po_header_id = phs.po_header_id
403: AND rts.po_release_id = prs.po_release_id(+)
404: AND rts.po_line_location_id = plls.line_location_id
458: X_curr_invoice_num ap_invoices.invoice_num%TYPE;
459:
460: X_curr_vendor_id NUMBER := NULL;
461: X_curr_pay_site_id NUMBER := NULL;
462: X_curr_currency_code rcv_transactions.currency_code%TYPE := NULL;
463: X_curr_payment_terms_id NUMBER := NULL;
464: X_curr_transaction_date DATE := NULL;
465: X_curr_le_transaction_date DATE := NULL; --LE time zone date (Bug: 5205516)
466: X_curr_packing_slip rcv_shipment_headers.receipt_num%TYPE := NULL;
466: X_curr_packing_slip rcv_shipment_headers.receipt_num%TYPE := NULL;
467: X_curr_shipment_header_id NUMBER := NULL;
468: X_curr_osa_flag VARCHAR2(1) := NULL; --Shikyu project
469:
470: X_curr_conversion_rate_type rcv_transactions.currency_conversion_type%TYPE;
471: X_curr_conversion_rate_date DATE;
472: X_curr_conversion_rate NUMBER;
473:
474: /** Bug# 1176326 **/
1152:
1153:
1154: SELECT MIN(NVL(transaction_type, X_receipt_event))
1155: INTO X_inv_event
1156: FROM rcv_transactions
1157: WHERE shipment_line_id = X_rcv_txns.shipment_line_id
1158: AND po_distribution_id = NVL(X_rcv_txns.po_distribution_id,-1)
1159: AND parent_transaction_id = X_rcv_txns.transaction_id
1160: AND transaction_type = 'DELIVER';
1254:
1255: -- update invoice_status_code of 'RECEIVE', 'CORRECT' and
1256: -- 'RETURN TO VENDOR' transactions
1257:
1258: UPDATE rcv_transactions
1259: SET invoice_status_code = DECODE(X_curr_inv_process_flag,'Y','INVOICED','REJECTED'), -- bug 3640106
1260: last_updated_by = FND_GLOBAL.user_id,
1261: last_update_date = sysdate,
1262: last_update_login = FND_GLOBAL.login_id
1263: WHERE transaction_id IN (
1264: SELECT
1265: transaction_id
1266: FROM
1267: rcv_transactions
1268: WHERE
1269: invoice_status_code <> 'INVOICED' AND
1270: transaction_type IN ('RECEIVE','CORRECT','RETURN TO VENDOR')
1271: START WITH transaction_id = X_rcv_txns.transaction_id
1844: transaction_type,
1845: parent_transaction_id,
1846: unit_of_measure -- Added for bug 6822594 : To get the transaction uom
1847: from
1848: rcv_transactions
1849: where
1850: invoice_status_code <> 'INVOICED'
1851: start with transaction_id = c_transaction_id
1852: connect by parent_transaction_id = prior transaction_id;
1888: transaction_type
1889: into
1890: v_parent_type
1891: from
1892: rcv_transactions
1893: where
1894: transaction_id = v_parent_id;
1895:
1896: IF v_parent_type = 'RECEIVE' THEN
1985: amount,
1986: transaction_type,
1987: parent_transaction_id
1988: from
1989: rcv_transactions
1990: where
1991: invoice_status_code <> 'INVOICED'
1992: start with transaction_id = c_transaction_id
1993: connect by parent_transaction_id = prior transaction_id;
2014: transaction_type
2015: into
2016: v_parent_type
2017: from
2018: rcv_transactions
2019: where
2020: transaction_id = v_parent_id;
2021:
2022: IF v_parent_type = 'RECEIVE' THEN