AR Transaction Detail

➜ Back to Library
Description
Categories: BI Publisher
Application: Receivables
Source: Transaction Detail Report (XML)
Short Name: ARXTDR_XML
DB package: AR_ARXTDR_XMLP_PKG
Run AR Transaction Detail and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT       RACT.CUSTOMER_TRX_ID                    CUSTOMER_TRX_ID,
        LOOK1.MEANING                           TRANSACTION_CLASS,
        DECODE(RACT.COMPLETE_FLAG,
                'Y', :YES_MEANING,
                       :NO_MEANING)                     COMPLETE,
        DECODE(RACTT.POST_TO_GL,
                'Y', :YES_MEANING,
                       :NO_MEANING)                     POSTABLE,
        LOOK5.MEANING                           CREDIT_METHOD_FOR_RULES,
        LOOK6.MEANING                   CREDIT_METHOD_FOR_INSTALLMENTS,
        RACT.TRX_NUMBER                 INVOICE_NUMBER,
        RACT.BILL_TO_CUSTOMER_ID                BILL_TO_CUSTOMER_ID,
        RAC_BILL.ACCOUNT_NUMBER         BILL_TO_CUSTOMER_NUMBER,
        SUBSTRB(RAC_BILL_PARTY.PARTY_NAME,1,50)         BILL_TO_CUSTOMER_NAME,
        RAAD_BILL_LOC.ADDRESS1                  BILL_TO_ADDRESS1,
        RAAD_BILL_LOC.ADDRESS2                  BILL_TO_ADDRESS2,
        RAAD_BILL_LOC.ADDRESS3                  BILL_TO_ADDRESS3,
        RAAD_BILL_LOC.ADDRESS4                  BILL_TO_ADDRESS4,
        RAAD_BILL_LOC.CITY                              BILL_TO_CITY,
        NVL(RAAD_BILL_LOC.PROVINCE,
                RAAD_BILL_LOC.STATE)                    BILL_TO_STATE,
        TERR_BILL.TERRITORY_SHORT_NAME          BILL_TO_COUNTRY,
        RAAD_BILL_LOC.POSTAL_CODE                       BILL_TO_POSTAL_CODE,
        SUBSTRB(RACO_BILL_PARTY.PERSON_LAST_NAME,1,50)||' '||
                SUBSTRB(RACO_BILL_PARTY.PERSON_FIRST_NAME,1,40)         BILL_TO_CONTACT_NAME,
        RACT.SHIP_TO_CUSTOMER_ID                SHIP_TO_CUSTOMER_ID,
        RAC_SHIP.ACCOUNT_NUMBER         SHIP_TO_CUSTOMER_NUMBER,
        SUBSTRB(RAC_SHIP_PARTY.PARTY_NAME,1,50) SHIP_TO_CUSTOMER_NAME,
        RAAD_SHIP_LOC.ADDRESS1                  SHIP_TO_ADDRESS1,
        RAAD_SHIP_LOC.ADDRESS2                  SHIP_TO_ADDRESS2,
        RAAD_SHIP_LOC.ADDRESS3                  SHIP_TO_ADDRESS3,
        RAAD_SHIP_LOC.ADDRESS4                  SHIP_TO_ADDRESS4,
        RAAD_SHIP_LOC.CITY                              SHIP_TO_CITY,
        NVL(RAAD_SHIP_LOC.PROVINCE,
                RAAD_SHIP_LOC.STATE )                   SHIP_TO_STATE,
        TERR_SHIP.TERRITORY_SHORT_NAME          SHIP_TO_COUNTRY,
        RAAD_SHIP_LOC.POSTAL_CODE                       SHIP_TO_POSTAL_CODE,
        SUBSTRB(RACO_SHIP_PARTY.PERSON_LAST_NAME,1,50)||' '||
                SUBSTRB(RACO_SHIP_PARTY.PERSON_FIRST_NAME,1,40)         SHIP_TO_CONTACT_NAME,
        SUBSTRB(RAC_SOLD_PARTY.PARTY_NAME,1,50)         SOLD_TO_CUSTOMER_NAME,
        RAC_SOLD.ACCOUNT_NUMBER         SOLD_TO_CUSTOMER_NUMBER,
        RACT.SOLD_TO_CUSTOMER_ID                SOLD_TO_CUSTOMER_ID,
        RACT.REMIT_TO_ADDRESS_ID                REMIT_TO_ADDRESS_ID,
        RAAD_REMIT_LOC.ADDRESS1         REMIT_TO_ADDRESS1,
        RAAD_REMIT_LOC.ADDRESS2         REMIT_TO_ADDRESS2,
        RAAD_REMIT_LOC.ADDRESS3         REMIT_TO_ADDRESS3,
        RAAD_REMIT_LOC.ADDRESS4         REMIT_TO_ADDRESS4,
  RAAD_REMIT_LOC.CITY                     REMIT_TO_CITY,
        RAAD_REMIT_LOC.STATE                    REMIT_TO_STATE,
        RAAD_REMIT_LOC.POSTAL_CODE              REMIT_TO_POSTAL_CODE,
        TERR_REMIT.TERRITORY_SHORT_NAME REMIT_TO_COUNTRY,
        RACT.INTERFACE_HEADER_CONTEXT           FLEX_CONTEXT,
        RACT.TRX_DATE                           INVOICE_DATE,
        LOOK3.MEANING                           INVOICE_STATUS,
        GL_DIST_DATE.GL_DATE                    GL_DATE,
        GL_DIST_DATE.AMOUNT                     CREDITED_AMOUNT,
        ARPS.DUE_DATE                           DUE_DATE,
        RACT.START_DATE_COMMITMENT              START_DATE,
        RACT.END_DATE_COMMITMENT                END_DATE,
        RACT.INVOICE_CURRENCY_CODE              CURRENCY_tr,
        RACT.EXCHANGE_DATE                      EXCHANGE_DATE,
        RACT.EXCHANGE_RATE                      EXCHANGE_RATE,
        RACT.EXCHANGE_RATE_TYPE                 EXCHANGE_RATE_TYPE,
        DECODE(GL_DIST_DATE.ACCOUNT_SET_FLAG,
                'Y', '', null)    RECEIVABLES_ACCOUNT,
        RACTT.TYPE                              TRANSACTION_TYPE_CODE,
        RACTT.NAME                              TRANSACTION_TYPE_NAME,
        RABS.NAME                               BATCH_SOURCE_NAME,
         SOA.NAME                               AGREEMENT_NAME,
        RAT.NAME                                TERM_NAME,
        RAS.NAME                                PRIMARY_SALESPERSON,
        null                      TERRITORY,
        RACT.PURCHASE_ORDER                     PO_NUMBER,
        RACT.PURCHASE_ORDER_REVISION            PO_REVISION,
        RACT.PURCHASE_ORDER_DATE                PO_DATE,
        RACT.INITIAL_CUSTOMER_TRX_ID            COMMITMENT_NUM,
        RACT_COMM.TRX_NUMBER                    COMM_NUMBER,
        LOOK2.MEANING                           PRINTING_OPTION,
        RACT.INTERNAL_NOTES                     SPECIAL_INSTRUCTIONS,
        DECODE(RACTT.ACCOUNTING_AFFECT_FLAG,
                'Y', :YES_MEANING, :NO_MEANING) OPEN_RECEIVABLE,
        RACT.COMMENTS                           COMMENTS,
        RACT.DOC_SEQUENCE_VALUE         DOCUMENT_NUMBER,
        RACT.PREVIOUS_CUSTOMER_TRX_ID           REL_RACT,
        RAR.NAME                                INVOICING_RULE,
        ARRM.NAME                               RECEIPT_METHOD,
