AR Sales Journal By Customer

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Sales Journal By Customer (XML)
Short Name: RAXGLA_XML
DB package: AR_RAXGLA_XMLP_PKG
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
Chart Of Accounts ID
 
Exclude Rounding equals zero
 
LOV Oracle
Transaction Number High
 
LOV Oracle
Transaction Number Low
 
LOV Oracle
Transaction Type High
 
LOV Oracle
Transaction Type Low
 
LOV Oracle
Transaction Date To
 
Date
Transaction Date From
 
Date
Customer Number High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Customer Name High
 
LOV Oracle
Customer Name Low
 
LOV Oracle
Posting Status
 
LOV Oracle
GL Account Type
 
LOV Oracle
Entered Currency
 
LOV Oracle
GL Date To
 
Date
GL Date From
 
Date
Company Segment High
 
Company Segment Low
 
Report By Line
 
LOV Oracle
Order By
 
LOV Oracle
Reporting Context
 
LOV Oracle
Reporting Level
 
LOV Oracle