VIEW: APPS.AR_IDEP_ACTIONS_TAKEN

Source

View Text - Preformatted

SELECT trx.initial_customer_trx_id commitment_id, trx.customer_trx_id trx_id, trx.trx_number trx_number, line.sales_order sales_order, trx.trx_date trx_date, type.type cm_inv, type.name child_type, nvl ( ar_idep_utils.get_course_description ( line.interface_line_attribute6, line.uom_code ) , line.description ) description, nvl ( line.quantity_ordered,line.quantity_invoiced ) qty, nvl ( line.unit_selling_price,line.unit_standard_price ) price, nvl ( line.extended_amount,0 ) line_amt, nvl ( line.extended_amount,0 ) ext_amt, nvl ( taxline.extended_amount,0 ) tax_freight_amt, ( nvl ( line.extended_amount,0 ) + nvl ( taxline.extended_amount,0 ) ) total_line_amt, line.line_number line_number from ra_cust_trx_types_all type, ra_customer_trx_lines_all taxline, ra_customer_trx_lines_all line, ra_customer_trx_all trx where trx.cust_trx_type_id = type.cust_trx_type_id and type.type = 'INV' and complete_flag = 'Y' and line.customer_trx_id = trx.customer_trx_id and line.line_type = 'LINE' and trx.org_id = type.org_id and line.org_id = trx.org_id and nvl(taxline.line_type,'TAX') = 'TAX' and taxline.link_to_cust_trx_line_id(+) =line.customer_trx_line_id and taxline.org_id(+) =line.org_id UNION /* Credit Memos against Commitment */ select trx.previous_customer_trx_id initial_customer_trx_id, trx.customer_trx_id child_customer_trx_id, trx.trx_number trx_number, line.sales_order sales_order, trx.trx_date trx_date, type.type cm_inv, type.name child_type, nvl ( ar_idep_utils.get_course_description ( line.interface_line_attribute6, line.uom_code ) , line.description ) description, nvl ( line.quantity_ordered,0 ) qty, nvl ( line.unit_selling_price,0 ) price, ( nvl ( line.extended_amount,0 ) ) line_amt, ( nvl ( line.extended_amount,0 ) ) ext_amt, ( nvl ( taxline.extended_amount,0 ) ) tax_freight_amt, (( nvl ( line.extended_amount,0 ) ) + ( nvl( taxline.extended_amount,0 ) ) ) total_line_amt, line.line_number line_number from ra_cust_trx_types_all type, ra_customer_trx_lines_all taxline, ra_customer_trx_lines_all line, ra_customer_trx_all trx where trx.cust_trx_type_id = type.cust_trx_type_id and type.type = 'CM' and complete_flag = 'Y' and line.customer_trx_id = trx.customer_trx_id and line.line_type = 'LINE' and trx.org_id = type.org_id and line.org_id = trx.org_id and nvl(taxline.line_type,'TAX') = 'TAX' and line.customer_trx_line_id = taxline.link_to_cust_trx_line_id ( + ) and line.org_id = taxline.org_id ( + ) UNION /* Credit Memos on Invoices against the Commitment */ select trx.initial_customer_trx_id initial_customer_trx_id, trx.customer_trx_id child_cm_cust_trx_id , trx.trx_number child_trx_number_cm, line.sales_order sales_order, trx.trx_date trx_date_cm, type.type cm_inv_cm, type.name child_type_cm, line.description description, nvl ( line.quantity_ordered,line.quantity_invoiced ) qty, nvl ( line.unit_selling_price,line.unit_standard_price ) price, nvl ( line.extended_amount,0 ) line_amt, nvl ( line.extended_amount,0 ) ext_amt, nvl ( taxline.extended_amount,0 ) tax_freight_amt, ( nvl ( line.extended_amount,0 ) + nvl ( taxline.extended_amount,0 ) ) total_line_amt, line.customer_trx_line_id line_id from ra_customer_trx_all trx, ra_customer_trx_lines_all taxline, ra_customer_trx_lines_all line, ra_cust_trx_types_all type where trx.initial_customer_trx_id <> trx.previous_customer_trx_id and trx.cust_trx_type_id = type.cust_trx_type_id and type.type = 'CM' and complete_flag = 'Y' and line.line_type = 'LINE' and line.customer_trx_id = trx.customer_trx_id and nvl(taxline.line_type,'TAX') = 'TAX' and line.customer_trx_line_id = taxline.link_to_cust_trx_line_id ( + ) and line.org_id = taxline.org_id ( + ) and trx.org_id = type.org_id

