AR Sales Journal By Customer

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Sales Journal By Customer
Application: Receivables
Source: Sales Journal By Customer (XML)
Short Name: RAXGLA_XML
DB package: AR_RAXGLA_XMLP_PKG
Run AR Sales Journal By Customer and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT	 
	gl_dist.code_combination_id  code_combination_id,
	gl_dist.account_class gl_acc_type,
	null  segment,
	trx.trx_number  trx_number,
	--null  balancing_seg_rev,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_acct_flex_bal_seg', 'SQLGL', 'GL#', gl.CHART_OF_ACCOUNTS_ID, NULL, gl.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') balancing_seg_rev,
	arpt_sql_func_util.get_trx_type_details(trx.cust_trx_type_id,'TYPE', trx.org_id) trx_type,
	 fnd_flex_xml_publisher_apis.process_kff_combination_1('d_acct_flex_acct_seg', 'SQLGL', 'GL#', gl.CHART_OF_ACCOUNTS_ID, NULL, gl.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE')  account_seg_rev,
	substrb(party.party_name,1,50) customer_name,
	c.account_number customer_number,
                to_char(trx.trx_date,'DD-MON-YY') trx_date,
	to_char(gl_dist.gl_date,'DD-MON-YY') gl_date,
        	sum(nvl(decode(gl_dist.account_class,
                                               'REC',  decode(sign(gl_dist.amount),
                                                                          1, 0,
                                                                             -1 * gl_dist.amount
                                                                         ),
                                                           decode(sign(gl_dist.amount),
                                                                         1,  gl_dist.amount,
                                                                              0
                                                                        ) 
                                              )   ,0
                                ) 
                           )   curr_credit_amount,
                   sum(nvl(decode(gl_dist.account_class,
                                                'REC',   decode(sign(gl_dist.amount),
                                                                            1,  gl_dist.amount,
                                                                                 0
                                                                            ),
                                                              decode(sign(gl_dist.amount),
                                                                            1, 0,
                                                                                -1 * gl_dist.amount
                                                                           )
                                              ), 0
                                   )
                            ) curr_debit_amount ,
                  trx.invoice_currency_code currency_code_rev , 
                  &P_LINE_NUMBER                         line_number,
                  decode(upper(:p_sort_by),
                               'CUSTOMER',             substrb(party.party_name,1,50), 
                               'INVOICE NUMBER',   trx.trx_number, 
                                                                 trx.trx_number) orderby1,
                decode(upper(:p_sort_by),
                              'INVOICE NUMBER',  substrb(party.party_name,1,50), 
                              'CUSTOMER',            trx.trx_number, 
                                                               c.account_number)  orderby2,
               trx.org_id org_id1, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_acct_flex_bal_seg', 'SQLGL', 'GL#', gl.CHART_OF_ACCOUNTS_ID, NULL, gl.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_Segment_Curr, 
	AR_RAXGLA_XMLP_PKG.out_of_balanceformula(:Sum_Curr_Cr_Amt_Seg_Rev, :Sum_Curr_Dr_Amt_Seg_Rev) OUT_OF_BALANCE, 
	AR_RAXGLA_XMLP_PKG.set_curr_code_revformula(trx.invoice_currency_code) Set_Curr_Code_Rev, 
	AR_RAXGLA_XMLP_PKG.line_number_displayformula(&P_LINE_NUMBER) LINE_NUMBER_DISPLAY, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_segment', 'SQLGL', 'GL#', gl.CHART_OF_ACCOUNTS_ID, NULL, gl.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') D_segment
	--AR_RAXGLA_XMLP_PKG.CUST_NAME_P(substrb(party.party_name,1,50)) CUST_NAME,
	--AR_RAXGLA_XMLP_PKG.CUST_CURR_p(substrb(party.party_name,1,50),trx.invoice_currency_code) CUST_CURR,
	/*AR_RAXGLA_XMLP_PKG.cust_dr_P(substrb(party.party_name,1,50),sum(nvl(decode(gl_dist.account_class,
                                                'REC',   decode(sign(gl_dist.amount),
                                                                            1,  gl_dist.amount,
                                                                                 0
                                                                            ),
                                                              decode(sign(gl_dist.amount),
                                                                            1, 0,
                                                                                -1 * gl_dist.amount
                                                                           )
                                              ), 0
                                   )
                            )) cust_dr,
	AR_RAXGLA_XMLP_PKG.CUST_CR_P(substrb(party.party_name,1,50),sum(nvl(decode(gl_dist.account_class,
                                               'REC',  decode(sign(gl_dist.amount),
                                                                          1, 0,
                                                                             -1 * gl_dist.amount
                                                                         ),
                                                           decode(sign(gl_dist.amount),
                                                                         1,  gl_dist.amount,
                                                                              0
                                                                        ) 
                                              )   ,0
                                ) 
                           )) CUST_CR*/
FROM 
     	hz_cust_accounts_all           c,
	hz_parties                            party,
     	ra_customer_trx_all            trx,
     	ra_customer_trx_lines_all     lines,
                ra_customer_trx_lines_all     link_line,
     	gl_code_combinations           gl,
                ar_xla_ctlgd_lines_v                 gl_dist
WHERE
       trx.complete_flag                            = 'Y'
and  gl_dist.account_set_flag                = 'N'
and  link_line.customer_trx_line_id(+)  = lines.link_to_cust_trx_line_id
and  gl_dist.customer_trx_line_id          = lines.customer_trx_line_id(+)
and  gl_dist.customer_trx_id                  = trx.customer_trx_id
and  c.cust_account_id                         = trx.bill_to_customer_id
and  c.party_id 		           = party.party_id
and  gl.code_combination_id                  = gl_dist.code_combination_id
and  gl_dist.set_of_books_id                  = :set_of_books_id
and  nvl(gl_dist.gl_posted_date, TO_DATE('01/01/0001', 'MM/DD/YYYY')) = 
       decode(:p_posting_status,
                         'POSTED',      gl_dist.gl_posted_date,
                         'UNPOSTED', TO_DATE('01/01/0001','MM/DD/YYYY'),
                                               nvl(gl_dist.gl_posted_date, TO_DATE('01/01/0001', 'MM/DD/YYYY')))
&P_WHERE_1          
&LP_START_GL_DATE
&LP_END_GL_DATE
&LP_TRX_DATE_LOW
&LP_TRX_DATE_HIGH
&P_WHERE_GL_TYPE
and &LP_COMPANY_START
and &LP_COMPANY_END 
&LP_TRX_TYPE_LOW
&LP_TRX_TYPE_HIGH
&LP_TRX_NUMBER_LOW
&LP_TRX_NUMBER_HIGH
&LP_CUSTOMER_LOW      
&LP_CUSTOMER_HIGH
&LP_CUSTOMER_NUMBER_LOW
&LP_CUSTOMER_NUMBER_HIGH
&P_ORG_WHERE_C
&P_ORG_WHERE_TRX
&P_ORG_WHERE_LINES
&P_ORG_WHERE_LINK_LINE
&P_ORG_WHERE_GL_DIST
&LP_ZERO_ROUND 
 and trx.org_id=:org_id
group by
                trx.org_id,
                trx.invoice_currency_code ,
				fnd_flex_xml_publisher_apis.process_kff_combination_1('d_acct_flex_bal_seg', 'SQLGL', 'GL#', gl.CHART_OF_ACCOUNTS_ID, NULL, gl.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
                trx.trx_number,
                party.party_name, 
                c.account_number, 
                gl_dist.code_combination_id,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('d_segment', 'SQLGL', 'GL#', gl.CHART_OF_ACCOUNTS_ID, NULL, gl.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'),
                &P_LINE_NUMBER,
                &P_LINE_NUMBER_ORDER,
                arpt_sql_func_util.get_trx_type_details(trx.cust_trx_type_id,'NAME',trx.org_id), 
                fnd_flex_xml_publisher_apis.process_kff_combination_1('d_acct_flex_acct_seg', 'SQLGL', 'GL#', gl.CHART_OF_ACCOUNTS_ID, NULL, gl.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE'),
                to_char(gl_dist.gl_date,'DD-MON-YY'),
                 to_char(trx.trx_date,'DD-MON-YY'),
                arpt_sql_func_util.get_trx_type_details(trx.cust_trx_type_id,'TYPE',trx.org_id),
                gl_dist.account_class
order by 
                 trx.invoice_currency_code
				 ,5 ASC,16 ASC,17 ASC,4 ASC,6 ASC,8 ASC,9 ASC,10 ASC ,18 ASC, 11 ASC , 15 ASC  
				 /*,trx.invoice_currency_code,
                 fnd_flex_xml_publisher_apis.process_kff_combination_1('d_segment', 'SQLGL', 'GL#', gl.CHART_OF_ACCOUNTS_ID, NULL, gl.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE'),
                 decode(upper(:p_sort_by),
                               'CUSTOMER',             substrb(party.party_name,1,50), 
                               'INVOICE NUMBER',  trx.trx_number, 
                                                                    trx.trx_number),
                decode(upper(:p_sort_by),
                              'INVOICE NUMBER',  substrb(party.party_name,1,50), 
                               'CUSTOMER',            trx.trx_number, 
                                                                    c.account_number),   
                 &P_LINE_NUMBER_ORDER,
                to_char(gl_dist.gl_date,'DD-MON-YY')
          --       &ACCT_FLEX_ACCT_SEG,
            --     &ACCT_FLEX_ALL_SEG*/
Parameter Name SQL text Validation
Reporting Level 
 
 LOV Oracle
Reporting Context 
 
 LOV Oracle
Order By 
 
 LOV Oracle
Report By Line 
 
 LOV Oracle
Company Segment Low 
 
 Char
Company Segment High 
 
 Char
GL Date From 
 
 Date
GL Date To 
 
 Date
Entered Currency 
 
 LOV Oracle
GL Account Type 
 
 LOV Oracle
Posting Status 
 
 LOV Oracle
Customer Name Low 
 
 LOV Oracle
Customer Name High 
 
 LOV Oracle
Customer Number Low 
 
 LOV Oracle
Customer Number High 
 
 LOV Oracle
Transaction Date From 
 
 Date
Transaction Date To 
 
 Date
Transaction Type Low 
 
 LOV Oracle
Transaction Type High 
 
 LOV Oracle
Transaction Number Low 
 
 LOV Oracle
Transaction Number High 
 
 LOV Oracle
Exclude Rounding equals zero 
 
 LOV Oracle
Download
Related Blitz Reports
AR Open Items Revaluation AR Invoice Exception AR Invoices Posted to Suspense AR Discount Projection AR Inter Company Invoices AR Transaction Detail GL Account Analysis (Distributions) GL Account Analysis GL Account Analysis 11g
Blitz Report™

Blitz Report™ provides multiple benefits: