SELECT SUM (rctl.extended_amount) sales_amount, rct.trx_class, rctt.
NAME,
(SELECT SUM ([Link]) staff_pur
FROM ar_cash_receipts_all arc, ar_receipt_methods arm
WHERE arc.receipt_method_id = arm.receipt_method_id
AND [Link] LIKE 'FREE%') amount,
(SELECT SUM ([Link])
FROM ar_cash_receipts_all arc,
ar_receipt_methods arm
WHERE arc.receipt_method_id = arm.receipt_method_id
AND [Link] = 'Master-106'
AND TO_CHAR (arc.receipt_date, 'DD-MON-YYYY') = '06-09-2019'
GROUP BY [Link]) card_master,
(SELECT SUM ([Link])
FROM ar_cash_receipts_all arc,
ar_receipt_methods arm
WHERE arc.receipt_method_id = arm.receipt_method_id
AND [Link] = 'Span-106'
AND TO_CHAR (arc.receipt_date, 'DD-MON-YYYY') = '06-09-2019'
GROUP BY [Link]) card_span,
(SELECT SUM ([Link])
FROM ar_cash_receipts_all arc,
ar_receipt_methods arm
WHERE arc.receipt_method_id = arm.receipt_method_id
AND [Link] = 'Visa-106'
AND TO_CHAR (arc.receipt_date, 'DD-MON-YYYY') = '06-09-2019'
GROUP BY [Link]) card_visa,
(SELECT SUM ([Link])
FROM ar_cash_receipts_all arc, ar_receipt_methods arm
WHERE arc.receipt_method_id = arm.receipt_method_id
AND [Link] = 'Coupon-106'
AND TO_CHAR (arc.receipt_date, 'DD-MON-YYYY') = '06-09-2019'
GROUP BY [Link]) coupon,
(SELECT ABS (SUM (rctl.extended_amount)) refund
FROM ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
ra_cust_trx_types_all rctt
WHERE 1 = 1
AND rct.customer_trx_id = rctl.customer_trx_id
AND rctt.cust_trx_type_seq_id = rct.cust_trx_type_seq_id
AND [Link] = 'CM-70'
AND TO_CHAR (rct.trx_date, 'DD-MON-YYYY') = '07-09-2019'
GROUP BY rct.trx_class, [Link]) refund,
(SELECT SUM ([Link])
FROM ar_cash_receipts_all arc,
ar_receipt_methods arm
WHERE arc.receipt_method_id = arm.receipt_method_id
AND [Link] LIKE 'Cash-106'
AND TO_CHAR (arc.receipt_date, 'DD-MON-YYYY') = '06-09-2019'
GROUP BY [Link]) cash_received_in_branch,
(SELECT ABS (SUM ([Link]))
FROM ar_cash_receipts_all arc,
ar_receipt_methods arm
WHERE arc.receipt_method_id = arm.receipt_method_id
AND [Link] = 'Cash-106'
AND [Link] = 'MISC'
AND TO_CHAR (arc.receipt_date, 'DD-MON-YYYY') = '06-09-2019'
GROUP BY [Link])
cash_transfer_to_hq,rcgl.code_combination_id,fndvl.flex_value,[Link]
ACCT_NAME
FROM ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
ra_cust_trx_types_all rctt,
RA_CUST_TRX_LINE_GL_DIST_ALL rcgl,
gl_code_combinations gcc,
fnd_vs_value_sets fndv,
fnd_flex_values_vl fndvl
WHERE 1 = 1
AND rct.customer_trx_id = rctl.customer_trx_id
AND rctt.cust_trx_type_seq_id = rct.cust_trx_type_seq_id
AND rct.trx_class LIKE 'INV'
and rct.customer_trx_id=rcgl.customer_trx_id
AND TO_CHAR (rct.trx_date, 'DD-MON-YYYY') = '06-09-2019'
AND [Link] = 'IN-106'
and gcc.code_combination_id=rcgl.code_combination_id
and fndv.value_set_id = fndvl.flex_value_set_id
and fndv.value_set_code = 'Branch Barn_Structure'
and fndvl.flex_value = gcc.segment3
GROUP BY rct.trx_class,
[Link],rcgl.code_combination_id,fndvl.flex_value,[Link]