VIEW: APPS.PA_CC_DIST_LINES_SUMMARIZE_V

Source

View Text - Preformatted

SELECT SUM(decode(sign(ccd.denom_transfer_price), 1, ccd.denom_transfer_price, 0 )) ENTERED_DR, SUM(decode(sign(ccd.denom_transfer_price), -1, -1 * ccd.denom_transfer_price, 0 )) ENTERED_CR, SUM(decode(sign(ccd.amount), 1, ccd.amount, 0 )) ACCOUNTED_DR, SUM(decode(sign(ccd.amount), -1, -1 * ccd.amount, 0 )) ACCOUNTED_CR, ccd.denom_tp_currency_code, ccd.acct_currency_code, ccd.gl_date, ccd.gl_batch_name, ccd.line_type, ccd.request_id, ccd.dr_code_combination_id, ccd.transfer_status_code, ccd.transfer_rejection_code, count(*), ccd.pa_date, 'DR', CCD.org_id FROM PA_CC_DIST_LINES_ALL CCD WHERE ccd.transfer_status_code in ('A', 'R') GROUP BY ccd.denom_tp_currency_code, ccd.acct_currency_code, ccd.gl_date, ccd.gl_batch_name, ccd.dr_code_combination_id, ccd.line_type, ccd.transfer_status_code, ccd.transfer_rejection_code, ccd.pa_date, ccd.request_id, ccd.org_id UNION ALL SELECT SUM(decode(sign(ccd.denom_transfer_price), -1, -1 * ccd.denom_transfer_price, 0 )) ENTERED_DR, SUM(decode(sign(ccd.denom_transfer_price), 1, ccd.denom_transfer_price, 0 )) ENTERED_CR, SUM(decode(sign(ccd.amount), -1, -1 * ccd.amount, 0 )) ACCOUNTED_DR, SUM(decode(sign(ccd.amount), 1, ccd.amount, 0 )) ACCOUNTED_CR, ccd.denom_tp_currency_code, ccd.acct_currency_code, ccd.gl_date, ccd.gl_batch_name, ccd.line_type, ccd.request_id, ccd.cr_code_combination_id, ccd.transfer_status_code, ccd.transfer_rejection_code, count(*), ccd.pa_date, 'CR', CCD.org_id FROM PA_CC_DIST_LINES_ALL CCD WHERE ccd.transfer_status_code in ('A', 'R') GROUP BY ccd.denom_tp_currency_code, ccd.acct_currency_code, ccd.gl_date, ccd.gl_batch_name, ccd.cr_code_combination_id, ccd.line_type, ccd.transfer_status_code, ccd.transfer_rejection_code, ccd.pa_date, ccd.request_id, CCD.org_id

View Text - HTML Formatted

SELECT SUM(DECODE(SIGN(CCD.DENOM_TRANSFER_PRICE)
, 1
, CCD.DENOM_TRANSFER_PRICE
, 0 )) ENTERED_DR
, SUM(DECODE(SIGN(CCD.DENOM_TRANSFER_PRICE)
, -1
, -1 * CCD.DENOM_TRANSFER_PRICE
, 0 )) ENTERED_CR
, SUM(DECODE(SIGN(CCD.AMOUNT)
, 1
, CCD.AMOUNT
, 0 )) ACCOUNTED_DR
, SUM(DECODE(SIGN(CCD.AMOUNT)
, -1
, -1 * CCD.AMOUNT
, 0 )) ACCOUNTED_CR
, CCD.DENOM_TP_CURRENCY_CODE
, CCD.ACCT_CURRENCY_CODE
, CCD.GL_DATE
, CCD.GL_BATCH_NAME
, CCD.LINE_TYPE
, CCD.REQUEST_ID
, CCD.DR_CODE_COMBINATION_ID
, CCD.TRANSFER_STATUS_CODE
, CCD.TRANSFER_REJECTION_CODE
, COUNT(*)
, CCD.PA_DATE
, 'DR'
, CCD.ORG_ID
FROM PA_CC_DIST_LINES_ALL CCD
WHERE CCD.TRANSFER_STATUS_CODE IN ('A'
, 'R') GROUP BY CCD.DENOM_TP_CURRENCY_CODE
, CCD.ACCT_CURRENCY_CODE
, CCD.GL_DATE
, CCD.GL_BATCH_NAME
, CCD.DR_CODE_COMBINATION_ID
, CCD.LINE_TYPE
, CCD.TRANSFER_STATUS_CODE
, CCD.TRANSFER_REJECTION_CODE
, CCD.PA_DATE
, CCD.REQUEST_ID
, CCD.ORG_ID UNION ALL SELECT SUM(DECODE(SIGN(CCD.DENOM_TRANSFER_PRICE)
, -1
, -1 * CCD.DENOM_TRANSFER_PRICE
, 0 )) ENTERED_DR
, SUM(DECODE(SIGN(CCD.DENOM_TRANSFER_PRICE)
, 1
, CCD.DENOM_TRANSFER_PRICE
, 0 )) ENTERED_CR
, SUM(DECODE(SIGN(CCD.AMOUNT)
, -1
, -1 * CCD.AMOUNT
, 0 )) ACCOUNTED_DR
, SUM(DECODE(SIGN(CCD.AMOUNT)
, 1
, CCD.AMOUNT
, 0 )) ACCOUNTED_CR
, CCD.DENOM_TP_CURRENCY_CODE
, CCD.ACCT_CURRENCY_CODE
, CCD.GL_DATE
, CCD.GL_BATCH_NAME
, CCD.LINE_TYPE
, CCD.REQUEST_ID
, CCD.CR_CODE_COMBINATION_ID
, CCD.TRANSFER_STATUS_CODE
, CCD.TRANSFER_REJECTION_CODE
, COUNT(*)
, CCD.PA_DATE
, 'CR'
, CCD.ORG_ID
FROM PA_CC_DIST_LINES_ALL CCD
WHERE CCD.TRANSFER_STATUS_CODE IN ('A'
, 'R') GROUP BY CCD.DENOM_TP_CURRENCY_CODE
, CCD.ACCT_CURRENCY_CODE
, CCD.GL_DATE
, CCD.GL_BATCH_NAME
, CCD.CR_CODE_COMBINATION_ID
, CCD.LINE_TYPE
, CCD.TRANSFER_STATUS_CODE
, CCD.TRANSFER_REJECTION_CODE
, CCD.PA_DATE
, CCD.REQUEST_ID
, CCD.ORG_ID