View Text - HTML Formatted

SELECT TRX.INITIAL_CUSTOMER_TRX_ID COMMITMENT_ID
, TRX.CUSTOMER_TRX_ID TRX_ID
, TRX.TRX_NUMBER TRX_NUMBER
, LINE.SALES_ORDER SALES_ORDER
, TRX.TRX_DATE TRX_DATE
, TYPE.TYPE CM_INV
, TYPE.NAME CHILD_TYPE
, NVL ( AR_IDEP_UTILS.GET_COURSE_DESCRIPTION ( LINE.INTERFACE_LINE_ATTRIBUTE6
, LINE.UOM_CODE )
, LINE.DESCRIPTION ) DESCRIPTION
, NVL ( LINE.QUANTITY_ORDERED
, LINE.QUANTITY_INVOICED ) QTY
, NVL ( LINE.UNIT_SELLING_PRICE
, LINE.UNIT_STANDARD_PRICE ) PRICE
, NVL ( LINE.EXTENDED_AMOUNT
, 0 ) LINE_AMT
, NVL ( LINE.EXTENDED_AMOUNT
, 0 ) EXT_AMT
, NVL ( TAXLINE.EXTENDED_AMOUNT
, 0 ) TAX_FREIGHT_AMT
, ( NVL ( LINE.EXTENDED_AMOUNT
, 0 ) + NVL ( TAXLINE.EXTENDED_AMOUNT
, 0 ) ) TOTAL_LINE_AMT
, LINE.LINE_NUMBER LINE_NUMBER
FROM RA_CUST_TRX_TYPES_ALL TYPE
, RA_CUSTOMER_TRX_LINES_ALL TAXLINE
, RA_CUSTOMER_TRX_LINES_ALL LINE
, RA_CUSTOMER_TRX_ALL TRX
WHERE TRX.CUST_TRX_TYPE_ID = TYPE.CUST_TRX_TYPE_ID
AND TYPE.TYPE = 'INV'
AND COMPLETE_FLAG = 'Y'
AND LINE.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND LINE.LINE_TYPE = 'LINE'
AND TRX.ORG_ID = TYPE.ORG_ID
AND LINE.ORG_ID = TRX.ORG_ID
AND NVL(TAXLINE.LINE_TYPE
, 'TAX') = 'TAX'
AND TAXLINE.LINK_TO_CUST_TRX_LINE_ID(+) =LINE.CUSTOMER_TRX_LINE_ID
AND TAXLINE.ORG_ID(+) =LINE.ORG_ID UNION /* CREDIT MEMOS AGAINST COMMITMENT */ SELECT TRX.PREVIOUS_CUSTOMER_TRX_ID INITIAL_CUSTOMER_TRX_ID
, TRX.CUSTOMER_TRX_ID CHILD_CUSTOMER_TRX_ID
, TRX.TRX_NUMBER TRX_NUMBER
, LINE.SALES_ORDER SALES_ORDER
, TRX.TRX_DATE TRX_DATE
, TYPE.TYPE CM_INV
, TYPE.NAME CHILD_TYPE
, NVL ( AR_IDEP_UTILS.GET_COURSE_DESCRIPTION ( LINE.INTERFACE_LINE_ATTRIBUTE6
, LINE.UOM_CODE )
, LINE.DESCRIPTION ) DESCRIPTION
, NVL ( LINE.QUANTITY_ORDERED
, 0 ) QTY
, NVL ( LINE.UNIT_SELLING_PRICE
, 0 ) PRICE
, ( NVL ( LINE.EXTENDED_AMOUNT
, 0 ) ) LINE_AMT
, ( NVL ( LINE.EXTENDED_AMOUNT
, 0 ) ) EXT_AMT
, ( NVL ( TAXLINE.EXTENDED_AMOUNT
, 0 ) ) TAX_FREIGHT_AMT
, (( NVL ( LINE.EXTENDED_AMOUNT
, 0 ) ) + ( NVL( TAXLINE.EXTENDED_AMOUNT
, 0 ) ) ) TOTAL_LINE_AMT
, LINE.LINE_NUMBER LINE_NUMBER
FROM RA_CUST_TRX_TYPES_ALL TYPE
, RA_CUSTOMER_TRX_LINES_ALL TAXLINE
, RA_CUSTOMER_TRX_LINES_ALL LINE
, RA_CUSTOMER_TRX_ALL TRX
WHERE TRX.CUST_TRX_TYPE_ID = TYPE.CUST_TRX_TYPE_ID
AND TYPE.TYPE = 'CM'
AND COMPLETE_FLAG = 'Y'
AND LINE.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND LINE.LINE_TYPE = 'LINE'
AND TRX.ORG_ID = TYPE.ORG_ID
AND LINE.ORG_ID = TRX.ORG_ID
AND NVL(TAXLINE.LINE_TYPE
, 'TAX') = 'TAX'
AND LINE.CUSTOMER_TRX_LINE_ID = TAXLINE.LINK_TO_CUST_TRX_LINE_ID ( + )
AND LINE.ORG_ID = TAXLINE.ORG_ID ( + ) UNION /* CREDIT MEMOS ON INVOICES AGAINST THE COMMITMENT */ SELECT TRX.INITIAL_CUSTOMER_TRX_ID INITIAL_CUSTOMER_TRX_ID
, TRX.CUSTOMER_TRX_ID CHILD_CM_CUST_TRX_ID
, TRX.TRX_NUMBER CHILD_TRX_NUMBER_CM
, LINE.SALES_ORDER SALES_ORDER
, TRX.TRX_DATE TRX_DATE_CM
, TYPE.TYPE CM_INV_CM
, TYPE.NAME CHILD_TYPE_CM
, LINE.DESCRIPTION DESCRIPTION
, NVL ( LINE.QUANTITY_ORDERED
, LINE.QUANTITY_INVOICED ) QTY
, NVL ( LINE.UNIT_SELLING_PRICE
, LINE.UNIT_STANDARD_PRICE ) PRICE
, NVL ( LINE.EXTENDED_AMOUNT
, 0 ) LINE_AMT
, NVL ( LINE.EXTENDED_AMOUNT
, 0 ) EXT_AMT
, NVL ( TAXLINE.EXTENDED_AMOUNT
, 0 ) TAX_FREIGHT_AMT
, ( NVL ( LINE.EXTENDED_AMOUNT
, 0 ) + NVL ( TAXLINE.EXTENDED_AMOUNT
, 0 ) ) TOTAL_LINE_AMT
, LINE.CUSTOMER_TRX_LINE_ID LINE_ID
FROM RA_CUSTOMER_TRX_ALL TRX
, RA_CUSTOMER_TRX_LINES_ALL TAXLINE
, RA_CUSTOMER_TRX_LINES_ALL LINE
, RA_CUST_TRX_TYPES_ALL TYPE
WHERE TRX.INITIAL_CUSTOMER_TRX_ID <> TRX.PREVIOUS_CUSTOMER_TRX_ID
AND TRX.CUST_TRX_TYPE_ID = TYPE.CUST_TRX_TYPE_ID
AND TYPE.TYPE = 'CM'
AND COMPLETE_FLAG = 'Y'
AND LINE.LINE_TYPE = 'LINE'
AND LINE.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND NVL(TAXLINE.LINE_TYPE
, 'TAX') = 'TAX'
AND LINE.CUSTOMER_TRX_LINE_ID = TAXLINE.LINK_TO_CUST_TRX_LINE_ID ( + )
AND LINE.ORG_ID = TAXLINE.ORG_ID ( + )
AND TRX.ORG_ID = TYPE.ORG_ID