[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT JCA.PERIOD_NAME AS PERIOD_NAME
,GP.PERIOD_NUM AS PERIOD_NUM
FROM JA_CN_CFS_ACTIVITIES_ALL JCA
,JA_CN_CFS_ASSIGNMENTS_ALL JCCA
,RG_REPORT_AXES RRA
,RG_REPORTS RG
,GL_LEDGERS LED
,GL_PERIODS GP
,GL_PERIODS GP1
,GL_PERIODS GP2
WHERE JCA.LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID
AND JCA.LEDGER_ID = P_LEDGER_ID
AND JCA.SOURCE = NVL(P_SOURCE, JCA.SOURCE)
AND JCA.GL_DATE >= GP1.START_DATE
AND GP1.PERIOD_SET_NAME = LED.PERIOD_SET_NAME
AND GP1.PERIOD_NAME = P_START_PERIOD
AND GP1.PERIOD_TYPE = LED.ACCOUNTED_PERIOD_TYPE
AND GP2.PERIOD_SET_NAME = LED.PERIOD_SET_NAME
AND GP2.PERIOD_NAME = P_END_PERIOD
AND GP2.PERIOD_TYPE = LED.ACCOUNTED_PERIOD_TYPE
AND JCA.GL_DATE <= GP2.END_DATE
AND ((P_BSV IS NOT NULL AND JCA.BALANCING_SEGMENT = P_BSV) OR
(P_BSV IS NULL AND JCA.BALANCING_SEGMENT IN
(SELECT BAL_SEG_VALUE
FROM JA_CN_LEDGER_LE_BSV_GT
WHERE LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID
AND LEDGER_ID = P_LEDGER_ID)))
AND JCCA.DETAILED_CFS_ITEM = JCA.DETAILED_CFS_ITEM
AND JCCA.CHART_OF_ACCOUNTS_ID = P_CHART_OF_ACCOUNTS_ID
AND JCCA.AXIS_SET_ID = RRA.AXIS_SET_ID
AND JCCA.AXIS_SEQ = RRA.AXIS_SEQ
AND JCCA.AXIS_SEQ = NVL(P_ROW_ID, JCCA.AXIS_SEQ)
AND RG.ROW_SET_ID = RRA.AXIS_SET_ID
AND RG.REPORT_ID = NVL(P_REPORT_ID, RG.REPORT_ID)
AND LED.LEDGER_ID = P_LEDGER_ID
AND GP.PERIOD_SET_NAME = LED.PERIOD_SET_NAME
AND GP.PERIOD_NAME = JCA.PERIOD_NAME
ORDER BY PERIOD_NUM DESC;
SELECT DISTINCT RRA.AXIS_SEQ AS ROW_ID
,RRA.DESCRIPTION AS ROW_NAME
FROM JA_CN_CFS_ACTIVITIES_ALL JCA
,JA_CN_CFS_ASSIGNMENTS_ALL JCCA
,RG_REPORT_AXES RRA
,RG_REPORTS RG
,XLA_DISTRIBUTION_LINKS XDL
WHERE JCA.LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID
AND JCA.LEDGER_ID = P_LEDGER_ID
AND JCA.SOURCE = NVL(P_SOURCE, JCA.SOURCE)
AND JCA.PERIOD_NAME = XX_PERIOD_NAME --****
AND ((P_BSV IS NOT NULL AND JCA.BALANCING_SEGMENT = P_BSV) OR
(P_BSV IS NULL AND JCA.BALANCING_SEGMENT IN
(SELECT BAL_SEG_VALUE
FROM JA_CN_LEDGER_LE_BSV_GT
WHERE LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID
AND LEDGER_ID = P_LEDGER_ID)))
AND JCCA.DETAILED_CFS_ITEM = JCA.DETAILED_CFS_ITEM
AND JCCA.CHART_OF_ACCOUNTS_ID = P_CHART_OF_ACCOUNTS_ID
AND JCCA.AXIS_SET_ID = RRA.AXIS_SET_ID
AND JCCA.AXIS_SEQ = RRA.AXIS_SEQ
AND JCCA.AXIS_SEQ = NVL(P_ROW_ID, JCCA.AXIS_SEQ)
AND RG.ROW_SET_ID = RRA.AXIS_SET_ID
AND RG.REPORT_ID = NVL(P_REPORT_ID, RG.REPORT_ID)
AND XDL.AE_HEADER_ID = JCA.TRX_ID
AND XDL.AE_LINE_NUM = JCA.TRX_LINE_ID;
SELECT JCA.TRX_ID
,JCA.TRX_LINE_ID
,JCA.CASH_TRX_ID
,JCA.CASH_TRX_LINE_ID
FROM JA_CN_CFS_ACTIVITIES_ALL JCA
,JA_CN_CFS_ASSIGNMENTS_ALL JCCA
,RG_REPORTS RG
,XLA_TRANSACTION_ENTITIES ENT
,AP_CHECKS_ALL ACA
,PO_VENDORS PV
,XLA_AE_HEADERS AEH
,XLA_DISTRIBUTION_LINKS XDL
,AP_INVOICES_ALL API
WHERE JCA.LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID
AND JCA.LEDGER_ID = P_LEDGER_ID
AND JCA.TRANSACTION_TYPE = 'SLA'
AND JCA.SOURCE = 'SQLAP'
AND JCA.SOURCE = NVL(P_SOURCE, JCA.SOURCE)
AND JCA.PERIOD_NAME = XX_PERIOD_NAME --****
AND ((P_BSV IS NOT NULL AND JCA.BALANCING_SEGMENT = P_BSV) OR
(P_BSV IS NULL AND JCA.BALANCING_SEGMENT IN
(SELECT BAL_SEG_VALUE
FROM JA_CN_LEDGER_LE_BSV_GT
WHERE LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID
AND LEDGER_ID = P_LEDGER_ID)))
AND JCCA.DETAILED_CFS_ITEM = JCA.DETAILED_CFS_ITEM
AND JCCA.CHART_OF_ACCOUNTS_ID = P_CHART_OF_ACCOUNTS_ID
AND JCCA.AXIS_SEQ = XX_ROW_ID --****
AND RG.ROW_SET_ID = JCCA.AXIS_SET_ID
AND RG.REPORT_ID = NVL(P_REPORT_ID, RG.REPORT_ID)
AND ENT.APPLICATION_ID = 200
AND ENT.ENTITY_CODE = 'AP_PAYMENTS'
AND ACA.CHECK_ID = ENT.SOURCE_ID_INT_1
AND PV.VENDOR_ID(+) = ACA.VENDOR_ID
AND AEH.ENTITY_ID = ENT.ENTITY_ID
AND AEH.AE_HEADER_ID = JCA.TRX_ID
AND AEH.LEDGER_ID = P_LEDGER_ID
AND XDL.AE_HEADER_ID = JCA.TRX_ID
AND XDL.AE_LINE_NUM = JCA.TRX_LINE_ID
AND API.INVOICE_ID(+) = XDL.APPLIED_TO_SOURCE_ID_NUM_1
UNION ALL
SELECT JCA.TRX_ID
,JCA.TRX_LINE_ID
,JCA.CASH_TRX_ID
,JCA.CASH_TRX_LINE_ID
FROM JA_CN_CFS_ACTIVITIES_ALL JCA
,JA_CN_CFS_ASSIGNMENTS_ALL JCCA
,RG_REPORTS RG
,XLA_TRANSACTION_ENTITIES ENT
,XLA_AE_HEADERS AEH
,AR_CASH_RECEIPTS_ALL ACA
,HZ_CUST_ACCOUNTS CUST
,HZ_PARTIES HP
,XLA_DISTRIBUTION_LINKS XDL
,RA_CUSTOMER_TRX_ALL RA
WHERE JCA.LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID
AND JCA.LEDGER_ID = P_LEDGER_ID
AND JCA.TRANSACTION_TYPE = 'SLA'
AND JCA.SOURCE = 'AR'
AND JCA.FUNC_AMOUNT <> 0 -- added
AND JCA.SOURCE = NVL(P_SOURCE, JCA.SOURCE)
AND JCA.PERIOD_NAME = XX_PERIOD_NAME --****
AND ((P_BSV IS NOT NULL AND JCA.BALANCING_SEGMENT = P_BSV) OR
(P_BSV IS NULL AND JCA.BALANCING_SEGMENT IN
(SELECT BAL_SEG_VALUE
FROM JA_CN_LEDGER_LE_BSV_GT
WHERE LEGAL_ENTITY_ID = P_LEGAL_ENTITY_ID
AND LEDGER_ID = P_LEDGER_ID)))
AND JCCA.DETAILED_CFS_ITEM = JCA.DETAILED_CFS_ITEM
AND JCCA.CHART_OF_ACCOUNTS_ID = P_CHART_OF_ACCOUNTS_ID
AND JCCA.AXIS_SEQ = XX_ROW_ID --****
AND RG.ROW_SET_ID = JCCA.AXIS_SET_ID
AND RG.REPORT_ID = NVL(P_REPORT_ID, RG.REPORT_ID)
AND ENT.APPLICATION_ID = 222
AND ENT.ENTITY_CODE = 'RECEIPTS'
AND ENT.ENTITY_ID = AEH.ENTITY_ID
AND AEH.AE_HEADER_ID = JCA.TRX_ID
AND AEH.LEDGER_ID = P_LEDGER_ID
AND ACA.CASH_RECEIPT_ID = ENT.SOURCE_ID_INT_1
AND ACA.PAY_FROM_CUSTOMER = CUST.CUST_ACCOUNT_ID(+)
AND CUST.PARTY_ID = HP.PARTY_ID(+)
AND XDL.AE_HEADER_ID = JCA.TRX_ID
AND XDL.AE_LINE_NUM = JCA.TRX_LINE_ID
AND RA.CUSTOMER_TRX_ID(+) = XDL.APPLIED_TO_SOURCE_ID_NUM_1;*/
DELETE
FROM JA_CN_LEDGER_LE_BSV_GT;
SELECT GP1.PERIOD_YEAR
,GP2.PERIOD_YEAR
INTO L_START_YEAR
,L_END_YEAR
FROM GL_LEDGERS LED
,GL_PERIODS GP1
,GL_PERIODS GP2
WHERE LED.LEDGER_ID = P_LEDGER_ID
AND GP1.PERIOD_SET_NAME = LED.PERIOD_SET_NAME
AND GP1.PERIOD_NAME = P_START_PERIOD
AND GP2.PERIOD_SET_NAME = LED.PERIOD_SET_NAME
AND GP2.PERIOD_NAME = P_END_PERIOD;