eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PA_AEL_GL_DR_TMP_V

Source

View Text - Preformatted


SELECT 275 , imp.set_of_books_id , dr.org_id , lclass.meaning , lclass.lookup_code , Null , Null , to_char(dr.draft_revenue_num) , dr.draft_revenue_num , ev.completion_date , ev.description , 'DR' , to_number(NULL) , ltype.meaning , ltype.lookup_code , erdl.code_combination_id , pr.project_currency_code , decode(sign(erdl.amount), -1,-1*erdl.amount, to_number(NULL)) , decode(sign(erdl.amount), -1,to_number(NULL), erdl.amount) , decode(sign(erdl.amount), -1,-1*erdl.amount, to_number(NULL)) , decode(sign(erdl.amount), -1,to_number(NULL), erdl.amount) , to_date(NULL) , NULL , to_number(NULL) , NULL , NULL , NULL , NULL , NULL , dr.gl_date , NULL , 'DR' , to_number(NULL) , 'ERDL' , to_number(NULL) , ltype.meaning||','||ltran.meaning|| to_char(dr.draft_revenue_num)|| ','||lline.meaning|| to_char(erdl.line_num) , ltype.meaning||','||ltran.meaning|| to_char(dr.draft_revenue_num)|| ','||lline.meaning|| to_char(erdl.line_num) , erdl.creation_date , erdl.created_by , erdl.request_id , erdl.program_application_id , erdl.program_id , erdl.program_update_date , pr.segment1 , dr.project_id , pr.name , pt.task_number , pt.task_name , org1.name , NULL , NULL , ev.event_type , ev.event_num , ev.revenue_amount , NULL , dr.pa_date , dr.transferred_date , cust_acct.cust_account_id , substrb(party.party_name,1,50) , agr.agreement_num , dr.accrue_through_date , erdl.draft_revenue_item_line_num , ev.event_id , erdl.line_num , lsys.meaning , lsys.lookup_code , lstat.meaning , lstat.lookup_code , lfrto.meaning , decode(dr.transfer_status_code, 'X',dr.transfer_rejection_reason, 'R',dr.transfer_rejection_reason, NULL) , lyesno.lookup_code , lyesno.meaning , llbr.lookup_code , jel.je_header_id , jel.je_line_num , NULL , NULL , NULL , NULL , NULL , NULL , cust_acct.account_number , NULL from pa_implementations_all imp, hr_all_organization_units_tl org1, gl_je_lines jel, fnd_lookup_values lsys, fnd_lookup_values lstat, fnd_lookup_values lfrto, fnd_lookup_values llbr, fnd_lookup_values lyesno, fnd_lookup_values ltype, fnd_lookup_values lline, fnd_lookup_values ltran, fnd_lookup_values lclass, pa_events ev, pa_projects_all pr, pa_tasks pt, pa_cust_event_rdl_all erdl, hz_parties party, hz_cust_accounts cust_acct, pa_agreements_all agr, pa_draft_revenues_all dr WHERE 'DR' = ( select nvl(xla_util.get_query_context,'DR') from dual ) and dr.project_id = pr.project_id and pa_security.allow_query(pr.project_id) = 'Y' and erdl.batch_name = jel.reference_1 and erdl.code_combination_id = jel.code_combination_id and jel.reference_3 = 'Revenue - Event Revenue' and dr.project_id = erdl.project_id and dr.org_id = imp.org_id and dr.draft_revenue_num = erdl.draft_revenue_num and erdl.event_num = ev.event_num and erdl.project_id = ev.project_id and nvl(erdl.task_id,-1) =nvl(ev.task_id,-1) and pt.task_id(+) = nvl(ev.task_id,-1) and pt.project_id(+) = ev.project_id and agr.customer_id = cust_acct.cust_account_id and cust_acct.party_id = party.party_id and agr.agreement_id = dr.agreement_id and org1.organization_id(+) = ev.organization_id and org1.language (+) = USERENV('LANG') and lyesno.view_application_id(+) =0 and lyesno.language (+) = USERENV('LANG') and lyesno.lookup_type(+) = 'YES_NO' and lyesno.lookup_code(+) = decode(dr.transfer_status_code, 'P','N', 'Y') and lsys.lookup_type = 'PA_XLA_TRANSFER_SYSTEM' and lsys.lookup_code = 'GL' and lsys.language = USERENV('LANG') and lsys.view_application_id =275 and llbr.lookup_type = 'YES_NO' and llbr.lookup_code = 'Y' and llbr.language = USERENV('LANG') and llbr.view_application_id =0 and lfrto.lookup_type = 'PA_XLA_TRANSFER_FROM_TO' and lfrto.lookup_code = 'TO' and lfrto.view_application_id =275 and lfrto.language= USERENV('LANG') and lstat.view_application_id(+) =275 and lstat.language(+) = USERENV('LANG') and lstat.lookup_type(+) = 'PA_XLA_TRANSFER_STATUS' and lstat.lookup_code(+) = decode(dr.transfer_status_code, 'P', 'P', 'A', 'A', 'X', 'R', 'V', 'T', 'T', 'T', 'R', 'R', 'G', 'G', NULL) and lclass.lookup_type = 'PA_XLA_TXN_CLASS' and lclass.lookup_code = 'REVENUE' and lclass.language = USERENV('LANG') and lclass.view_application_id =275 and ltype.lookup_type = 'PA_XLA_ACCT_LINE_TYPE' and ltype.lookup_code = 'EV-REV' and ltype.language = USERENV('LANG') and ltype.view_application_id =275 and lline.lookup_type = 'PA_XLA_LINE_REF' and lline.lookup_code = 'LINE' and lline.language = USERENV('LANG') and lline.view_application_id =275 and ltran.lookup_type = 'PA_XLA_LINE_REF' and ltran.lookup_code = 'TRANS_ID' and ltran.language = USERENV('LANG') and ltran.view_application_id =275 UNION select 275 , imp.set_of_books_id , dr.org_id , lclass.meaning , lclass.lookup_code , Null , Null , to_char(dr.draft_revenue_num) , dr.draft_revenue_num , ev.completion_date , ev.description , 'DR' , to_number(NULL) , ltype.meaning , ltype.lookup_code , erdl.code_combination_id , pr.project_currency_code , decode(sign(erdl.amount), -1,-1*erdl.amount, to_number(NULL)) , decode(sign(erdl.amount), -1,to_number(NULL), erdl.amount) , decode(sign(erdl.amount), -1,-1*erdl.amount, to_number(NULL)) , decode(sign(erdl.amount), -1,to_number(NULL), erdl.amount) , to_date(NULL) , NULL , to_number(NULL) , NULL , NULL , NULL , NULL , NULL , dr.gl_date , NULL , 'DR' , to_number(NULL) , 'ERDL' , to_number(NULL) , ltype.meaning||','||ltran.meaning|| to_char(dr.draft_revenue_num)|| ','||lline.meaning|| to_char(erdl.line_num) , ltype.meaning||','||ltran.meaning|| to_char(dr.draft_revenue_num)|| ','||lline.meaning|| to_char(erdl.line_num) , erdl.creation_date , erdl.created_by , erdl.request_id , erdl.program_application_id , erdl.program_id , erdl.program_update_date , pr.segment1 , dr.project_id , pr.name , pt.task_number , pt.task_name , org1.name , NULL , NULL , ev.event_type , ev.event_num , ev.revenue_amount , NULL , dr.pa_date , dr.transferred_date , cust_acct.cust_account_id , substrb(party.party_name,1,50) , agr.agreement_num , dr.accrue_through_date , erdl.draft_revenue_item_line_num , ev.event_id , erdl.line_num , lsys.meaning , lsys.lookup_code , lstat.meaning , lstat.lookup_code , lfrto.meaning , decode(dr.transfer_status_code, 'X',dr.transfer_rejection_reason, 'R',dr.transfer_rejection_reason, NULL) , lyesno.lookup_code , lyesno.meaning , llbr.lookup_code , impref.je_header_id , impref.je_line_num , NULL , NULL , NULL , NULL , NULL , NULL , cust_acct.account_number , NULL from pa_implementations_all imp, hr_all_organization_units_tl org1, gl_import_references impref, fnd_lookup_values lsys, fnd_lookup_values lstat, fnd_lookup_values lfrto, fnd_lookup_values llbr, fnd_lookup_values lyesno, fnd_lookup_values ltype, fnd_lookup_values lline, fnd_lookup_values ltran, fnd_lookup_values lclass, pa_events ev, pa_projects_all pr, pa_tasks pt, pa_cust_event_rdl_all erdl, hz_parties party, hz_cust_accounts cust_acct, pa_agreements_all agr, pa_draft_revenues_all dr where 'DR' = ( select nvl(xla_util.get_query_context,'DR') from dual ) and dr.project_id = pr.project_id and pa_security.allow_query(pr.project_id) = 'Y' and erdl.batch_name = impref.reference_1 and erdl.code_combination_id = impref.reference_2 and impref.reference_3 = 'Revenue - Event Revenue' and dr.project_id = erdl.project_id and dr.org_id = imp.org_id and dr.draft_revenue_num = erdl.draft_revenue_num and erdl.event_num = ev.event_num and erdl.project_id = ev.project_id and nvl(erdl.task_id,-1) =nvl(ev.task_id,-1) and pt.task_id(+) = nvl(ev.task_id,-1) and pt.project_id(+) = ev.project_id and agr.customer_id = cust_acct.cust_account_id and cust_acct.party_id = party.party_id and agr.agreement_id = dr.agreement_id and org1.organization_id(+) = ev.organization_id and org1.language (+) = USERENV('LANG') and lyesno.view_application_id(+) =0 and lyesno.language (+) = USERENV('LANG') and lyesno.lookup_type(+) = 'YES_NO' and lyesno.lookup_code(+) = decode(dr.transfer_status_code, 'P','N', 'Y') and lsys.lookup_type = 'PA_XLA_TRANSFER_SYSTEM' and lsys.lookup_code = 'GL' and lsys.language = USERENV('LANG') and lsys.view_application_id =275 and llbr.lookup_type = 'YES_NO' and llbr.lookup_code = 'Y' and llbr.language = USERENV('LANG') and llbr.view_application_id =0 and lfrto.lookup_type = 'PA_XLA_TRANSFER_FROM_TO' and lfrto.lookup_code = 'TO' and lfrto.view_application_id =275 and lfrto.language= USERENV('LANG') and lstat.view_application_id(+) =275 and lstat.language(+) = USERENV('LANG') and lstat.lookup_type(+) = 'PA_XLA_TRANSFER_STATUS' and lstat.lookup_code(+) = decode(dr.transfer_status_code, 'P', 'P', 'A', 'A', 'X', 'R', 'V', 'T', 'T', 'T', 'R', 'R', 'G', 'G', NULL) and lclass.lookup_type = 'PA_XLA_TXN_CLASS' and lclass.lookup_code = 'REVENUE' and lclass.language = USERENV('LANG') and lclass.view_application_id =275 and ltype.lookup_type = 'PA_XLA_ACCT_LINE_TYPE' and ltype.lookup_code = 'EV-REV' and ltype.language = USERENV('LANG') and ltype.view_application_id =275 and lline.lookup_type = 'PA_XLA_LINE_REF' and lline.lookup_code = 'LINE' and lline.language = USERENV('LANG') and lline.view_application_id =275 and ltran.lookup_type = 'PA_XLA_LINE_REF' and ltran.lookup_code = 'TRANS_ID' and ltran.language = USERENV('LANG') and ltran.view_application_id =275 UNION select 275 , imp.set_of_books_id , dr.org_id , lclass.meaning , lclass.lookup_code , Null , Null , to_char(dr.draft_revenue_num) , dr.draft_revenue_num , to_date(NULL) , NULL , 'DR' , to_number(NULL) , ltype.meaning , ltype.lookup_code , dr.realized_gains_ccid , pr.project_currency_code , decode(dr.realized_gains_amount, to_number(NULL),to_number(NULL), decode(sign(dr.realized_gains_amount), -1,-1*dr.realized_gains_amount, to_number(NULL) )) , decode(dr.realized_gains_amount, to_number(NULL), to_number(NULL), decode(sign(dr.realized_gains_amount), -1,to_number(NULL), dr.realized_gains_amount)) , decode(dr.realized_gains_amount, to_number(NULL),to_number(NULL), decode(sign(dr.realized_gains_amount), -1,-1*dr.realized_gains_amount, to_number(NULL) )) , decode(dr.realized_gains_amount, to_number(NULL), to_number(NULL), decode(sign(dr.realized_gains_amount), -1,to_number(NULL), dr.realized_gains_amount)) , to_date(NULL) , NULL , to_number(NULL) , NULL , NULL , NULL , NULL , NULL , dr.gl_date , NULL , 'DR' , to_number(NULL) , NULL , to_number(NULL) , NULL , NULL , dr.creation_date , dr.created_by , dr.request_id , dr.program_application_id , dr.program_id , dr.program_update_date , pr.segment1 , dr.project_id , pr.name , NULL , NULL , NULL , NULL , NULL , NULL , to_number(NULL) , to_number(NULL) , NULL , dr.pa_date , dr.transferred_date , cust_acct.cust_account_id , substrb(party.party_name,1,50) , agr.agreement_num , dr.accrue_through_date , to_number(NULL) , to_number(NULL) , to_number(NULL) , lsys.meaning , lsys.lookup_code , lstat.meaning , lstat.lookup_code , lfrto.meaning , decode(dr.transfer_status_code, 'X',dr.transfer_rejection_reason, 'R',dr.transfer_rejection_reason, NULL) , lyesno.lookup_code , lyesno.meaning , llbr.lookup_code , jel.je_header_id , jel.je_line_num , NULL , NULL , NULL , NULL , NULL , NULL , cust_acct.account_number , NULL from pa_implementations_all imp, pa_projects_all pr, fnd_lookup_values lsys, fnd_lookup_values lstat, gl_je_lines jel, fnd_lookup_values lfrto, fnd_lookup_values llbr, fnd_lookup_values lyesno, fnd_lookup_values ltype, fnd_lookup_values lclass, hz_parties party, hz_cust_accounts cust_acct, pa_agreements_all agr, pa_draft_revenues_all dr where 'DR' = ( select nvl(xla_util.get_query_context,'DR') from dual ) and dr.project_id = pr.project_id and pa_security.allow_query(pr.project_id) = 'Y' and dr.transfer_status_code <> 'P' and dr.unearned_batch_name = jel.reference_1 and dr.unearned_code_combination_id = jel.code_combination_id and jel.reference_3 = 'Revenue - Realized Gains' and agr.customer_id = cust_acct.cust_account_id and cust_acct.party_id = party.party_id and imp.org_id = dr.org_id and agr.agreement_id = dr.agreement_id and lyesno.view_application_id(+) =0 and lyesno.language(+) = USERENV('LANG') and lyesno.lookup_type(+) = 'YES_NO' and lyesno.lookup_code(+) = decode(dr.transfer_status_code, 'P','N', 'Y') and lsys.lookup_type = 'PA_XLA_TRANSFER_SYSTEM' and lsys.lookup_code = 'GL' and lsys.view_application_id =275 and lsys.language = USERENV('LANG') and llbr.lookup_type = 'YES_NO' and llbr.lookup_code = 'Y' and llbr.view_application_id =0 and llbr.language = USERENV('LANG') and lfrto.lookup_type = 'PA_XLA_TRANSFER_FROM_TO' and lfrto.lookup_code = 'TO' and lfrto.view_application_id =275 and lfrto.language = USERENV('LANG') and lstat.lookup_type = 'PA_XLA_TRANSFER_STATUS' and lstat.lookup_code = 'T' and lstat.language = USERENV('LANG') and lstat.view_application_id =275 and lclass.lookup_type = 'PA_XLA_TXN_CLASS' and lclass.lookup_code = 'REVENUE' and lclass.view_application_id =275 and lclass.language = USERENV('LANG') and ltype.lookup_type = 'PA_XLA_ACCT_LINE_TYPE' and ltype.lookup_code = 'RLZD-GAINS' and ltype.language = USERENV('LANG') UNION select 275 , imp.set_of_books_id , dr.org_id , lclass.meaning , lclass.lookup_code , Null , Null , to_char(dr.draft_revenue_num) , dr.draft_revenue_num , to_date(NULL) , NULL , 'DR' , to_number(NULL) , ltype.meaning , ltype.lookup_code , dr.realized_gains_ccid , pr.project_currency_code , decode(dr.realized_gains_amount, to_number(NULL),to_number(NULL), decode(sign(dr.realized_gains_amount), -1,-1*dr.realized_gains_amount, to_number(NULL) )) , decode(dr.realized_gains_amount, to_number(NULL), to_number(NULL), decode(sign(dr.realized_gains_amount), -1,to_number(NULL), dr.realized_gains_amount)) , decode(dr.realized_gains_amount, to_number(NULL),to_number(NULL), decode(sign(dr.realized_gains_amount), -1,-1*dr.realized_gains_amount, to_number(NULL) )) , decode(dr.realized_gains_amount, to_number(NULL), to_number(NULL), decode(sign(dr.realized_gains_amount), -1,to_number(NULL), dr.realized_gains_amount)) , to_date(NULL) , NULL , to_number(NULL) , NULL , NULL , NULL , NULL , NULL , dr.gl_date , NULL , 'DR' , to_number(NULL) , NULL , to_number(NULL) , NULL , NULL , dr.creation_date , dr.created_by , dr.request_id , dr.program_application_id , dr.program_id , dr.program_update_date , pr.segment1 , dr.project_id , pr.name , NULL , NULL , NULL , NULL , NULL , NULL , to_number(NULL) , to_number(NULL) , NULL , dr.pa_date , dr.transferred_date , cust_acct.cust_account_id , substrb(party.party_name,1,50) , agr.agreement_num , dr.accrue_through_date , to_number(NULL) , to_number(NULL) , to_number(NULL) , lsys.meaning , lsys.lookup_code , lstat.meaning , lstat.lookup_code , lfrto.meaning , decode(dr.transfer_status_code, 'X',dr.transfer_rejection_reason, 'R',dr.transfer_rejection_reason, NULL) , lyesno.lookup_code , lyesno.meaning , llbr.lookup_code , impref.je_header_id , impref.je_line_num , NULL , NULL , NULL , NULL , NULL , NULL , cust_acct.account_number , NULL from pa_implementations_all imp, pa_projects_all pr, fnd_lookup_values lsys, fnd_lookup_values lstat, gl_import_references impref, fnd_lookup_values lfrto, fnd_lookup_values llbr, fnd_lookup_values lyesno, fnd_lookup_values ltype, fnd_lookup_values lclass, hz_parties party, hz_cust_accounts cust_acct, pa_agreements_all agr, pa_draft_revenues_all dr where 'DR' = ( select nvl(xla_util.get_query_context,'DR') from dual ) and dr.project_id = pr.project_id and pa_security.allow_query(pr.project_id) = 'Y' and dr.transfer_status_code <> 'P' and dr.unearned_batch_name = impref.reference_1 and dr.unearned_code_combination_id = impref.reference_2 and impref.reference_3 = 'Revenue - Realized Gains' and agr.customer_id = cust_acct.cust_account_id and cust_acct.party_id = party.party_id and imp.org_id = dr.org_id and agr.agreement_id = dr.agreement_id and lyesno.view_application_id(+) =0 and lyesno.language(+) = USERENV('LANG') and lyesno.lookup_type(+) = 'YES_NO' and lyesno.lookup_code(+) = decode(dr.transfer_status_code, 'P','N', 'Y') and lsys.lookup_type = 'PA_XLA_TRANSFER_SYSTEM' and lsys.lookup_code = 'GL' and lsys.view_application_id =275 and lsys.language = USERENV('LANG') and llbr.lookup_type = 'YES_NO' and llbr.lookup_code = 'Y' and llbr.view_application_id =0 and llbr.language = USERENV('LANG') and lfrto.lookup_type = 'PA_XLA_TRANSFER_FROM_TO' and lfrto.lookup_code = 'TO' and lfrto.view_application_id =275 and lfrto.language = USERENV('LANG') and lstat.lookup_type = 'PA_XLA_TRANSFER_STATUS' and lstat.lookup_code = 'T' and lstat.language = USERENV('LANG') and lstat.view_application_id =275 and lclass.lookup_type = 'PA_XLA_TXN_CLASS' and lclass.lookup_code = 'REVENUE' and lclass.view_application_id =275 and lclass.language = USERENV('LANG') and ltype.lookup_type = 'PA_XLA_ACCT_LINE_TYPE' and ltype.lookup_code = 'RLZD-GAINS' and ltype.language = USERENV('LANG') UNION select 275 , imp.set_of_books_id , dr.org_id , lclass.meaning , lclass.lookup_code , Null , Null , to_char(dr.draft_revenue_num) , dr.draft_revenue_num , to_date(NULL) , NULL , 'DR' , to_number(NULL) , ltype.meaning , ltype.lookup_code , dr.realized_losses_ccid , pr.project_currency_code , decode(dr.realized_losses_amount, to_number(NULL),to_number(NULL), decode(sign(dr.realized_losses_amount), -1,-1*dr.realized_losses_amount, to_number(NULL) )) , decode(dr.realized_losses_amount, to_number(NULL), to_number(NULL), decode(sign(dr.realized_losses_amount), -1,to_number(NULL), dr.realized_losses_amount)) , decode(dr.realized_losses_amount, to_number(NULL),to_number(NULL), decode(sign(dr.realized_losses_amount), -1,-1*dr.realized_losses_amount, to_number(NULL) )) , decode(dr.realized_losses_amount, to_number(NULL), to_number(NULL), decode(sign(dr.realized_losses_amount), -1,to_number(NULL), dr.realized_losses_amount)) , to_date(NULL) , NULL , to_number(NULL) , NULL , NULL , NULL , NULL , NULL , dr.gl_date , NULL , 'DR' , to_number(NULL) , NULL , to_number(NULL) , NULL , NULL , dr.creation_date , dr.created_by , dr.request_id , dr.program_application_id , dr.program_id , dr.program_update_date , pr.segment1 , dr.project_id , pr.name , NULL , NULL , NULL , NULL , NULL , NULL , to_number(NULL) , to_number(NULL) , NULL , dr.pa_date , dr.transferred_date , cust_acct.cust_account_id , substrb(party.party_name,1,50) , agr.agreement_num , dr.accrue_through_date , to_number(NULL) , to_number(NULL) , to_number(NULL) , lsys.meaning , lsys.lookup_code , lstat.meaning , lstat.lookup_code , lfrto.meaning , decode(dr.transfer_status_code, 'X',dr.transfer_rejection_reason, 'R',dr.transfer_rejection_reason, NULL) , lyesno.lookup_code , lyesno.meaning , llbr.lookup_code , jel.je_header_id , jel.je_line_num , NULL , NULL , NULL , NULL , NULL , NULL , cust_acct.account_number , NULL from pa_implementations_all imp, pa_projects_all pr, fnd_lookup_values lsys, fnd_lookup_values lstat, gl_je_lines jel, fnd_lookup_values lfrto, fnd_lookup_values llbr, fnd_lookup_values lyesno, fnd_lookup_values ltype, fnd_lookup_values lclass, hz_parties party, hz_cust_accounts cust_acct, pa_agreements_all agr, pa_draft_revenues_all dr where 'DR' = ( select nvl(xla_util.get_query_context,'DR') from dual ) and dr.project_id = pr.project_id and pa_security.allow_query(pr.project_id) = 'Y' and dr.transfer_status_code <> 'P' and dr.unearned_batch_name = jel.reference_1 and dr.unearned_code_combination_id = jel.code_combination_id and jel.reference_3 = 'Revenue - Realized Losses' and agr.customer_id = cust_acct.cust_account_id and cust_acct.party_id = party.party_id and imp.org_id = dr.org_id and agr.agreement_id = dr.agreement_id and lyesno.view_application_id(+) =0 and lyesno.language(+) = USERENV('LANG') and lyesno.lookup_type(+) = 'YES_NO' and lyesno.lookup_code(+) = decode(dr.transfer_status_code, 'P','N', 'Y') and lsys.lookup_type = 'PA_XLA_TRANSFER_SYSTEM' and lsys.lookup_code = 'GL' and lsys.view_application_id =275 and lsys.language = USERENV('LANG') and llbr.lookup_type = 'YES_NO' and llbr.lookup_code = 'Y' and llbr.view_application_id =0 and llbr.language = USERENV('LANG') and lfrto.lookup_type = 'PA_XLA_TRANSFER_FROM_TO' and lfrto.lookup_code = 'TO' and lfrto.view_application_id =275 and lfrto.language = USERENV('LANG') and lstat.lookup_type = 'PA_XLA_TRANSFER_STATUS' and lstat.lookup_code = 'T' and lstat.language = USERENV('LANG') and lstat.view_application_id =275 and lclass.lookup_type = 'PA_XLA_TXN_CLASS' and lclass.lookup_code = 'REVENUE' and lclass.view_application_id =275 and lclass.language = USERENV('LANG') and ltype.lookup_type = 'PA_XLA_ACCT_LINE_TYPE' and ltype.lookup_code = 'RLZD-LOSSES' and ltype.language = USERENV('LANG') UNION select 275 , imp.set_of_books_id , dr.org_id , lclass.meaning , lclass.lookup_code , Null , Null , to_char(dr.draft_revenue_num) , dr.draft_revenue_num , to_date(NULL) , NULL , 'DR' , to_number(NULL) , ltype.meaning , ltype.lookup_code , dr.realized_losses_ccid , pr.project_currency_code , decode(dr.realized_losses_amount, to_number(NULL),to_number(NULL), decode(sign(dr.realized_losses_amount), -1,-1*dr.realized_losses_amount, to_number(NULL) )) , decode(dr.realized_losses_amount, to_number(NULL), to_number(NULL), decode(sign(dr.realized_losses_amount), -1,to_number(NULL), dr.realized_losses_amount)) , decode(dr.realized_losses_amount, to_number(NULL),to_number(NULL), decode(sign(dr.realized_losses_amount), -1,-1*dr.realized_losses_amount, to_number(NULL) )) , decode(dr.realized_losses_amount, to_number(NULL), to_number(NULL), decode(sign(dr.realized_losses_amount), -1,to_number(NULL), dr.realized_losses_amount)) , to_date(NULL) , NULL , to_number(NULL) , NULL , NULL , NULL , NULL , NULL , dr.gl_date , NULL , 'DR' , to_number(NULL) , NULL , to_number(NULL) , NULL , NULL , dr.creation_date , dr.created_by , dr.request_id , dr.program_application_id , dr.program_id , dr.program_update_date , pr.segment1 , dr.project_id , pr.name , NULL , NULL , NULL , NULL , NULL , NULL , to_number(NULL) , to_number(NULL) , NULL , dr.pa_date , dr.transferred_date , cust_acct.cust_account_id , substrb(party.party_name,1,50) , agr.agreement_num , dr.accrue_through_date , to_number(NULL) , to_number(NULL) , to_number(NULL) , lsys.meaning , lsys.lookup_code , lstat.meaning , lstat.lookup_code , lfrto.meaning , decode(dr.transfer_status_code, 'X',dr.transfer_rejection_reason, 'R',dr.transfer_rejection_reason, NULL) , lyesno.lookup_code , lyesno.meaning , llbr.lookup_code , impref.je_header_id , impref.je_line_num , NULL , NULL , NULL , NULL , NULL , NULL , cust_acct.account_number , NULL from pa_implementations_all imp, pa_projects_all pr, fnd_lookup_values lsys, fnd_lookup_values lstat, gl_import_references impref, fnd_lookup_values lfrto, fnd_lookup_values llbr, fnd_lookup_values lyesno, fnd_lookup_values ltype, fnd_lookup_values lclass, hz_parties party, hz_cust_accounts cust_acct, pa_agreements_all agr, pa_draft_revenues_all dr where 'DR' = ( select nvl(xla_util.get_query_context,'DR') from dual ) and dr.project_id = pr.project_id and pa_security.allow_query(pr.project_id) = 'Y' and dr.transfer_status_code <> 'P' and dr.unearned_batch_name = impref.reference_1 and dr.unearned_code_combination_id = impref.reference_2 and impref.reference_3 = 'Revenue - Realized Losses' and agr.customer_id = cust_acct.cust_account_id and cust_acct.party_id = party.party_id and imp.org_id = dr.org_id and agr.agreement_id = dr.agreement_id and lyesno.view_application_id(+) =0 and lyesno.language(+) = USERENV('LANG') and lyesno.lookup_type(+) = 'YES_NO' and lyesno.lookup_code(+) = decode(dr.transfer_status_code, 'P','N', 'Y') and lsys.lookup_type = 'PA_XLA_TRANSFER_SYSTEM' and lsys.lookup_code = 'GL' and lsys.view_application_id =275 and lsys.language = USERENV('LANG') and llbr.lookup_type = 'YES_NO' and llbr.lookup_code = 'Y' and llbr.view_application_id =0 and llbr.language = USERENV('LANG') and lfrto.lookup_type = 'PA_XLA_TRANSFER_FROM_TO' and lfrto.lookup_code = 'TO' and lfrto.view_application_id =275 and lfrto.language = USERENV('LANG') and lstat.lookup_type = 'PA_XLA_TRANSFER_STATUS' and lstat.lookup_code = 'T' and lstat.language = USERENV('LANG') and lstat.view_application_id =275 and lclass.lookup_type = 'PA_XLA_TXN_CLASS' and lclass.lookup_code = 'REVENUE' and lclass.view_application_id =275 and lclass.language = USERENV('LANG') and ltype.lookup_type = 'PA_XLA_ACCT_LINE_TYPE' and ltype.lookup_code = 'RLZD-LOSSES' and ltype.language = USERENV('LANG')

