Thursday, August 9, 2012

Oracle Receivables-Query to Extract Transaction Sources

Query :
/* Formatted on 8/9/2012 7:31:10 PM (QP5 v5.163.1008.3004) */
SELECT rbsa.NAME,
       DECODE (rbsa.BATCH_SOURCE_TYPE, 'INV', 'Manual', 'Imported') "Type",
       rbsa.Description,
       DECODE (rbsa.STATUS, 'A', 'Checked', 'Unchecked') "Active",
       rbsa.START_DATE,
       rbsa.END_DATE,
       DECODE (rbsa.AUTO_TRX_NUMBERING_FLAG, 'Y', 'Checked', 'Unchecked')
          "AUTOMTC_TRANS_NUM",
       LAST_BATCH_NUM "LAST NUMBER USED",
       DEFAULT_REFERENCE "REFERENCE FIELD DEFAULT VALUE",
       rctty.NAME "STANDARD TRANSACTION TYPE NAME",
       rbsa.INVALID_TAX_RATE_RULE,
       rbsa.INVALID_LINES_RULE,
       rbsa.GL_DATE_PERIOD_RULE,
       rgr.NAME "GROUPING RULE",
       DECODE (rbsa.CREATE_CLEARING_FLAG, 'Y', 'Checked', 'Unchecked')
          "CREATE CLEARING",
       DECODE (rbsa.SALES_CREDIT_TYPE_RULE, 'Y', 'Checked', 'Unchecked')
          "ALLOW SALES CREDIT",
       rbsa.SOLD_CUSTOMER_RULE,
       rbsa.BILL_CUSTOMER_RULE,
       rbsa.BILL_ADDRESS_RULE,
       rbsa.BILL_CONTACT_RULE,
       rbsa.SHIP_CUSTOMER_RULE,
       rbsa.SHIP_ADDRESS_RULE,
       rbsa.SHIP_CONTACT_RULE,
       rbsa.RECEIPT_METHOD_RULE "PAYMENT_METHOD_RULE",
       rbsa.CUSTOMER_BANK_ACCOUNT_RULE "CUSTOMER BANK ACCOUNT",
       rbsa.INVOICING_RULE_RULE "Accounting_Invoice_Rule",
       rbsa.ACCOUNTING_RULE_RULE "Accounting Rule",
       rbsa.ACCOUNTING_FLEXFIELD_RULE "ACCOUNTING FLEXFIELD",
       DECODE (rbsa.DERIVE_DATE_FLAG, 'Y', 'Checked', 'Unchecked')
          "DERIVE DATE",
       rbsa.TERM_RULE "Payment Rule",
       rbsa.REV_ACC_ALLOCATION_RULE "Revenue Account Allocation",
       rbsa.CUST_TRX_TYPE_RULE "OtherInfo-TransactionType",
       rbsa.MEMO_REASON_RULE "OtherInfo-MemoLineRule",
       rbsa.SALES_TERRITORY_RULE "OtherInfo-SalesTerritory",
       rbsa.INVENTORY_ITEM_RULE "OtherInfo-InventoryItem",
       rbsa.UNIT_OF_MEASURE_RULE "OtherInfo-UOM",
       rbsa.FOB_POINT_RULE "OtherInfoFobPoint",
       rbsa.SHIP_VIA_RULE "OtherInfo-FreightCarrier",
       rbsa.RELATED_DOCUMENT_RULE "OtherInfo-DocumentRule",
       rbsa.SALESPERSON_RULE "SalesCreditVal-SalesPerson",
       rbsa.SALES_CREDIT_TYPE_RULE "SalesCreditVal-SalesCreditType",
       rbsa.SALES_CREDIT_RULE "SalesCreditRule"
  FROM ra_batch_sources_all rbsa,
       RA_CUST_TRX_TYPES_ALL rctty,
       RA_GROUPING_RULES rgr
 WHERE     rctty.CUST_TRX_TYPE_ID = rbsa.DEFAULT_INV_TRX_TYPE
       AND rgr.GROUPING_RULE_ID(+) = rbsa.GROUPING_RULE_ID
       AND rbsa.name = :P_name
Posted by at
Labels:

