[Home] [Help]
629: SUM(NVL(Ael.Entered_Dr,0) + NVL(-1 * Ael.Entered_Cr, 0)) Entered_Amount
630: FROM RA_CUSTOMER_TRX_ALL Rct,
631: RA_CUST_TRX_LINE_GL_DIST_ALL Rctlgd,
632: Xla_Transaction_Entities Ent,
633: Xla_Ae_Headers Aeh,
634: Xla_Ae_Lines Ael,
635: Gl_Import_References Gir,
636: Gl_Je_Headers Jeh
637: WHERE Rct.Customer_Trx_Id = pn_customer_trx_id -- variable: pn_customer_trx_id
669: Ael.Entered_Cr Entered_Cr
670: FROM AR_CASH_RECEIPTS_ALL Acr,
671: AR_RECEIVABLE_APPLICATIONS_ALL App,
672: Xla_Transaction_Entities Ent,
673: Xla_Ae_Headers Aeh,
674: Xla_Ae_Lines Ael,
675: Gl_Je_Headers Jeh,
676: Gl_Import_References Gir
677: WHERE Acr.Cash_Receipt_Id = App.Cash_Receipt_Id
706: AND (Ael.Ae_Header_Id,Ael.Ae_Line_Num) IN
707: (SELECT Xdl.Ae_Header_Id,
708: Xdl.Ae_Line_Num
709: FROM AR_RECEIVABLE_APPLICATIONS_ALL App,
710: Xla_Ae_Headers Aeh,
711: Xla_Distribution_Links Xdl,
712: Ar_Distributions_All Ada
713: WHERE App.event_id = Aeh.event_id
714: AND Aeh.ae_header_id = Xdl.ae_header_id
746: Ael.Entered_Cr Entered_Cr
747: FROM RA_CUSTOMER_TRX_ALL Rct,
748: RA_CUST_TRX_LINE_GL_DIST_ALL Rctlgd,
749: Xla_Transaction_Entities Ent,
750: Xla_Ae_Headers Aeh,
751: Xla_Ae_Lines Ael,
752: Gl_Import_References Gir,
753: Gl_Je_Headers Jeh
754: WHERE Rct.Previous_Customer_Trx_Id = pn_customer_trx_id -- variable: pn_customer_trx_id
780: Ael.Entered_Dr Entered_Dr,
781: Ael.Entered_Cr Entered_Cr
782: FROM AR_ADJUSTMENTS_ALL Adj,
783: Xla_Transaction_Entities Ent,
784: Xla_Ae_Headers Aeh,
785: Xla_Ae_Lines Ael,
786: Gl_Import_References Gir,
787: Gl_Je_Headers Jeh
788: WHERE Adj.Customer_Trx_Id = pn_customer_trx_id -- variable: pn_customer_trx_id
1256: CURSOR account_number_cur
1257: IS
1258: SELECT DISTINCT Account_Number FROM(
1259: SELECT Jab.Account_Segment Account_Number
1260: FROM Xla_Ae_Headers Aeh,
1261: Xla_Ae_Lines Ael,
1262: Ja_Cn_Code_Combination_v Jcc,
1263: Ja_Cn_Account_Balances Jab
1264: WHERE Jab.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
1460: FROM GL_JE_HEADERS jeh,
1461: GL_JE_LINES jel,
1462: GL_IMPORT_REFERENCES gir,
1463: XLA_AE_LINES ael,
1464: XLA_AE_HEADERS aeh,
1465: JA_CN_CODE_COMBINATION_V jcc,
1466: HZ_CUST_ACCOUNTS cust,
1467: HZ_PARTIES part,
1468: Gl_Ledgers Gl,
1697: FROM GL_JE_HEADERS jeh,
1698: GL_JE_LINES jel,
1699: GL_IMPORT_REFERENCES gir,
1700: XLA_AE_LINES ael,
1701: XLA_AE_HEADERS aeh,
1702: JA_CN_CODE_COMBINATION_V jcc,
1703: HZ_CUST_ACCOUNTS cust,
1704: HZ_PARTIES part,
1705: GL_PERIOD_STATUSES gps
1736: IS
1737: SELECT DISTINCT Rct.Customer_Trx_Id Customer_Trx_Id
1738: FROM RA_CUSTOMER_TRX_ALL Rct,
1739: Xla_Transaction_Entities Ent,
1740: Xla_Ae_Headers Aeh
1741: WHERE Ent.Application_Id = 222
1742: AND Rct.Customer_Trx_Id = Ent.Source_Id_Int_1
1743: AND Ent.Entity_Code = 'TRANSACTIONS'
1744: AND Ent.Entity_Id = Aeh.Entity_Id
1753: SELECT DISTINCT App.Applied_Customer_Trx_Id Customer_Trx_Id
1754: FROM AR_CASH_RECEIPTS_ALL Acr,
1755: AR_RECEIVABLE_APPLICATIONS_ALL App,
1756: Xla_Transaction_Entities Ent,
1757: Xla_Ae_Headers Aeh
1758: WHERE Ent.Application_Id = 222
1759: AND Acr.Cash_Receipt_Id = App.Cash_Receipt_Id
1760: AND Acr.Cash_Receipt_Id = Ent.Source_Id_Int_1
1761: AND Ent.Entity_Code = 'RECEIPTS'
1766: UNION
1767: SELECT DISTINCT Adj.Customer_Trx_Id Customer_Trx_Id
1768: FROM AR_ADJUSTMENTS_ALL Adj,
1769: Xla_Transaction_Entities Ent,
1770: Xla_Ae_Headers Aeh
1771: WHERE Ent.Application_Id = 222
1772: AND Adj.Adjustment_Id = Ent.Source_Id_Int_1
1773: AND Ent.Entity_Code = 'ADJUSTMENTS'
1774: AND Ent.Entity_Id = Aeh.Entity_Id
1777: UNION
1778: SELECT DISTINCT Rct.Previous_Customer_Trx_Id Customer_Trx_Id
1779: FROM RA_CUSTOMER_TRX_ALL Rct,
1780: Xla_Transaction_Entities Ent,
1781: Xla_Ae_Headers Aeh
1782: WHERE Ent.Application_Id = 222
1783: AND Rct.Customer_Trx_Id = Ent.Source_Id_Int_1
1784: AND Ent.Entity_Code = 'TRANSACTIONS'
1785: AND Ent.Entity_Id = Aeh.Entity_Id
1921: 'ATTRIBUTE15',Rct.ATTRIBUTE15,NULL), NULL)*/ Remittance_Bill_Number
1922: FROM RA_CUSTOMER_TRX_ALL Rct,
1923: RA_CUST_TRX_LINE_GL_DIST_ALL Rctlgd,
1924: Xla_Transaction_Entities Ent,
1925: Xla_Ae_Headers Aeh,
1926: Xla_Ae_Lines Ael,
1927: Gl_Import_References Gir,
1928: Gl_Je_Headers Jeh,
1929: Gl_Ledgers Gl,
1983: TO_CHAR(Acr.Receipt_Date,'YYYYMMDD') Payment_Date
1984: FROM AR_CASH_RECEIPTS_ALL Acr,
1985: AR_RECEIVABLE_APPLICATIONS_ALL App,
1986: Xla_Transaction_Entities Ent,
1987: Xla_Ae_Headers Aeh,
1988: Xla_Ae_Lines Ael,
1989: Gl_Je_Headers Jeh,
1990: Gl_Import_References Gir,
1991: Gl_Ledgers Gl,
2020: --NOT EXISTS means that the customer_trx_id is from Transaction,so
2021: --all the receipt applied to it need be exported.
2022: AND ((EXISTS(SELECT Aeh1.Ae_Header_Id
2023: FROM Xla_Transaction_Entities Ent1,
2024: Xla_Ae_Headers Aeh1
2025: WHERE Ent1.Entity_Code = 'RECEIPTS'
2026: AND Ent1.Entity_Id = Aeh1.Entity_Id
2027: AND Aeh1.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2028: AND Aeh1.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
2029: AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
2030: )
2031: OR NOT EXISTS(SELECT Aeh1.Ae_Header_Id
2032: FROM Xla_Transaction_Entities Ent1,
2033: Xla_Ae_Headers Aeh1
2034: WHERE Ent1.Entity_Code = 'RECEIPTS'
2035: AND Ent1.Entity_Id = Aeh1.Entity_Id
2036: AND Aeh1.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2037: AND Aeh1.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
2038: )
2039: --NOT EXISTS means that the customer_trx_id not from CreditMemo
2040: AND NOT EXISTS(SELECT Aeh2.Ae_Header_Id
2041: FROM Xla_Transaction_Entities Ent2,
2042: Xla_Ae_Headers Aeh2,
2043: RA_CUSTOMER_TRX_ALL Rct2
2044: WHERE Ent2.Application_Id = 222
2045: AND Rct2.Customer_Trx_Id = Ent2.Source_Id_Int_1
2046: AND Ent2.Entity_Code = 'TRANSACTIONS'
2050: AND Rct2.Previous_Customer_Trx_Id IS NOT NULL) --variable: ln_ae_header_id
2051: --NOT EXISTS means that the customer_trx_id not from Adjustment
2052: AND NOT EXISTS(SELECT Aeh3.Ae_Header_Id
2053: FROM Xla_Transaction_Entities Ent3,
2054: Xla_Ae_Headers Aeh3
2055: WHERE Ent3.Entity_Code = 'ADJUSTMENTS'
2056: AND Ent3.Entity_Id = Aeh3.Entity_Id
2057: AND Aeh3.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2058: AND Aeh3.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
2112: 'ATTRIBUTE15',Rct.ATTRIBUTE15,NULL), NULL)*/ Remittance_Bill_Number
2113: FROM RA_CUSTOMER_TRX_ALL Rct,
2114: RA_CUST_TRX_LINE_GL_DIST_ALL Rctlgd,
2115: Xla_Transaction_Entities Ent,
2116: Xla_Ae_Headers Aeh,
2117: Xla_Ae_Lines Ael,
2118: Gl_Import_References Gir,
2119: Gl_Je_Headers Jeh,
2120: Gl_Ledgers Gl,
2151: --NOT EXISTS means that the customer_trx_id is from Transaction,so
2152: --all the creditmemo applied to it need be exported.
2153: AND ((EXISTS(SELECT Aeh1.Ae_Header_Id
2154: FROM Xla_Transaction_Entities Ent1,
2155: Xla_Ae_Headers Aeh1,
2156: RA_CUSTOMER_TRX_ALL Rct
2157: WHERE Ent1.Application_Id = 222
2158: AND Rct.Customer_Trx_Id = Ent1.Source_Id_Int_1
2159: AND Ent1.Entity_Code = 'TRANSACTIONS'
2164: AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
2165: )
2166: OR NOT EXISTS(SELECT Aeh1.Ae_Header_Id
2167: FROM Xla_Transaction_Entities Ent1,
2168: Xla_Ae_Headers Aeh1,
2169: RA_CUSTOMER_TRX_ALL Rct
2170: WHERE Ent1.Application_Id = 222
2171: AND Rct.Customer_Trx_Id = Ent1.Source_Id_Int_1
2172: AND Ent1.Entity_Code = 'TRANSACTIONS'
2177: )
2178: --NOT EXISTS means that the customer_trx_id not from Receipt
2179: AND NOT EXISTS(SELECT Aeh2.Ae_Header_Id
2180: FROM Xla_Transaction_Entities Ent2,
2181: Xla_Ae_Headers Aeh2
2182: WHERE Ent2.Entity_Code = 'RECEIPTS'
2183: AND Ent2.Entity_Id = Aeh2.Entity_Id
2184: AND Aeh2.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2185: AND Aeh2.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
2185: AND Aeh2.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
2186: --NOT EXISTS means that the customer_trx_id not from Adjustment
2187: AND NOT EXISTS(SELECT Aeh3.Ae_Header_Id
2188: FROM Xla_Transaction_Entities Ent3,
2189: Xla_Ae_Headers Aeh3
2190: WHERE Ent3.Entity_Code = 'ADJUSTMENTS'
2191: AND Ent3.Entity_Id = Aeh3.Entity_Id
2192: AND Aeh3.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2193: AND Aeh3.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
2212: Adj.Comments Description,
2213: TO_CHAR(Adj.Gl_Date,'YYYYMMDD') Applied_Date
2214: FROM AR_ADJUSTMENTS_ALL Adj,
2215: Xla_Transaction_Entities Ent,
2216: Xla_Ae_Headers Aeh,
2217: Xla_Ae_Lines Ael,
2218: Gl_Import_References Gir,
2219: Gl_Je_Headers Jeh,
2220: Gl_Ledgers Gl,
2245: --NOT EXISTS means that the customer_trx_id is from Transaction,so
2246: --all Adjustments based on it need be exported.
2247: AND ((EXISTS(SELECT Aeh1.Ae_Header_Id
2248: FROM Xla_Transaction_Entities Ent1,
2249: Xla_Ae_Headers Aeh1
2250: WHERE Ent1.Entity_Code = 'ADJUSTMENTS'
2251: AND Ent1.Entity_Id = Aeh1.Entity_Id
2252: AND Aeh1.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2253: AND Aeh1.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
2254: AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
2255: )
2256: OR NOT EXISTS(SELECT Aeh1.Ae_Header_Id
2257: FROM Xla_Transaction_Entities Ent1,
2258: Xla_Ae_Headers Aeh1
2259: WHERE Ent1.Entity_Code = 'ADJUSTMENTS'
2260: AND Ent1.Entity_Id = Aeh1.Entity_Id
2261: AND Aeh1.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2262: AND Aeh1.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
2263: )
2264: --NOT EXISTS means that the customer_trx_id not from CreditMemo
2265: AND NOT EXISTS(SELECT Aeh2.Ae_Header_Id
2266: FROM Xla_Transaction_Entities Ent2,
2267: Xla_Ae_Headers Aeh2,
2268: RA_CUSTOMER_TRX_ALL Rct2
2269: WHERE Ent2.Application_Id = 222
2270: AND Rct2.Customer_Trx_Id = Ent2.Source_Id_Int_1
2271: AND Ent2.Entity_Code = 'TRANSACTIONS'
2275: AND Rct2.Previous_Customer_Trx_Id IS NOT NULL) --variable: ln_ae_header_id
2276: --NOT EXISTS means that the customer_trx_id not from Receipt
2277: AND NOT EXISTS(SELECT Aeh3.Ae_Header_Id
2278: FROM Xla_Transaction_Entities Ent3,
2279: Xla_Ae_Headers Aeh3
2280: WHERE Ent3.Entity_Code = 'RECEIPTS'
2281: AND Ent3.Entity_Id = Aeh3.Entity_Id
2282: AND Aeh3.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
2283: AND Aeh3.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
2308: IS
2309: SELECT SUM(NVL(Ael.Accounted_Dr,0) + NVL(-1 * Ael.Accounted_Cr,0)) Functional_Amount,
2310: SUM(NVL(Ael.Entered_Dr,0) + NVL(-1 * Ael.Entered_Cr, 0)) Entered_Amount
2311: FROM Xla_Ae_Lines Ael,
2312: Xla_Ae_Headers Aeh
2313: WHERE Ael.Ae_Header_Id = Aeh.Ae_Header_Id
2314: AND Ael.Accounting_Class_Code = 'RECEIVABLE'
2315: AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
2316: --BSV condition, one transaction having BSVs with different legal entity in distribution lines????
2334: AND (Ael.Ae_Header_Id,Ael.Ae_Line_Num) IN
2335: (SELECT Xdl.Ae_Header_Id,
2336: Xdl.Ae_Line_Num
2337: FROM AR_RECEIVABLE_APPLICATIONS_ALL App,
2338: Xla_Ae_Headers Aeh,
2339: Xla_Distribution_Links Xdl,
2340: Ar_Distributions_All Ada
2341: WHERE App.event_id = Aeh.event_id
2342: AND Aeh.ae_header_id = Xdl.ae_header_id
2362: IS
2363: SELECT SUM(NVL(Ael.Accounted_Dr,0) + NVL(-1 * Ael.Accounted_Cr,0)) Functional_Amount,
2364: SUM(NVL(Ael.Entered_Dr,0) + NVL(-1 * Ael.Entered_Cr, 0)) Entered_Amount
2365: FROM Xla_Ae_Lines Ael,
2366: Xla_Ae_Headers Aeh
2367: WHERE Ael.Ae_Header_Id = Aeh.Ae_Header_Id
2368: AND Ael.Accounting_Class_Code = 'RECEIVABLE'
2369: AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
2370: AND Ael.Ae_Line_Num = ln_ae_line_num