decode(apba.instrument_type,'BANKACCOUNT',bk.bank_Account_name,'CREDITCARD',apba.card_issuer_name,'DEBITCARD',apba.card_issuer_name,null) CUSTOMER_BANK_ACCOUNT,
decode(apba.instrument_type,'BANKACCOUNT',apba.account_number,'CREDITCARD',apba.card_number,'DEBITCARD',apba.card_number,null) CUSTOMER_BANK_NUM,
        RACT1.TRX_NUMBER                        CROSS_REF_NUMBER,
        RACT.PRIMARY_SALESREP_ID                PRIMARY_SALESREP_ID,
        RACT.SHIP_VIA                           SHIP_VIA,
        ORGF.DESCRIPTION                        FREIGHT_CARRIER,
        RACT.FOB_POINT                          FOB_POINT,
        LOOK4.MEANING                           FOB_POINT_MEANING,
        RACT.WAYBILL_NUMBER                     WAYBILL_NUMBER,
  RACT.SHIP_DATE_ACTUAL                   SHIP_DATE,
        &lp_query_show_bill cons_bill_number,
	AR_ARXTDR_XMLP_PKG.bill_to_address5formula(RAAD_BILL_LOC.CITY, NVL ( RAAD_BILL_LOC.PROVINCE , RAAD_BILL_LOC.STATE ), RAAD_BILL_LOC.POSTAL_CODE, TERR_BILL.TERRITORY_SHORT_NAME) BILL_TO_ADDRESS5,
	AR_ARXTDR_XMLP_PKG.ship_to_address5formula(RAAD_SHIP_LOC.CITY, NVL ( RAAD_SHIP_LOC.PROVINCE , RAAD_SHIP_LOC.STATE ), RAAD_SHIP_LOC.POSTAL_CODE, TERR_SHIP.TERRITORY_SHORT_NAME) SHIP_TO_ADDRESS5,
	--AR_ARXTDR_XMLP_PKG.d_sold_toformula(RAC_SOLD.ACCOUNT_NUMBER) D_SOLD_TO,
	AR_ARXTDR_XMLP_PKG.d_sold_toformula(RAC_SOLD.ACCOUNT_NUMBER,SUBSTRB(RAC_SOLD_PARTY.PARTY_NAME,1,50)) D_SOLD_TO,
	AR_ARXTDR_XMLP_PKG.d_remit_toformula(RAAD_REMIT_LOC.ADDRESS1, RAAD_REMIT_LOC.ADDRESS2, RAAD_REMIT_LOC.ADDRESS3, RAAD_REMIT_LOC.ADDRESS4, RAAD_REMIT_LOC.CITY, RAAD_REMIT_LOC.STATE, RAAD_REMIT_LOC.POSTAL_CODE, TERR_REMIT.TERRITORY_SHORT_NAME) D_REMIT_TO,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_territory', 'AR', 'CT#', 101, NULL, RATT.TERRITORY_ID, 'ALL', 'Y', 'VALUE') D_TERRITORY,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('d_receivables_account', 'SQLGL', 'GL#', glcc_rcvbl.CHART_OF_ACCOUNTS_ID, NULL, glcc_rcvbl.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') D_RECEIVABLES_ACCOUNT,
	AR_ARXTDR_XMLP_PKG.d_locationformula() D_LOCATION,
	AR_ARXTDR_XMLP_PKG.tr_inv_amountformula(:TR_LN_EXTD_AMOUNT, :TR_TX_EXTD_AMOUNT, :TR_FR_EXTD_AMOUNT) TR_INV_AMOUNT,
	AR_ARXTDR_XMLP_PKG.trx_transaction_flexformula(RACT.CUSTOMER_TRX_ID) TRX_TRANSACTION_FLEX