3 comments:

  1. RakshithMarch 31, 2013 at 11:28 PM

    Dude, Good effort! But get rid of this theme man, very annoying.

    -Rakshith Chengappa

    ReplyDelete
  2. thomas paulJune 2, 2013 at 6:09 AM

    Very good Blog , please change the theme to some white back ground will be very helpful

    ReplyDelete
  3. UnknownFebruary 19, 2019 at 11:33 AM

    More Fields added
    SELECT
    rbsa.org_id,
    rbsa.legal_entity_id,
    rbsa.name,
    DECODE(rbsa.batch_source_type, 'INV', 'Manual', 'Imported') "Type",
    rbsa.description,
    DECODE(rbsa.status, 'A', 'Checked', 'Unchecked') "Active",
    rbsa.start_date,
    rbsa.end_date,
    rbsa.auto_batch_numbering_flag,
    rbsa.last_batch_num "BATCH_LAST NUMBER",
    DECODE(rbsa.auto_trx_numbering_flag, 'Y', 'Checked', 'Unchecked') "AUTOMTC_TRANS_NUM",
    rbsa.copy_doc_number_flag AS copy_doc_num_tran_num,
    rbsa.allow_duplicate_trx_num_flag,
    rbsa.copy_inv_tidff_to_cm_flag AS copy_tra_info_flex_cm,
    rbsa.gen_line_level_bal_flag AS genlinelevelbalances,
    rbsa.receipt_handling_option,
    rbsa.default_reference "REFERENCE FIELD DEFAULT VALUE",
    rctty.name "STANDARD TRANSACTION TYPE",
    rbsa1.name AS cmbatchsource,
    rbsa.invalid_lines_rule AS invalid_line,
    rbsa.gl_date_period_rule AS gl_date_in_closed_period,
    rgr.name "GROUPING RULE",
    DECODE(rbsa.create_clearing_flag, 'Y', 'Checked', 'Unchecked') "CREATE CLEARING",
    DECODE(rbsa.allow_sales_credit_flag, 'Y', 'Checked', 'Unchecked') "ALLOW SALES CREDIT",
    DECODE(rbsa.payment_det_def_hierarchy, 'PARENT', 'Parent of Bill-To Customer', 'Bill-To Customer') payment_det_def_hierarchy,
    rbsa.sold_customer_rule,
    rbsa.bill_customer_rule,
    rbsa.bill_address_rule,
    rbsa.bill_contact_rule,
    rbsa.ship_customer_rule,
    rbsa.ship_address_rule,
    rbsa.ship_contact_rule,
    rbsa.receipt_method_rule "PAYMENT_METHOD_RULE",
    rbsa.customer_bank_account_rule "CUSTOMER BANK ACCOUNT",
    rbsa.invoicing_rule_rule "Accounting_Invoice_Rule",
    rbsa.accounting_rule_rule "Accounting Rule",
    rbsa.accounting_flexfield_rule "ACCOUNTING FLEXFIELD",
    DECODE(rbsa.derive_date_flag, 'Y', 'Checked', 'Unchecked') "DERIVE DATE",
    rbsa.term_rule "Payment Rule",
    rbsa.rev_acc_allocation_rule "Revenue Account Allocation",
    rbsa.cust_trx_type_rule "OtherInfo-TransactionType",
    rbsa.memo_reason_rule "OtherInfo-MemoLineRule",
    rbsa.sales_territory_rule "OtherInfo-SalesTerritory",
    rbsa.inventory_item_rule "OtherInfo-InventoryItem",
    rbsa.unit_of_measure_rule "OtherInfo-UOM",
    rbsa.fob_point_rule "OtherInfoFobPoint",
    rbsa.ship_via_rule "OtherInfo-FreightCarrier",
    rbsa.related_document_rule "OtherInfo-DocumentRule",
    rbsa.salesperson_rule "SalesCreditVal-SalesPerson",
    rbsa.sales_credit_type_rule "SalesCreditVal-SalesCreditType",
    rbsa.sales_credit_rule "SalesCreditRule"
    FROM
    ra_batch_sources_all rbsa,
    ra_batch_sources_all rbsa1,
    ra_cust_trx_types_all rctty,
    ra_grouping_rules rgr
    WHERE
    rctty.cust_trx_type_id (+) = rbsa.default_inv_trx_type
    AND rbsa.org_id = rctty.org_id (+)
    AND rgr.grouping_rule_id (+) = rbsa.grouping_rule_id
    AND rbsa.credit_memo_batch_source_id = rbsa1.batch_source_id (+)

    ReplyDelete

Subscribe to: Post Comments (Atom)