SELECT ORG_ID,
CUSTOMER_NAME,
CUSTOMER_NUMBER,
CUSTOMER_ID,
TRX_DATE,
TRX_NUMBER,
CUSTOMER_TRX_ID,
VOUCHER_NO,
DUE_DATE,
AMOUNT_DR,
AMOUNT_CR,
USER_NAME,
EXCISE_INVOICE_NO,
TAX_INVOICE_NO,
GL_DATE,
CODE_COMBINATION,
ACCOUNT,
AC_DESC,
NAME,
VOC_SEQ
FROM ( SELECT HR.NAME ORG_ID,
ARC.CUSTOMER_NAME,
ARC.CUSTOMER_NUMBER,
ARC.CUSTOMER_ID,
RCT.TRX_DATE,
RCT.TRX_NUMBER,
RCT.CUSTOMER_TRX_ID,
RCT.DOC_SEQUENCE_VALUE VOUCHER_NO,
(SELECT DUE_DATE
FROM AR_PAYMENT_SCHEDULES_ALL
WHERE CUSTOMER_TRX_ID =
RCT.CUSTOMER_TRX_ID
AND ROWNUM = 1)
DUE_DATE,
NVL (
DECODE (
RCTT.TYPE,
'INV',
DECODE (
RCT.INVOICE_CURRENCY_CODE,
'INR',
SUM (RCTL.AMOUNT),
SUM (RCTL.AMOUNT)
* RCT.EXCHANGE_RATE
),
'DM',
DECODE (
RCT.INVOICE_CURRENCY_CODE,
'INR',
SUM (RCTL.AMOUNT),
SUM (RCTL.AMOUNT)
* RCT.EXCHANGE_RATE
),
'DEP',
DECODE (
RCT.INVOICE_CURRENCY_CODE,
'INR',
SUM (RCTL.AMOUNT),
SUM (RCTL.AMOUNT)
* RCT.EXCHANGE_RATE
)
),
0
)
AMOUNT_DR,
NVL (
DECODE (
RCTT.TYPE,
'CM',
DECODE (
RCT.INVOICE_CURRENCY_CODE,
'INR',
ABS (SUM (RCTL.AMOUNT)),
ABS (SUM (RCTL.AMOUNT))
* RCT.EXCHANGE_RATE
)
),
0
)
AMOUNT_CR,
FU.USER_NAME,
(SELECT JATL.EXCISE_INVOICE_NO
FROM JAI_AR_TRX_LINES JATL
WHERE JATL.CUSTOMER_TRX_ID =
RCT.CUSTOMER_TRX_ID
AND ROWNUM = 1)
EXCISE_INVOICE_NO,
RCTL.GL_DATE,
GCC.CONCATENATED_SEGMENTS CODE_COMBINATION,
GCC.SEGMENT3 ACCOUNT,
(SELECT DESCRIPTION
FROM RA_CUSTOMER_TRX_LINES_ALL
WHERE CUSTOMER_TRX_ID =
RCT.CUSTOMER_TRX_ID
AND ROWNUM = 1)
AC_DESC,
RCTT.NAME,
(SELECT NAME
FROM FND_DOCUMENT_SEQUENCES FDS
WHERE FDS.DOC_SEQUENCE_ID =
RCT.DOC_SEQUENCE_ID)
VOC_SEQ,
(SELECT NVL (JOW.VAT_INVOICE_NO,
WND.ATTRIBUTE1)
FROM RA_CUSTOMER_TRX_LINES_ALL RCTL,
JAI_OM_WSH_LINES_ALL JOW,
WSH_NEW_DELIVERIES WND
WHERE RCTL.INTERFACE_LINE_ATTRIBUTE3 =
TO_CHAR (JOW.DELIVERY_ID)
AND WND.DELIVERY_ID =
JOW.DELIVERY_ID
AND RCTL.CUSTOMER_TRX_ID =
RCT.CUSTOMER_TRX_ID
AND ROWNUM = 1)
TAX_INVOICE_NO
FROM RA_CUSTOMER_TRX_ALL RCT,
RA_CUST_TRX_LINE_GL_DIST_ALL RCTL,
FND_USER FU,
RA_CUST_TRX_TYPES_ALL RCTT,
GL_CODE_COMBINATIONS_KFV GCC,
FND_FLEX_VALUES_VL FFVL,
AR_CUSTOMERS ARC,
HR_OPERATING_UNITS HR,
HZ_CUST_ACCOUNTS HCA
WHERE RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND RCTL.ACCOUNT_CLASS = 'REC'
AND FU.USER_ID = RCT.CREATED_BY
AND RCTT.CUST_TRX_TYPE_ID =
RCT.CUST_TRX_TYPE_ID
AND GCC.CODE_COMBINATION_ID =
RCTL.CODE_COMBINATION_ID
AND GCC.SEGMENT3 = FFVL.FLEX_VALUE
AND FFVL.FLEX_VALUE_SET_ID = 1014163
AND ARC.CUSTOMER_ID =
RCT.BILL_TO_CUSTOMER_ID
AND RCT.ORG_ID = RCTT.ORG_ID
--AND RCT.TRX_NUMBER = '84002579'
AND RCT.COMPLETE_FLAG = 'Y'
AND ARC.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
AND TRUNC (RCTL.GL_DATE) BETWEEN P_FROM_DATE
AND P_TO_DATE
AND ARC.CUSTOMER_ID =
NVL (P_CUSTOMER_ID, ARC.CUSTOMER_ID)
-- AND GCC.SEGMENT3 = NVL(:P_ACCOUNT, GCC.SEGMENT3)
--AND RCT.ORG_ID = 81
---NVL(:P_ORG_ID, RCT.ORG_ID)
--- AND SUBSTR(HCA.CUSTOMER_CLASS_CODE, 1,2) = NVL(:P_BUSINESS_LINE, SUBSTR(HCA.CUSTOMER_CLASS_CODE, 1,2))
AND RCT.ORG_ID = HR.ORGANIZATION_ID
AND NVL (ARC.CUSTOMER_CATEGORY_CODE, '~') =
NVL (
P_CUST_TYPE,
NVL (ARC.CUSTOMER_CATEGORY_CODE,
'~')
)
GROUP BY ARC.CUSTOMER_NAME,
ARC.CUSTOMER_NUMBER,
ARC.CUSTOMER_ID,
RCT.TRX_DATE,
RCT.TRX_NUMBER,
RCT.DOC_SEQUENCE_VALUE,
RCT.CUSTOMER_TRX_ID,
FU.USER_NAME,
RCTL.GL_DATE,
RCTT.NAME,
GCC.CONCATENATED_SEGMENTS,
GCC.SEGMENT3,
FFVL.DESCRIPTION,
RCT.INVOICE_CURRENCY_CODE,
RCT.EXCHANGE_RATE,
RCTT.TYPE,
RCT.ORG_ID,
RCT.DOC_SEQUENCE_ID,
HR.NAME
UNION
SELECT HR.NAME ORG_ID,
ARC.CUSTOMER_NAME,
ARC.CUSTOMER_NUMBER,
ARC.CUSTOMER_ID,
ACR.RECEIPT_DATE TRX_DATE,
ACR.RECEIPT_NUMBER TRX_NUMBER,
ACR.CASH_RECEIPT_ID CUSTOMER_TRX_ID,
ACR.DOC_SEQUENCE_VALUE VOUCHER_NO,
ACR.RECEIPT_DATE DUE_DATE,
NVL (SUM (ADR.ACCTD_AMOUNT_CR), 0)
AMOUNT_DR,
NVL (SUM (ADR.ACCTD_AMOUNT_DR), 0)
- NVL (
(SELECT SUM (ARA.AMOUNT_APPLIED)
FROM AR_RECEIVABLE_APPLICATIONS_V ARA
WHERE ARA.CASH_RECEIPT_ID =
ACR.CASH_RECEIPT_ID
AND ARA.TRX_NUMBER =
'Refund'),
0
)
AMOUNT_CR,
FU.USER_NAME,
NULL EXCISE_INVOICE_NO,
ACRH.GL_DATE GL_DATE,
GCC.CONCATENATED_SEGMENTS CODE_COMBINATION,
GCC.SEGMENT3 ACCOUNT,
NULL /*FFVL.DESCRIPTION*/
AC_DESC,
ARM.NAME NAME,
(SELECT NAME
FROM FND_DOCUMENT_SEQUENCES FDS
WHERE FDS.DOC_SEQUENCE_ID =
ACR.DOC_SEQUENCE_ID)
VOC_SEQ,
NULL TAX_INVOICE_NO
FROM AR_CASH_RECEIPTS_ALL ACR,
AR_CASH_RECEIPT_HISTORY_ALL ACRH,
AR_DISTRIBUTIONS_ALL ADR,
FND_USER FU,
AR_CUSTOMERS ARC,
GL_CODE_COMBINATIONS_KFV GCC,
FND_FLEX_VALUES_VL FFVL,
AR_RECEIPT_METHODS ARM,
HR_OPERATING_UNITS HR,
HZ_CUST_ACCOUNTS HCA
WHERE ACR.CASH_RECEIPT_ID = ACRH.CASH_RECEIPT_ID
AND FU.USER_ID = ACR.CREATED_BY
AND ARM.RECEIPT_METHOD_ID =
ACR.RECEIPT_METHOD_ID
AND ARC.CUSTOMER_ID = ACR.PAY_FROM_CUSTOMER
AND GCC.CODE_COMBINATION_ID =
ADR.CODE_COMBINATION_ID
AND ADR.SOURCE_TYPE IN
('REMITTANCE', 'CASH')
--AND ACRH.STATUS='CLEARED'
--AND ADR.SOURCE_TYPE='CASH'
AND ADR.SOURCE_ID =
ACRH.CASH_RECEIPT_HISTORY_ID
AND GCC.SEGMENT3 = FFVL.FLEX_VALUE
AND FFVL.FLEX_VALUE_SET_ID = 1014163
AND ARC.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
-- AND ACR.RECEIPT_NUMBER ='SR0600200'--'SUPP. INV.NO.-10107033'
AND TRUNC (ACRH.GL_DATE) BETWEEN P_FROM_DATE
AND P_TO_DATE
AND ARC.CUSTOMER_ID =
NVL (P_CUSTOMER_ID, ARC.CUSTOMER_ID)
--AND ACR.ORG_ID = 81
--NVL(:P_ORG_ID, ACR.ORG_ID)
--AND ARC.CUSTOMER_ID BETWEEN
-- NVL(:P_FROM_CUSTOMER_ID, ARC.CUSTOMER_ID) AND
-- NVL(:P_TO_CUSTOMER_ID, ARC.CUSTOMER_ID)
------ AND GCC.SEGMENT3 = NVL(:P_ACCOUNT, GCC.SEGMENT3)
---AND SUBSTR(HCA.CUSTOMER_CLASS_CODE, 1,2) = NVL(:P_BUSINESS_LINE, SUBSTR(HCA.CUSTOMER_CLASS_CODE, 1,2))
AND NVL (ARC.CUSTOMER_CATEGORY_CODE, '~') =
NVL (
P_CUST_TYPE,
NVL (ARC.CUSTOMER_CATEGORY_CODE,
'~')
)
AND CONFIRMED_FLAG = 'Y'
AND ACR.ORG_ID = HR.ORGANIZATION_ID
GROUP BY ACR.RECEIPT_DATE,
ACR.RECEIPT_NUMBER,
ACR.DOC_SEQUENCE_VALUE,
ACR.RECEIPT_DATE,
FU.USER_NAME,
ARC.CUSTOMER_NAME,
ARC.CUSTOMER_NUMBER,
ARC.CUSTOMER_ID,
ACR.CASH_RECEIPT_ID,
ACRH.GL_DATE,
GCC.CONCATENATED_SEGMENTS,
GCC.SEGMENT3,
FFVL.DESCRIPTION,
ACR.ORG_ID,
HR.NAME,
ACR.DOC_SEQUENCE_ID,
ARM.NAME) A
WHERE A.DUE_DATE = NVL (NULL, A.DUE_DATE)
AND AMOUNT_DR <> AMOUNT_CR
ORDER BY TRX_DATE, GL_DATE;
No comments:
Post a Comment