VIEW: APPS.AR_IDEP_ACTIONS_NOT_TAKEN

Source

View Text - Preformatted

SELECT ol.commitment_id commitment_id, trx.trx_number trx_number, oh.order_number order_number, ol.pricing_date trx_date, ott.name trx_type, ol.ordered_quantity qty, ol.unit_selling_price price, nvl ( ( ol.ordered_quantity * ol.unit_selling_price ) ,0 ) line_amt, nvl ( ( ol.ordered_quantity * ol.unit_selling_price ) ,0 ) ext_amt, nvl ( op.commitment_applied_amount,0 ) applied_amount, NVL(ar_idep_utils.get_course_description ( ol.line_id, ol.order_quantity_uom ),mt.description) description from mtl_system_items mt, oe_payments op, oe_order_lines_all ol, oe_order_headers_all oh, oe_transaction_types_tl ott, ra_customer_trx_all trx WHERE ol.header_id = oh.header_id and oh.order_type_id = ott.transaction_type_id and oh.org_id = ol.org_id and trx.customer_trx_id = ol.commitment_id and nvl ( ol.cancelled_flag, 'N') = 'N' and nvl ( ol.open_flag, 'Y') = 'Y' and ( ( nvl ( ol.invoice_interface_status_code, 'NO') <> 'YES' ) or ( nvl ( ol.invoice_interface_status_code, 'NO') = 'YES' and exists ( select 1 from ra_interface_lines_all l, ra_customer_trx_lines_all lines where nvl ( l.interface_status, 'A') <> 'P' and l.sales_order = to_char ( oh.order_number ) and l.interface_line_attribute6 = ol.line_id and lines.customer_trx_id = trx.customer_trx_id and l.reference_line_id = lines.customer_trx_line_id ) ) ) and op.header_id ( + ) = ol.header_id and op.line_id ( + ) = ol.line_id and ol.inventory_item_id=mt.inventory_item_id(+) and mt.organization_id = ol.org_id

View Text - HTML Formatted

SELECT OL.COMMITMENT_ID COMMITMENT_ID
, TRX.TRX_NUMBER TRX_NUMBER
, OH.ORDER_NUMBER ORDER_NUMBER
, OL.PRICING_DATE TRX_DATE
, OTT.NAME TRX_TYPE
, OL.ORDERED_QUANTITY QTY
, OL.UNIT_SELLING_PRICE PRICE
, NVL ( ( OL.ORDERED_QUANTITY * OL.UNIT_SELLING_PRICE )
, 0 ) LINE_AMT
, NVL ( ( OL.ORDERED_QUANTITY * OL.UNIT_SELLING_PRICE )
, 0 ) EXT_AMT
, NVL ( OP.COMMITMENT_APPLIED_AMOUNT
, 0 ) APPLIED_AMOUNT
, NVL(AR_IDEP_UTILS.GET_COURSE_DESCRIPTION ( OL.LINE_ID
, OL.ORDER_QUANTITY_UOM )
, MT.DESCRIPTION) DESCRIPTION
FROM MTL_SYSTEM_ITEMS MT
, OE_PAYMENTS OP
, OE_ORDER_LINES_ALL OL
, OE_ORDER_HEADERS_ALL OH
, OE_TRANSACTION_TYPES_TL OTT
, RA_CUSTOMER_TRX_ALL TRX
WHERE OL.HEADER_ID = OH.HEADER_ID
AND OH.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID
AND OH.ORG_ID = OL.ORG_ID
AND TRX.CUSTOMER_TRX_ID = OL.COMMITMENT_ID
AND NVL ( OL.CANCELLED_FLAG
, 'N') = 'N'
AND NVL ( OL.OPEN_FLAG
, 'Y') = 'Y'
AND ( ( NVL ( OL.INVOICE_INTERFACE_STATUS_CODE
, 'NO') <> 'YES' ) OR ( NVL ( OL.INVOICE_INTERFACE_STATUS_CODE
, 'NO') = 'YES'
AND EXISTS ( SELECT 1
FROM RA_INTERFACE_LINES_ALL L
, RA_CUSTOMER_TRX_LINES_ALL LINES
WHERE NVL ( L.INTERFACE_STATUS
, 'A') <> 'P'
AND L.SALES_ORDER = TO_CHAR ( OH.ORDER_NUMBER )
AND L.INTERFACE_LINE_ATTRIBUTE6 = OL.LINE_ID
AND LINES.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND L.REFERENCE_LINE_ID = LINES.CUSTOMER_TRX_LINE_ID ) ) )
AND OP.HEADER_ID ( + ) = OL.HEADER_ID
AND OP.LINE_ID ( + ) = OL.LINE_ID
AND OL.INVENTORY_ITEM_ID=MT.INVENTORY_ITEM_ID(+)
AND MT.ORGANIZATION_ID = OL.ORG_ID