FROM    AR_LOOKUPS                      LOOK1,
        AR_LOOKUPS                      LOOK2,
        AR_LOOKUPS                      LOOK3,
        AR_LOOKUPS                      LOOK4,
        AR_LOOKUPS                      LOOK5,
        AR_LOOKUPS                      LOOK6,
        RA_CUST_TRX_TYPES               RACTT,
        HZ_CUST_ACCOUNTS                RAC_BILL,
        HZ_PARTIES                      RAC_BILL_PARTY,
        HZ_CUST_SITE_USES               RASU_BILL,
        HZ_CUST_ACCT_SITES              RAAD_BILL,
        HZ_PARTY_SITES                  RAAD_BILL_PS,
        HZ_LOCATIONS                    RAAD_BILL_LOC,
        FND_TERRITORIES_VL              TERR_BILL,
        HZ_CUST_ACCOUNT_ROLES           RACO_BILL,
        HZ_PARTIES                      RACO_BILL_PARTY,
        HZ_RELATIONSHIPS                        RACO_BILL_REL,
        HZ_CUST_ACCOUNTS                RAC_SHIP,
        HZ_PARTIES                      RAC_SHIP_PARTY,
        HZ_CUST_SITE_USES                       RASU_SHIP,
        HZ_CUST_ACCT_SITES              RAAD_SHIP,
        HZ_PARTY_SITES                  RAAD_SHIP_PS,
        HZ_LOCATIONS                    RAAD_SHIP_LOC,
        FND_TERRITORIES_VL              TERR_SHIP,
        HZ_CUST_ACCOUNT_ROLES           RACO_SHIP,
        HZ_PARTIES                      RACO_SHIP_PARTY,
        HZ_RELATIONSHIPS                        RACO_SHIP_REL,
        HZ_CUST_ACCOUNTS                RAC_SOLD,
        HZ_PARTIES                      RAC_SOLD_PARTY,
        HZ_CUST_ACCT_SITES              RAAD_REMIT,
        HZ_PARTY_SITES                  RAAD_REMIT_PS,
        HZ_LOCATIONS                    RAAD_REMIT_LOC,
        FND_TERRITORIES_VL              TERR_REMIT,
                ar_xla_ctlgd_lines_v                        GL_DIST_DATE,
        AR_PAYMENT_SCHEDULES            ARPS,
        &lp_table_show_bill
        GL_CODE_COMBINATIONS            GLCC_RCVBL,
        RA_BATCH_SOURCES                RABS,
        SO_AGREEMENTS                   SOA,
        RA_TERMS                        RAT,
        RA_SALESREPS                    RAS,
        RA_TERRITORIES                  RATT,
  RA_RULES                        RAR,
        AR_RECEIPT_METHODS              ARRM,
        RA_CUSTOMER_TRX         RACT,
        RA_CUSTOMER_TRX         RACT1,
        RA_CUSTOMER_TRX         RACT_COMM,
        ORG_FREIGHT                     ORGF,
        IBY_TRXN_EXTENSIONS_V                 extn,
        IBY_FNDCPT_PAYER_ASSGN_INSTR_V        apba,
        iby_ext_bank_accounts_v   bk
