AR Transaction Batch Source Listing

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Transaction Batch Source Listing (XML)
Short Name: RAXSOL_XML
DB package: AR_RAXSOL_XMLP_PKG
SELECT
       substrb( rbs.name,1,15)                            Name ,
        substrb(rbs.description ,1,40)                    Description ,
       look6.meaning                       Type     ,
       look1.meaning                       Status   ,
       decode(rbs.auto_batch_numbering_flag,'Y',:rp_yes,'N',:rp_no,NULL)   Auto_Batch_Numbering,
       decode(rbs.auto_trx_numbering_flag,'Y',:rp_yes,'N',:rp_no,NULL) Auto_Invoice_Numbering,
       rbs.last_batch_num                  Last_Batch_Number ,
       rac.name                            Standard_Transaction_Type,
       rbs.start_date                      Effective_Date  ,
       rbs.end_date                        Ineffective_Date,
       rbs.batch_source_id                 Batch_Source_Id,
       rbs.auto_trx_numbering_flag         Auto_Trx_Numbering ,
       look4.meaning                       Invalid_Tax_Rate   ,
       decode(rbs.create_clearing_flag,'Y',:rp_yes,'N',:rp_no,NULL) Create_Clearing,
       decode(rbs.allow_sales_credit_flag , 'Y',:rp_yes,'N',:rp_no,NULL) Allow_Sales_Credit,
       look5.meaning                       Invalid_Lines ,
       look7.meaning                       GL_Date_in_a_Closed_Period ,
       rgr.name                            Grouping_Rule ,
       decode(rbs.cust_trx_type_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Transaction_Type ,
       decode(rbs.sold_customer_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Sold_to_Customer ,
       decode(rbs.bill_address_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Bill_To_Address ,
       decode(rbs.ship_customer_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Ship_To_Customer ,
       decode(rbs.ship_contact_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Ship_To_Contact ,
       decode(rbs.customer_bank_account_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Customer_Bank_Account ,
       decode(rbs.bill_customer_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Bill_To_Customer ,
       decode(rbs.bill_contact_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Bill_To_Contact ,
       decode(rbs.ship_address_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Ship_To_Address ,
       decode(rbs.receipt_method_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Payment_Method_Rule ,
       decode(rbs.invoicing_rule_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Invoicing_Rule ,
       decode(rbs.accounting_rule_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Accounting_Rule_Rule ,
       decode(rbs.accounting_flexfield_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Accounting_Flexfield ,
       decode(rbs.derive_date_flag,'Y',:rp_yes,'N',:rp_no,NULL)   Derive_Date,
       decode(rbs.term_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Payment_Terms ,
       decode(rbs.rev_acc_allocation_rule,'Amount',:rp_amt,'Percent',:rp_per,NULL)
	     Revenue_Account_Allocation,
       decode(rbs.memo_reason_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Memo_Reason ,
       decode(rbs.memo_line_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Memo_Line_Rule ,
       decode(rbs.inventory_item_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Inventory_Item ,
       decode(rbs.fob_point_rule,'Code',:rp_code,NULL)   FOB_Point ,
       decode(rbs.related_document_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Related_Document ,
       decode(rbs.agreement_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Agreement,
       decode(rbs.sales_territory_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Sales_Territory,
       decode(rbs.unit_of_measure_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Unit_Of_Measure,
       decode(rbs.ship_via_rule,'Code',:rp_code,NULL)   Freight_Carrier ,
       decode(rbs.salesperson_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Salesperson,
       decode(rbs.sales_credit_type_rule,'Id',:rp_id,'None',:rp_none,
	      'Number',:rp_number,'Segment',:rp_segment, 'Value',:rp_value,NULL)
	       Sales_Credit_Type,
       decode(rbs.sales_credit_rule,'Amount',:rp_amt,'Percent',:rp_per,NULL)
	       Sales_Credit,
	AR_RAXSOL_XMLP_PKG.c_data_not_foundformula(substrb( rbs.name,1,15)) C_DATA_NOT_FOUND,
	&ineffective_date c_date_ineffective,
	AR_RAXSOL_XMLP_PKG.c_last_invoice_numberformula(rbs.auto_trx_numbering_flag, rbs.batch_source_id) c_last_invoice_number
FROM   ra_batch_sources rbs, ar_lookups look1, ar_lookups look4 ,
       ar_lookups look5 , ar_lookups look6,  ar_lookups look7,
       ra_cust_trx_types rac , ra_grouping_rules  rgr
WHERE  rbs.status = look1.lookup_code
AND    look1.lookup_type = 'CODE_STATUS'
AND    rbs.batch_source_type = look6.lookup_code
AND    look6.lookup_type = 'BATCH_SOURCE'
AND    rbs.default_inv_trx_type = rac.cust_trx_type_id(+)
AND    rbs.invalid_tax_rate_rule = look4.lookup_code (+)
AND    nvl(look4.lookup_type ,   'COR/REJ')     =  'COR/REJ'
AND    rbs.invalid_lines_rule    = look5.lookup_code (+)
AND    nvl(look5.lookup_type,   'CRE/REJ')       =  'CRE/REJ'
AND    rbs.gl_date_period_rule    = look7.lookup_code (+)
AND    nvl(look7.lookup_type,    'ADJ/REJ')      =  'ADJ/REJ'
AND    rbs.grouping_rule_id      = rgr.grouping_rule_id (+)
order by  rbs.name
Ask a question