View Text - HTML Formatted

SELECT 275
, IMP.SET_OF_BOOKS_ID
, DR.ORG_ID
, LCLASS.MEANING
, LCLASS.LOOKUP_CODE
, NULL
, NULL
, TO_CHAR(DR.DRAFT_REVENUE_NUM)
, DR.DRAFT_REVENUE_NUM
, EV.COMPLETION_DATE
, EV.DESCRIPTION
, 'DR'
, TO_NUMBER(NULL)
, LTYPE.MEANING
, LTYPE.LOOKUP_CODE
, ERDL.CODE_COMBINATION_ID
, PR.PROJECT_CURRENCY_CODE
, DECODE(SIGN(ERDL.AMOUNT)
, -1
, -1*ERDL.AMOUNT
, TO_NUMBER(NULL))
, DECODE(SIGN(ERDL.AMOUNT)
, -1
, TO_NUMBER(NULL)
, ERDL.AMOUNT)
, DECODE(SIGN(ERDL.AMOUNT)
, -1
, -1*ERDL.AMOUNT
, TO_NUMBER(NULL))
, DECODE(SIGN(ERDL.AMOUNT)
, -1
, TO_NUMBER(NULL)
, ERDL.AMOUNT)
, TO_DATE(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, DR.GL_DATE
, NULL
, 'DR'
, TO_NUMBER(NULL)
, 'ERDL'
, TO_NUMBER(NULL)
, LTYPE.MEANING||'
, '||LTRAN.MEANING|| TO_CHAR(DR.DRAFT_REVENUE_NUM)|| '
, '||LLINE.MEANING|| TO_CHAR(ERDL.LINE_NUM)
, LTYPE.MEANING||'
, '||LTRAN.MEANING|| TO_CHAR(DR.DRAFT_REVENUE_NUM)|| '
, '||LLINE.MEANING|| TO_CHAR(ERDL.LINE_NUM)
, ERDL.CREATION_DATE
, ERDL.CREATED_BY
, ERDL.REQUEST_ID
, ERDL.PROGRAM_APPLICATION_ID
, ERDL.PROGRAM_ID
, ERDL.PROGRAM_UPDATE_DATE
, PR.SEGMENT1
, DR.PROJECT_ID
, PR.NAME
, PT.TASK_NUMBER
, PT.TASK_NAME
, ORG1.NAME
, NULL
, NULL
, EV.EVENT_TYPE
, EV.EVENT_NUM
, EV.REVENUE_AMOUNT
, NULL
, DR.PA_DATE
, DR.TRANSFERRED_DATE
, CUST_ACCT.CUST_ACCOUNT_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, AGR.AGREEMENT_NUM
, DR.ACCRUE_THROUGH_DATE
, ERDL.DRAFT_REVENUE_ITEM_LINE_NUM
, EV.EVENT_ID
, ERDL.LINE_NUM
, LSYS.MEANING
, LSYS.LOOKUP_CODE
, LSTAT.MEANING
, LSTAT.LOOKUP_CODE
, LFRTO.MEANING
, DECODE(DR.TRANSFER_STATUS_CODE
, 'X'
, DR.TRANSFER_REJECTION_REASON
, 'R'
, DR.TRANSFER_REJECTION_REASON
, NULL)
, LYESNO.LOOKUP_CODE
, LYESNO.MEANING
, LLBR.LOOKUP_CODE
, JEL.JE_HEADER_ID
, JEL.JE_LINE_NUM
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, CUST_ACCT.ACCOUNT_NUMBER
, NULL
FROM PA_IMPLEMENTATIONS_ALL IMP
, HR_ALL_ORGANIZATION_UNITS_TL ORG1
, GL_JE_LINES JEL
, FND_LOOKUP_VALUES LSYS
, FND_LOOKUP_VALUES LSTAT
, FND_LOOKUP_VALUES LFRTO
, FND_LOOKUP_VALUES LLBR
, FND_LOOKUP_VALUES LYESNO
, FND_LOOKUP_VALUES LTYPE
, FND_LOOKUP_VALUES LLINE
, FND_LOOKUP_VALUES LTRAN
, FND_LOOKUP_VALUES LCLASS
, PA_EVENTS EV
, PA_PROJECTS_ALL PR
, PA_TASKS PT
, PA_CUST_EVENT_RDL_ALL ERDL
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, PA_AGREEMENTS_ALL AGR
, PA_DRAFT_REVENUES_ALL DR
WHERE 'DR' = ( SELECT NVL(XLA_UTIL.GET_QUERY_CONTEXT
, 'DR')
FROM DUAL )
AND DR.PROJECT_ID = PR.PROJECT_ID
AND PA_SECURITY.ALLOW_QUERY(PR.PROJECT_ID) = 'Y'
AND ERDL.BATCH_NAME = JEL.REFERENCE_1
AND ERDL.CODE_COMBINATION_ID = JEL.CODE_COMBINATION_ID
AND JEL.REFERENCE_3 = 'REVENUE - EVENT REVENUE'
AND DR.PROJECT_ID = ERDL.PROJECT_ID
AND DR.ORG_ID = IMP.ORG_ID
AND DR.DRAFT_REVENUE_NUM = ERDL.DRAFT_REVENUE_NUM
AND ERDL.EVENT_NUM = EV.EVENT_NUM
AND ERDL.PROJECT_ID = EV.PROJECT_ID
AND NVL(ERDL.TASK_ID
, -1) =NVL(EV.TASK_ID
, -1)
AND PT.TASK_ID(+) = NVL(EV.TASK_ID
, -1)
AND PT.PROJECT_ID(+) = EV.PROJECT_ID
AND AGR.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND AGR.AGREEMENT_ID = DR.AGREEMENT_ID
AND ORG1.ORGANIZATION_ID(+) = EV.ORGANIZATION_ID
AND ORG1.LANGUAGE (+) = USERENV('LANG')
AND LYESNO.VIEW_APPLICATION_ID(+) =0
AND LYESNO.LANGUAGE (+) = USERENV('LANG')
AND LYESNO.LOOKUP_TYPE(+) = 'YES_NO'
AND LYESNO.LOOKUP_CODE(+) = DECODE(DR.TRANSFER_STATUS_CODE
, 'P'
, 'N'
, 'Y')
AND LSYS.LOOKUP_TYPE = 'PA_XLA_TRANSFER_SYSTEM'
AND LSYS.LOOKUP_CODE = 'GL'
AND LSYS.LANGUAGE = USERENV('LANG')
AND LSYS.VIEW_APPLICATION_ID =275
AND LLBR.LOOKUP_TYPE = 'YES_NO'
AND LLBR.LOOKUP_CODE = 'Y'
AND LLBR.LANGUAGE = USERENV('LANG')
AND LLBR.VIEW_APPLICATION_ID =0
AND LFRTO.LOOKUP_TYPE = 'PA_XLA_TRANSFER_FROM_TO'
AND LFRTO.LOOKUP_CODE = 'TO'
AND LFRTO.VIEW_APPLICATION_ID =275
AND LFRTO.LANGUAGE= USERENV('LANG')
AND LSTAT.VIEW_APPLICATION_ID(+) =275
AND LSTAT.LANGUAGE(+) = USERENV('LANG')
AND LSTAT.LOOKUP_TYPE(+) = 'PA_XLA_TRANSFER_STATUS'
AND LSTAT.LOOKUP_CODE(+) = DECODE(DR.TRANSFER_STATUS_CODE
, 'P'
, 'P'
, 'A'
, 'A'
, 'X'
, 'R'
, 'V'
, 'T'
, 'T'
, 'T'
, 'R'
, 'R'
, 'G'
, 'G'
, NULL)
AND LCLASS.LOOKUP_TYPE = 'PA_XLA_TXN_CLASS'
AND LCLASS.LOOKUP_CODE = 'REVENUE'
AND LCLASS.LANGUAGE = USERENV('LANG')
AND LCLASS.VIEW_APPLICATION_ID =275
AND LTYPE.LOOKUP_TYPE = 'PA_XLA_ACCT_LINE_TYPE'
AND LTYPE.LOOKUP_CODE = 'EV-REV'
AND LTYPE.LANGUAGE = USERENV('LANG')
AND LTYPE.VIEW_APPLICATION_ID =275
AND LLINE.LOOKUP_TYPE = 'PA_XLA_LINE_REF'
AND LLINE.LOOKUP_CODE = 'LINE'
AND LLINE.LANGUAGE = USERENV('LANG')
AND LLINE.VIEW_APPLICATION_ID =275
AND LTRAN.LOOKUP_TYPE = 'PA_XLA_LINE_REF'
AND LTRAN.LOOKUP_CODE = 'TRANS_ID'
AND LTRAN.LANGUAGE = USERENV('LANG')
AND LTRAN.VIEW_APPLICATION_ID =275 UNION SELECT 275
, IMP.SET_OF_BOOKS_ID
, DR.ORG_ID
, LCLASS.MEANING
, LCLASS.LOOKUP_CODE
, NULL
, NULL
, TO_CHAR(DR.DRAFT_REVENUE_NUM)
, DR.DRAFT_REVENUE_NUM
, EV.COMPLETION_DATE
, EV.DESCRIPTION
, 'DR'
, TO_NUMBER(NULL)
, LTYPE.MEANING
, LTYPE.LOOKUP_CODE
, ERDL.CODE_COMBINATION_ID
, PR.PROJECT_CURRENCY_CODE
, DECODE(SIGN(ERDL.AMOUNT)
, -1
, -1*ERDL.AMOUNT
, TO_NUMBER(NULL))
, DECODE(SIGN(ERDL.AMOUNT)
, -1
, TO_NUMBER(NULL)
, ERDL.AMOUNT)
, DECODE(SIGN(ERDL.AMOUNT)
, -1
, -1*ERDL.AMOUNT
, TO_NUMBER(NULL))
, DECODE(SIGN(ERDL.AMOUNT)
, -1
, TO_NUMBER(NULL)
, ERDL.AMOUNT)
, TO_DATE(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, DR.GL_DATE
, NULL
, 'DR'
, TO_NUMBER(NULL)
, 'ERDL'
, TO_NUMBER(NULL)
, LTYPE.MEANING||'
, '||LTRAN.MEANING|| TO_CHAR(DR.DRAFT_REVENUE_NUM)|| '
, '||LLINE.MEANING|| TO_CHAR(ERDL.LINE_NUM)
, LTYPE.MEANING||'
, '||LTRAN.MEANING|| TO_CHAR(DR.DRAFT_REVENUE_NUM)|| '
, '||LLINE.MEANING|| TO_CHAR(ERDL.LINE_NUM)
, ERDL.CREATION_DATE
, ERDL.CREATED_BY
, ERDL.REQUEST_ID
, ERDL.PROGRAM_APPLICATION_ID
, ERDL.PROGRAM_ID
, ERDL.PROGRAM_UPDATE_DATE
, PR.SEGMENT1
, DR.PROJECT_ID
, PR.NAME
, PT.TASK_NUMBER
, PT.TASK_NAME
, ORG1.NAME
, NULL
, NULL
, EV.EVENT_TYPE
, EV.EVENT_NUM
, EV.REVENUE_AMOUNT
, NULL
, DR.PA_DATE
, DR.TRANSFERRED_DATE
, CUST_ACCT.CUST_ACCOUNT_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, AGR.AGREEMENT_NUM
, DR.ACCRUE_THROUGH_DATE
, ERDL.DRAFT_REVENUE_ITEM_LINE_NUM
, EV.EVENT_ID
, ERDL.LINE_NUM
, LSYS.MEANING
, LSYS.LOOKUP_CODE
, LSTAT.MEANING
, LSTAT.LOOKUP_CODE
, LFRTO.MEANING
, DECODE(DR.TRANSFER_STATUS_CODE
, 'X'
, DR.TRANSFER_REJECTION_REASON
, 'R'
, DR.TRANSFER_REJECTION_REASON
, NULL)
, LYESNO.LOOKUP_CODE
, LYESNO.MEANING
, LLBR.LOOKUP_CODE
, IMPREF.JE_HEADER_ID
, IMPREF.JE_LINE_NUM
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, CUST_ACCT.ACCOUNT_NUMBER
, NULL
FROM PA_IMPLEMENTATIONS_ALL IMP
, HR_ALL_ORGANIZATION_UNITS_TL ORG1
, GL_IMPORT_REFERENCES IMPREF
, FND_LOOKUP_VALUES LSYS
, FND_LOOKUP_VALUES LSTAT
, FND_LOOKUP_VALUES LFRTO
, FND_LOOKUP_VALUES LLBR
, FND_LOOKUP_VALUES LYESNO
, FND_LOOKUP_VALUES LTYPE
, FND_LOOKUP_VALUES LLINE
, FND_LOOKUP_VALUES LTRAN
, FND_LOOKUP_VALUES LCLASS
, PA_EVENTS EV
, PA_PROJECTS_ALL PR
, PA_TASKS PT
, PA_CUST_EVENT_RDL_ALL ERDL
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, PA_AGREEMENTS_ALL AGR
, PA_DRAFT_REVENUES_ALL DR
WHERE 'DR' = ( SELECT NVL(XLA_UTIL.GET_QUERY_CONTEXT
, 'DR')
FROM DUAL )
AND DR.PROJECT_ID = PR.PROJECT_ID
AND PA_SECURITY.ALLOW_QUERY(PR.PROJECT_ID) = 'Y'
AND ERDL.BATCH_NAME = IMPREF.REFERENCE_1
AND ERDL.CODE_COMBINATION_ID = IMPREF.REFERENCE_2
AND IMPREF.REFERENCE_3 = 'REVENUE - EVENT REVENUE'
AND DR.PROJECT_ID = ERDL.PROJECT_ID
AND DR.ORG_ID = IMP.ORG_ID
AND DR.DRAFT_REVENUE_NUM = ERDL.DRAFT_REVENUE_NUM
AND ERDL.EVENT_NUM = EV.EVENT_NUM
AND ERDL.PROJECT_ID = EV.PROJECT_ID
AND NVL(ERDL.TASK_ID
, -1) =NVL(EV.TASK_ID
, -1)
AND PT.TASK_ID(+) = NVL(EV.TASK_ID
, -1)
AND PT.PROJECT_ID(+) = EV.PROJECT_ID
AND AGR.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND AGR.AGREEMENT_ID = DR.AGREEMENT_ID
AND ORG1.ORGANIZATION_ID(+) = EV.ORGANIZATION_ID
AND ORG1.LANGUAGE (+) = USERENV('LANG')
AND LYESNO.VIEW_APPLICATION_ID(+) =0
AND LYESNO.LANGUAGE (+) = USERENV('LANG')
AND LYESNO.LOOKUP_TYPE(+) = 'YES_NO'
AND LYESNO.LOOKUP_CODE(+) = DECODE(DR.TRANSFER_STATUS_CODE
, 'P'
, 'N'
, 'Y')
AND LSYS.LOOKUP_TYPE = 'PA_XLA_TRANSFER_SYSTEM'
AND LSYS.LOOKUP_CODE = 'GL'
AND LSYS.LANGUAGE = USERENV('LANG')
AND LSYS.VIEW_APPLICATION_ID =275
AND LLBR.LOOKUP_TYPE = 'YES_NO'
AND LLBR.LOOKUP_CODE = 'Y'
AND LLBR.LANGUAGE = USERENV('LANG')
AND LLBR.VIEW_APPLICATION_ID =0
AND LFRTO.LOOKUP_TYPE = 'PA_XLA_TRANSFER_FROM_TO'
AND LFRTO.LOOKUP_CODE = 'TO'
AND LFRTO.VIEW_APPLICATION_ID =275
AND LFRTO.LANGUAGE= USERENV('LANG')
AND LSTAT.VIEW_APPLICATION_ID(+) =275
AND LSTAT.LANGUAGE(+) = USERENV('LANG')
AND LSTAT.LOOKUP_TYPE(+) = 'PA_XLA_TRANSFER_STATUS'
AND LSTAT.LOOKUP_CODE(+) = DECODE(DR.TRANSFER_STATUS_CODE
, 'P'
, 'P'
, 'A'
, 'A'
, 'X'
, 'R'
, 'V'
, 'T'
, 'T'
, 'T'
, 'R'
, 'R'
, 'G'
, 'G'
, NULL)
AND LCLASS.LOOKUP_TYPE = 'PA_XLA_TXN_CLASS'
AND LCLASS.LOOKUP_CODE = 'REVENUE'
AND LCLASS.LANGUAGE = USERENV('LANG')
AND LCLASS.VIEW_APPLICATION_ID =275
AND LTYPE.LOOKUP_TYPE = 'PA_XLA_ACCT_LINE_TYPE'
AND LTYPE.LOOKUP_CODE = 'EV-REV'
AND LTYPE.LANGUAGE = USERENV('LANG')
AND LTYPE.VIEW_APPLICATION_ID =275
AND LLINE.LOOKUP_TYPE = 'PA_XLA_LINE_REF'
AND LLINE.LOOKUP_CODE = 'LINE'
AND LLINE.LANGUAGE = USERENV('LANG')
AND LLINE.VIEW_APPLICATION_ID =275
AND LTRAN.LOOKUP_TYPE = 'PA_XLA_LINE_REF'
AND LTRAN.LOOKUP_CODE = 'TRANS_ID'
AND LTRAN.LANGUAGE = USERENV('LANG')
AND LTRAN.VIEW_APPLICATION_ID =275 UNION SELECT 275
, IMP.SET_OF_BOOKS_ID
, DR.ORG_ID
, LCLASS.MEANING
, LCLASS.LOOKUP_CODE
, NULL
, NULL
, TO_CHAR(DR.DRAFT_REVENUE_NUM)
, DR.DRAFT_REVENUE_NUM
, TO_DATE(NULL)
, NULL
, 'DR'
, TO_NUMBER(NULL)
, LTYPE.MEANING
, LTYPE.LOOKUP_CODE
, DR.REALIZED_GAINS_CCID
, PR.PROJECT_CURRENCY_CODE
, DECODE(DR.REALIZED_GAINS_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(SIGN(DR.REALIZED_GAINS_AMOUNT)
, -1
, -1*DR.REALIZED_GAINS_AMOUNT
, TO_NUMBER(NULL) ))
, DECODE(DR.REALIZED_GAINS_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(SIGN(DR.REALIZED_GAINS_AMOUNT)
, -1
, TO_NUMBER(NULL)
, DR.REALIZED_GAINS_AMOUNT))
, DECODE(DR.REALIZED_GAINS_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(SIGN(DR.REALIZED_GAINS_AMOUNT)
, -1
, -1*DR.REALIZED_GAINS_AMOUNT
, TO_NUMBER(NULL) ))
, DECODE(DR.REALIZED_GAINS_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(SIGN(DR.REALIZED_GAINS_AMOUNT)
, -1
, TO_NUMBER(NULL)
, DR.REALIZED_GAINS_AMOUNT))
, TO_DATE(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, DR.GL_DATE
, NULL
, 'DR'
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, DR.CREATION_DATE
, DR.CREATED_BY
, DR.REQUEST_ID
, DR.PROGRAM_APPLICATION_ID
, DR.PROGRAM_ID
, DR.PROGRAM_UPDATE_DATE
, PR.SEGMENT1
, DR.PROJECT_ID
, PR.NAME
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, DR.PA_DATE
, DR.TRANSFERRED_DATE
, CUST_ACCT.CUST_ACCOUNT_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, AGR.AGREEMENT_NUM
, DR.ACCRUE_THROUGH_DATE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, LSYS.MEANING
, LSYS.LOOKUP_CODE
, LSTAT.MEANING
, LSTAT.LOOKUP_CODE
, LFRTO.MEANING
, DECODE(DR.TRANSFER_STATUS_CODE
, 'X'
, DR.TRANSFER_REJECTION_REASON
, 'R'
, DR.TRANSFER_REJECTION_REASON
, NULL)
, LYESNO.LOOKUP_CODE
, LYESNO.MEANING
, LLBR.LOOKUP_CODE
, JEL.JE_HEADER_ID
, JEL.JE_LINE_NUM
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, CUST_ACCT.ACCOUNT_NUMBER
, NULL
FROM PA_IMPLEMENTATIONS_ALL IMP
, PA_PROJECTS_ALL PR
, FND_LOOKUP_VALUES LSYS
, FND_LOOKUP_VALUES LSTAT
, GL_JE_LINES JEL
, FND_LOOKUP_VALUES LFRTO
, FND_LOOKUP_VALUES LLBR
, FND_LOOKUP_VALUES LYESNO
, FND_LOOKUP_VALUES LTYPE
, FND_LOOKUP_VALUES LCLASS
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, PA_AGREEMENTS_ALL AGR
, PA_DRAFT_REVENUES_ALL DR
WHERE 'DR' = ( SELECT NVL(XLA_UTIL.GET_QUERY_CONTEXT
, 'DR')
FROM DUAL )
AND DR.PROJECT_ID = PR.PROJECT_ID
AND PA_SECURITY.ALLOW_QUERY(PR.PROJECT_ID) = 'Y'
AND DR.TRANSFER_STATUS_CODE <> 'P'
AND DR.UNEARNED_BATCH_NAME = JEL.REFERENCE_1
AND DR.UNEARNED_CODE_COMBINATION_ID = JEL.CODE_COMBINATION_ID
AND JEL.REFERENCE_3 = 'REVENUE - REALIZED GAINS'
AND AGR.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND IMP.ORG_ID = DR.ORG_ID
AND AGR.AGREEMENT_ID = DR.AGREEMENT_ID
AND LYESNO.VIEW_APPLICATION_ID(+) =0
AND LYESNO.LANGUAGE(+) = USERENV('LANG')
AND LYESNO.LOOKUP_TYPE(+) = 'YES_NO'
AND LYESNO.LOOKUP_CODE(+) = DECODE(DR.TRANSFER_STATUS_CODE
, 'P'
, 'N'
, 'Y')
AND LSYS.LOOKUP_TYPE = 'PA_XLA_TRANSFER_SYSTEM'
AND LSYS.LOOKUP_CODE = 'GL'
AND LSYS.VIEW_APPLICATION_ID =275
AND LSYS.LANGUAGE = USERENV('LANG')
AND LLBR.LOOKUP_TYPE = 'YES_NO'
AND LLBR.LOOKUP_CODE = 'Y'
AND LLBR.VIEW_APPLICATION_ID =0
AND LLBR.LANGUAGE = USERENV('LANG')
AND LFRTO.LOOKUP_TYPE = 'PA_XLA_TRANSFER_FROM_TO'
AND LFRTO.LOOKUP_CODE = 'TO'
AND LFRTO.VIEW_APPLICATION_ID =275
AND LFRTO.LANGUAGE = USERENV('LANG')
AND LSTAT.LOOKUP_TYPE = 'PA_XLA_TRANSFER_STATUS'
AND LSTAT.LOOKUP_CODE = 'T'
AND LSTAT.LANGUAGE = USERENV('LANG')
AND LSTAT.VIEW_APPLICATION_ID =275
AND LCLASS.LOOKUP_TYPE = 'PA_XLA_TXN_CLASS'
AND LCLASS.LOOKUP_CODE = 'REVENUE'
AND LCLASS.VIEW_APPLICATION_ID =275
AND LCLASS.LANGUAGE = USERENV('LANG')
AND LTYPE.LOOKUP_TYPE = 'PA_XLA_ACCT_LINE_TYPE'
AND LTYPE.LOOKUP_CODE = 'RLZD-GAINS'
AND LTYPE.LANGUAGE = USERENV('LANG') UNION SELECT 275
, IMP.SET_OF_BOOKS_ID
, DR.ORG_ID
, LCLASS.MEANING
, LCLASS.LOOKUP_CODE
, NULL
, NULL
, TO_CHAR(DR.DRAFT_REVENUE_NUM)
, DR.DRAFT_REVENUE_NUM
, TO_DATE(NULL)
, NULL
, 'DR'
, TO_NUMBER(NULL)
, LTYPE.MEANING
, LTYPE.LOOKUP_CODE
, DR.REALIZED_GAINS_CCID
, PR.PROJECT_CURRENCY_CODE
, DECODE(DR.REALIZED_GAINS_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(SIGN(DR.REALIZED_GAINS_AMOUNT)
, -1
, -1*DR.REALIZED_GAINS_AMOUNT
, TO_NUMBER(NULL) ))
, DECODE(DR.REALIZED_GAINS_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(SIGN(DR.REALIZED_GAINS_AMOUNT)
, -1
, TO_NUMBER(NULL)
, DR.REALIZED_GAINS_AMOUNT))
, DECODE(DR.REALIZED_GAINS_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(SIGN(DR.REALIZED_GAINS_AMOUNT)
, -1
, -1*DR.REALIZED_GAINS_AMOUNT
, TO_NUMBER(NULL) ))
, DECODE(DR.REALIZED_GAINS_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(SIGN(DR.REALIZED_GAINS_AMOUNT)
, -1
, TO_NUMBER(NULL)
, DR.REALIZED_GAINS_AMOUNT))
, TO_DATE(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, DR.GL_DATE
, NULL
, 'DR'
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, DR.CREATION_DATE
, DR.CREATED_BY
, DR.REQUEST_ID
, DR.PROGRAM_APPLICATION_ID
, DR.PROGRAM_ID
, DR.PROGRAM_UPDATE_DATE
, PR.SEGMENT1
, DR.PROJECT_ID
, PR.NAME
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, DR.PA_DATE
, DR.TRANSFERRED_DATE
, CUST_ACCT.CUST_ACCOUNT_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, AGR.AGREEMENT_NUM
, DR.ACCRUE_THROUGH_DATE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, LSYS.MEANING
, LSYS.LOOKUP_CODE
, LSTAT.MEANING
, LSTAT.LOOKUP_CODE
, LFRTO.MEANING
, DECODE(DR.TRANSFER_STATUS_CODE
, 'X'
, DR.TRANSFER_REJECTION_REASON
, 'R'
, DR.TRANSFER_REJECTION_REASON
, NULL)
, LYESNO.LOOKUP_CODE
, LYESNO.MEANING
, LLBR.LOOKUP_CODE
, IMPREF.JE_HEADER_ID
, IMPREF.JE_LINE_NUM
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, CUST_ACCT.ACCOUNT_NUMBER
, NULL
FROM PA_IMPLEMENTATIONS_ALL IMP
, PA_PROJECTS_ALL PR
, FND_LOOKUP_VALUES LSYS
, FND_LOOKUP_VALUES LSTAT
, GL_IMPORT_REFERENCES IMPREF
, FND_LOOKUP_VALUES LFRTO
, FND_LOOKUP_VALUES LLBR
, FND_LOOKUP_VALUES LYESNO
, FND_LOOKUP_VALUES LTYPE
, FND_LOOKUP_VALUES LCLASS
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, PA_AGREEMENTS_ALL AGR
, PA_DRAFT_REVENUES_ALL DR
WHERE 'DR' = ( SELECT NVL(XLA_UTIL.GET_QUERY_CONTEXT
, 'DR')
FROM DUAL )
AND DR.PROJECT_ID = PR.PROJECT_ID
AND PA_SECURITY.ALLOW_QUERY(PR.PROJECT_ID) = 'Y'
AND DR.TRANSFER_STATUS_CODE <> 'P'
AND DR.UNEARNED_BATCH_NAME = IMPREF.REFERENCE_1
AND DR.UNEARNED_CODE_COMBINATION_ID = IMPREF.REFERENCE_2
AND IMPREF.REFERENCE_3 = 'REVENUE - REALIZED GAINS'
AND AGR.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND IMP.ORG_ID = DR.ORG_ID
AND AGR.AGREEMENT_ID = DR.AGREEMENT_ID
AND LYESNO.VIEW_APPLICATION_ID(+) =0
AND LYESNO.LANGUAGE(+) = USERENV('LANG')
AND LYESNO.LOOKUP_TYPE(+) = 'YES_NO'
AND LYESNO.LOOKUP_CODE(+) = DECODE(DR.TRANSFER_STATUS_CODE
, 'P'
, 'N'
, 'Y')
AND LSYS.LOOKUP_TYPE = 'PA_XLA_TRANSFER_SYSTEM'
AND LSYS.LOOKUP_CODE = 'GL'
AND LSYS.VIEW_APPLICATION_ID =275
AND LSYS.LANGUAGE = USERENV('LANG')
AND LLBR.LOOKUP_TYPE = 'YES_NO'
AND LLBR.LOOKUP_CODE = 'Y'
AND LLBR.VIEW_APPLICATION_ID =0
AND LLBR.LANGUAGE = USERENV('LANG')
AND LFRTO.LOOKUP_TYPE = 'PA_XLA_TRANSFER_FROM_TO'
AND LFRTO.LOOKUP_CODE = 'TO'
AND LFRTO.VIEW_APPLICATION_ID =275
AND LFRTO.LANGUAGE = USERENV('LANG')
AND LSTAT.LOOKUP_TYPE = 'PA_XLA_TRANSFER_STATUS'
AND LSTAT.LOOKUP_CODE = 'T'
AND LSTAT.LANGUAGE = USERENV('LANG')
AND LSTAT.VIEW_APPLICATION_ID =275
AND LCLASS.LOOKUP_TYPE = 'PA_XLA_TXN_CLASS'
AND LCLASS.LOOKUP_CODE = 'REVENUE'
AND LCLASS.VIEW_APPLICATION_ID =275
AND LCLASS.LANGUAGE = USERENV('LANG')
AND LTYPE.LOOKUP_TYPE = 'PA_XLA_ACCT_LINE_TYPE'
AND LTYPE.LOOKUP_CODE = 'RLZD-GAINS'
AND LTYPE.LANGUAGE = USERENV('LANG') UNION SELECT 275
, IMP.SET_OF_BOOKS_ID
, DR.ORG_ID
, LCLASS.MEANING
, LCLASS.LOOKUP_CODE
, NULL
, NULL
, TO_CHAR(DR.DRAFT_REVENUE_NUM)
, DR.DRAFT_REVENUE_NUM
, TO_DATE(NULL)
, NULL
, 'DR'
, TO_NUMBER(NULL)
, LTYPE.MEANING
, LTYPE.LOOKUP_CODE
, DR.REALIZED_LOSSES_CCID
, PR.PROJECT_CURRENCY_CODE
, DECODE(DR.REALIZED_LOSSES_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(SIGN(DR.REALIZED_LOSSES_AMOUNT)
, -1
, -1*DR.REALIZED_LOSSES_AMOUNT
, TO_NUMBER(NULL) ))
, DECODE(DR.REALIZED_LOSSES_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(SIGN(DR.REALIZED_LOSSES_AMOUNT)
, -1
, TO_NUMBER(NULL)
, DR.REALIZED_LOSSES_AMOUNT))
, DECODE(DR.REALIZED_LOSSES_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(SIGN(DR.REALIZED_LOSSES_AMOUNT)
, -1
, -1*DR.REALIZED_LOSSES_AMOUNT
, TO_NUMBER(NULL) ))
, DECODE(DR.REALIZED_LOSSES_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(SIGN(DR.REALIZED_LOSSES_AMOUNT)
, -1
, TO_NUMBER(NULL)
, DR.REALIZED_LOSSES_AMOUNT))
, TO_DATE(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, DR.GL_DATE
, NULL
, 'DR'
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, DR.CREATION_DATE
, DR.CREATED_BY
, DR.REQUEST_ID
, DR.PROGRAM_APPLICATION_ID
, DR.PROGRAM_ID
, DR.PROGRAM_UPDATE_DATE
, PR.SEGMENT1
, DR.PROJECT_ID
, PR.NAME
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, DR.PA_DATE
, DR.TRANSFERRED_DATE
, CUST_ACCT.CUST_ACCOUNT_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, AGR.AGREEMENT_NUM
, DR.ACCRUE_THROUGH_DATE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, LSYS.MEANING
, LSYS.LOOKUP_CODE
, LSTAT.MEANING
, LSTAT.LOOKUP_CODE
, LFRTO.MEANING
, DECODE(DR.TRANSFER_STATUS_CODE
, 'X'
, DR.TRANSFER_REJECTION_REASON
, 'R'
, DR.TRANSFER_REJECTION_REASON
, NULL)
, LYESNO.LOOKUP_CODE
, LYESNO.MEANING
, LLBR.LOOKUP_CODE
, JEL.JE_HEADER_ID
, JEL.JE_LINE_NUM
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, CUST_ACCT.ACCOUNT_NUMBER
, NULL
FROM PA_IMPLEMENTATIONS_ALL IMP
, PA_PROJECTS_ALL PR
, FND_LOOKUP_VALUES LSYS
, FND_LOOKUP_VALUES LSTAT
, GL_JE_LINES JEL
, FND_LOOKUP_VALUES LFRTO
, FND_LOOKUP_VALUES LLBR
, FND_LOOKUP_VALUES LYESNO
, FND_LOOKUP_VALUES LTYPE
, FND_LOOKUP_VALUES LCLASS
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, PA_AGREEMENTS_ALL AGR
, PA_DRAFT_REVENUES_ALL DR
WHERE 'DR' = ( SELECT NVL(XLA_UTIL.GET_QUERY_CONTEXT
, 'DR')
FROM DUAL )
AND DR.PROJECT_ID = PR.PROJECT_ID
AND PA_SECURITY.ALLOW_QUERY(PR.PROJECT_ID) = 'Y'
AND DR.TRANSFER_STATUS_CODE <> 'P'
AND DR.UNEARNED_BATCH_NAME = JEL.REFERENCE_1
AND DR.UNEARNED_CODE_COMBINATION_ID = JEL.CODE_COMBINATION_ID
AND JEL.REFERENCE_3 = 'REVENUE - REALIZED LOSSES'
AND AGR.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND IMP.ORG_ID = DR.ORG_ID
AND AGR.AGREEMENT_ID = DR.AGREEMENT_ID
AND LYESNO.VIEW_APPLICATION_ID(+) =0
AND LYESNO.LANGUAGE(+) = USERENV('LANG')
AND LYESNO.LOOKUP_TYPE(+) = 'YES_NO'
AND LYESNO.LOOKUP_CODE(+) = DECODE(DR.TRANSFER_STATUS_CODE
, 'P'
, 'N'
, 'Y')
AND LSYS.LOOKUP_TYPE = 'PA_XLA_TRANSFER_SYSTEM'
AND LSYS.LOOKUP_CODE = 'GL'
AND LSYS.VIEW_APPLICATION_ID =275
AND LSYS.LANGUAGE = USERENV('LANG')
AND LLBR.LOOKUP_TYPE = 'YES_NO'
AND LLBR.LOOKUP_CODE = 'Y'
AND LLBR.VIEW_APPLICATION_ID =0
AND LLBR.LANGUAGE = USERENV('LANG')
AND LFRTO.LOOKUP_TYPE = 'PA_XLA_TRANSFER_FROM_TO'
AND LFRTO.LOOKUP_CODE = 'TO'
AND LFRTO.VIEW_APPLICATION_ID =275
AND LFRTO.LANGUAGE = USERENV('LANG')
AND LSTAT.LOOKUP_TYPE = 'PA_XLA_TRANSFER_STATUS'
AND LSTAT.LOOKUP_CODE = 'T'
AND LSTAT.LANGUAGE = USERENV('LANG')
AND LSTAT.VIEW_APPLICATION_ID =275
AND LCLASS.LOOKUP_TYPE = 'PA_XLA_TXN_CLASS'
AND LCLASS.LOOKUP_CODE = 'REVENUE'
AND LCLASS.VIEW_APPLICATION_ID =275
AND LCLASS.LANGUAGE = USERENV('LANG')
AND LTYPE.LOOKUP_TYPE = 'PA_XLA_ACCT_LINE_TYPE'
AND LTYPE.LOOKUP_CODE = 'RLZD-LOSSES'
AND LTYPE.LANGUAGE = USERENV('LANG') UNION SELECT 275
, IMP.SET_OF_BOOKS_ID
, DR.ORG_ID
, LCLASS.MEANING
, LCLASS.LOOKUP_CODE
, NULL
, NULL
, TO_CHAR(DR.DRAFT_REVENUE_NUM)
, DR.DRAFT_REVENUE_NUM
, TO_DATE(NULL)
, NULL
, 'DR'
, TO_NUMBER(NULL)
, LTYPE.MEANING
, LTYPE.LOOKUP_CODE
, DR.REALIZED_LOSSES_CCID
, PR.PROJECT_CURRENCY_CODE
, DECODE(DR.REALIZED_LOSSES_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(SIGN(DR.REALIZED_LOSSES_AMOUNT)
, -1
, -1*DR.REALIZED_LOSSES_AMOUNT
, TO_NUMBER(NULL) ))
, DECODE(DR.REALIZED_LOSSES_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(SIGN(DR.REALIZED_LOSSES_AMOUNT)
, -1
, TO_NUMBER(NULL)
, DR.REALIZED_LOSSES_AMOUNT))
, DECODE(DR.REALIZED_LOSSES_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(SIGN(DR.REALIZED_LOSSES_AMOUNT)
, -1
, -1*DR.REALIZED_LOSSES_AMOUNT
, TO_NUMBER(NULL) ))
, DECODE(DR.REALIZED_LOSSES_AMOUNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(SIGN(DR.REALIZED_LOSSES_AMOUNT)
, -1
, TO_NUMBER(NULL)
, DR.REALIZED_LOSSES_AMOUNT))
, TO_DATE(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, DR.GL_DATE
, NULL
, 'DR'
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, DR.CREATION_DATE
, DR.CREATED_BY
, DR.REQUEST_ID
, DR.PROGRAM_APPLICATION_ID
, DR.PROGRAM_ID
, DR.PROGRAM_UPDATE_DATE
, PR.SEGMENT1
, DR.PROJECT_ID
, PR.NAME
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, DR.PA_DATE
, DR.TRANSFERRED_DATE
, CUST_ACCT.CUST_ACCOUNT_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, AGR.AGREEMENT_NUM
, DR.ACCRUE_THROUGH_DATE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, LSYS.MEANING
, LSYS.LOOKUP_CODE
, LSTAT.MEANING
, LSTAT.LOOKUP_CODE
, LFRTO.MEANING
, DECODE(DR.TRANSFER_STATUS_CODE
, 'X'
, DR.TRANSFER_REJECTION_REASON
, 'R'
, DR.TRANSFER_REJECTION_REASON
, NULL)
, LYESNO.LOOKUP_CODE
, LYESNO.MEANING
, LLBR.LOOKUP_CODE
, IMPREF.JE_HEADER_ID
, IMPREF.JE_LINE_NUM
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, CUST_ACCT.ACCOUNT_NUMBER
, NULL
FROM PA_IMPLEMENTATIONS_ALL IMP
, PA_PROJECTS_ALL PR
, FND_LOOKUP_VALUES LSYS
, FND_LOOKUP_VALUES LSTAT
, GL_IMPORT_REFERENCES IMPREF
, FND_LOOKUP_VALUES LFRTO
, FND_LOOKUP_VALUES LLBR
, FND_LOOKUP_VALUES LYESNO
, FND_LOOKUP_VALUES LTYPE
, FND_LOOKUP_VALUES LCLASS
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, PA_AGREEMENTS_ALL AGR
, PA_DRAFT_REVENUES_ALL DR
WHERE 'DR' = ( SELECT NVL(XLA_UTIL.GET_QUERY_CONTEXT
, 'DR')
FROM DUAL )
AND DR.PROJECT_ID = PR.PROJECT_ID
AND PA_SECURITY.ALLOW_QUERY(PR.PROJECT_ID) = 'Y'
AND DR.TRANSFER_STATUS_CODE <> 'P'
AND DR.UNEARNED_BATCH_NAME = IMPREF.REFERENCE_1
AND DR.UNEARNED_CODE_COMBINATION_ID = IMPREF.REFERENCE_2
AND IMPREF.REFERENCE_3 = 'REVENUE - REALIZED LOSSES'
AND AGR.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND IMP.ORG_ID = DR.ORG_ID
AND AGR.AGREEMENT_ID = DR.AGREEMENT_ID
AND LYESNO.VIEW_APPLICATION_ID(+) =0
AND LYESNO.LANGUAGE(+) = USERENV('LANG')
AND LYESNO.LOOKUP_TYPE(+) = 'YES_NO'
AND LYESNO.LOOKUP_CODE(+) = DECODE(DR.TRANSFER_STATUS_CODE
, 'P'
, 'N'
, 'Y')
AND LSYS.LOOKUP_TYPE = 'PA_XLA_TRANSFER_SYSTEM'
AND LSYS.LOOKUP_CODE = 'GL'
AND LSYS.VIEW_APPLICATION_ID =275
AND LSYS.LANGUAGE = USERENV('LANG')
AND LLBR.LOOKUP_TYPE = 'YES_NO'
AND LLBR.LOOKUP_CODE = 'Y'
AND LLBR.VIEW_APPLICATION_ID =0
AND LLBR.LANGUAGE = USERENV('LANG')
AND LFRTO.LOOKUP_TYPE = 'PA_XLA_TRANSFER_FROM_TO'
AND LFRTO.LOOKUP_CODE = 'TO'
AND LFRTO.VIEW_APPLICATION_ID =275
AND LFRTO.LANGUAGE = USERENV('LANG')
AND LSTAT.LOOKUP_TYPE = 'PA_XLA_TRANSFER_STATUS'
AND LSTAT.LOOKUP_CODE = 'T'
AND LSTAT.LANGUAGE = USERENV('LANG')
AND LSTAT.VIEW_APPLICATION_ID =275
AND LCLASS.LOOKUP_TYPE = 'PA_XLA_TXN_CLASS'
AND LCLASS.LOOKUP_CODE = 'REVENUE'
AND LCLASS.VIEW_APPLICATION_ID =275
AND LCLASS.LANGUAGE = USERENV('LANG')
AND LTYPE.LOOKUP_TYPE = 'PA_XLA_ACCT_LINE_TYPE'
AND LTYPE.LOOKUP_CODE = 'RLZD-LOSSES'
AND LTYPE.LANGUAGE = USERENV('LANG')