WHERE   LOOK1.LOOKUP_TYPE(+)     = 'INV/CM'
AND     LOOK1.LOOKUP_CODE(+)     = RACTT.TYPE
AND     LOOK2.LOOKUP_TYPE(+)     = 'INVOICE_PRINT_OPTIONS'
AND     LOOK2.LOOKUP_CODE(+)     = RACT.PRINTING_OPTION
AND     LOOK3.LOOKUP_TYPE(+)     = 'INVOICE_TRX_STATUS'
AND     LOOK3.LOOKUP_CODE(+)     = RACT.STATUS_TRX
AND     LOOK4.LOOKUP_TYPE(+)     = 'FOB'
AND     LOOK4.LOOKUP_CODE(+)     = RACT.FOB_POINT
AND     LOOK5.LOOKUP_TYPE(+)     = 'CREDIT_METHOD_FOR_RULES'
AND     LOOK5.LOOKUP_CODE(+)     = RACT.CREDIT_METHOD_FOR_RULES
AND     LOOK6.LOOKUP_TYPE(+)     = 'CREDIT_METHOD_FOR_INSTALLMENTS'
AND     LOOK6.LOOKUP_CODE(+)     = RACT.CREDIT_METHOD_FOR_INSTALLMENTS
AND     RACTT.CUST_TRX_TYPE_ID   = RACT.CUST_TRX_TYPE_ID
AND     RAC_BILL.CUST_ACCOUNT_ID(+)  = RACT.BILL_TO_CUSTOMER_ID
AND     RAC_BILL.PARTY_ID       = RAC_BILL_PARTY.PARTY_ID(+)
AND     RAAD_BILL.CUST_ACCT_SITE_ID(+)  = RASU_BILL.CUST_ACCT_SITE_ID
AND     RAAD_BILL.PARTY_SITE_ID = RAAD_BILL_PS.PARTY_SITE_ID(+)
AND     RAAD_BILL_LOC.LOCATION_ID(+) = RAAD_BILL_PS.LOCATION_ID
AND     RAAD_BILL_LOC.COUNTRY            = TERR_BILL.TERRITORY_CODE(+)
AND     RASU_BILL.SITE_USE_ID(+) = RACT.BILL_TO_SITE_USE_ID
AND     RACO_BILL.CUST_ACCOUNT_ROLE_ID(+)  = RACT.BILL_TO_CONTACT_ID
AND     RACO_BILL.PARTY_ID = RACO_BILL_REL.PARTY_ID(+)
AND     RACO_BILL_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND     RACO_BILL_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND     RACO_BILL_REL.DIRECTIONAL_FLAG(+) = 'F'
AND     RACO_BILL.ROLE_TYPE(+) = 'CONTACT'
AND     RACO_BILL_REL.SUBJECT_ID = RACO_BILL_PARTY.PARTY_ID(+)
AND     RAC_SHIP.CUST_ACCOUNT_ID(+)  = RACT.SHIP_TO_CUSTOMER_ID
AND     RAC_SHIP.PARTY_ID = RAC_SHIP_PARTY.PARTY_ID(+)
AND     RAAD_SHIP.CUST_ACCT_SITE_ID(+)  = RASU_SHIP.CUST_ACCT_SITE_ID
AND     RAAD_SHIP.PARTY_SITE_ID = RAAD_SHIP_PS.PARTY_SITE_ID(+)
AND     RAAD_SHIP_LOC.LOCATION_ID(+) = RAAD_SHIP_PS.LOCATION_ID
AND     RAAD_SHIP_LOC.COUNTRY           = TERR_SHIP.TERRITORY_CODE(+)
AND     RASU_SHIP.SITE_USE_ID(+) = RACT.SHIP_TO_SITE_USE_ID
AND     RACO_SHIP.CUST_ACCOUNT_ROLE_ID(+)  = RACT.SHIP_TO_CONTACT_ID
AND     RACO_SHIP.PARTY_ID = RACO_SHIP_REL.PARTY_ID(+)
AND     RACO_SHIP.ROLE_TYPE(+) = 'CONTACT'
AND     RACO_SHIP_REL.SUBJECT_ID = RACO_SHIP_PARTY.PARTY_ID(+)
AND     RACO_SHIP_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND     RACO_SHIP_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND     RACO_SHIP_REL.DIRECTIONAL_FLAG(+) = 'F'
AND     RAC_SOLD.CUST_ACCOUNT_ID(+)  = RACT.SOLD_TO_CUSTOMER_ID
AND     RAC_SOLD.PARTY_ID       = RAC_SOLD_PARTY.PARTY_ID(+)
AND     RAAD_REMIT.CUST_ACCT_SITE_ID(+) = RACT.REMIT_TO_ADDRESS_ID
AND     RAAD_REMIT.PARTY_SITE_ID        = RAAD_REMIT_PS.PARTY_SITE_ID(+)
AND     RAAD_REMIT_LOC.LOCATION_ID(+) = RAAD_REMIT_PS.LOCATION_ID
AND     RAAD_REMIT_LOC.COUNTRY           = TERR_REMIT.TERRITORY_CODE(+)
AND     GL_DIST_DATE.CUSTOMER_TRX_ID(+)   = RACT.CUSTOMER_TRX_ID
AND     GL_DIST_DATE.ACCOUNT_CLASS(+)     = 'REC'
AND     GL_DIST_DATE.LATEST_REC_FLAG     = 'Y'
AND     ARPS.CUSTOMER_TRX_ID(+)           = RACT.CUSTOMER_TRX_ID
AND     ARPS.TERMS_SEQUENCE_NUMBER(+)     = 1
AND     GLCC_RCVBL.CODE_COMBINATION_ID(+) = GL_DIST_DATE.CODE_COMBINATION_ID
AND     RABS.BATCH_SOURCE_ID(+)           = RACT.BATCH_SOURCE_ID
AND     SOA.AGREEMENT_ID(+)               = RACT.AGREEMENT_ID
AND     RAT.TERM_ID(+)                    = RACT.TERM_ID
AND     RAS.SALESREP_ID(+)                = RACT.PRIMARY_SALESREP_ID
AND     RATT.TERRITORY_ID(+)              = RACT.TERRITORY_ID
AND     RAR.RULE_ID(+)                    = RACT.INVOICING_RULE_ID
AND     ARRM.RECEIPT_METHOD_ID(+)         = RACT.RECEIPT_METHOD_ID
AND    extn.trxn_extension_id(+) = ract.payment_trxn_extension_id
and    extn.instrument_id = apba.instrument_id(+)
AND  extn.instr_assignment_id = apba.instr_assignment_id(+)
and    bk.bank_account_id(+) = decode(apba.instrument_type,'BANKACCOUNT',apba.instrument_id,null)
AND     RACT1.CUSTOMER_TRX_ID(+)          = RACT.RELATED_CUSTOMER_TRX_ID
AND     RACT_COMM.CUSTOMER_TRX_ID(+)     = RACT.INITIAL_CUSTOMER_TRX_ID
AND     (RACT.TRX_NUMBER  BETWEEN :P_INVOICE_NUM_LOW AND  :P_INVOICE_NUM_HIGH)
/*AND     ((RACTT.TYPE = :P_TRANSACTION_TYPE) OR
                        (:P_TRANSACTION_TYPE = 'ALL'))*/
AND     ((RACTT.TYPE = :P_TRANSACTION_TYPE_DUMMY) OR
                        (:P_TRANSACTION_TYPE_DUMMY = 'ALL'))
AND     ORGF.FREIGHT_CODE(+)    = RACT.SHIP_VIA
AND     ORGF.ORGANIZATION_ID(+) = :ITEM_FLEX_STRUCTURE
&lp_where_show_bill
ORDER BY
        RACT.TRX_NUMBER
Parameter Name SQL text Validation
Transaction Number Low 
 
 LOV Oracle
Transaction Number High 
 
 LOV Oracle
Transaction Class 
 
 LOV Oracle
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: