收款录入后产生的信息代码,OracleEBS代码库,德仔网

代码语言
代码分类
【OracleEBS】收款录入后产生的信息
作者:专注Oracle EBS / 发布于2012/11/29/ 862
--收款主表
SELECT *
  FROM AR_CASH_RECEIPTS_ALL ACRA
 WHERE ACRA.RECEIPT_NUMBER = '20120106001';

--收款历史记录表
SELECT *
  FROM AR_CASH_RECEIPT_HISTORY_ALL ACRH
 WHERE ACRH.CASH_RECEIPT_ID = 304387;

--收款事务处理表
SELECT *
  FROM AR_RECEIVABLE_APPLICATIONS_ALL ARA
 WHERE ARA.CASH_RECEIPT_ID = 304387;

--收款分配表
--收款录入时产生两条记录，SOURCE_ID分别对应 
--SOURCE_TABLE为 'RA' 时SOURCE_ID对应的是AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID,为CRH时SOURCE_ID对应的是AR_CASH_RECEIPT_HISTORY_ALL.CASH_RECEIPT_HISTORY_ID
--下面SQL获取的是收款录入时产生的未核销科目及收款核销时产生的应收账款和未核销科目
SELECT *
  FROM AR_DISTRIBUTIONS_ALL ADA
 WHERE ADA.SOURCE_ID IN
       (SELECT ARA.RECEIVABLE_APPLICATION_ID
          FROM AR_RECEIVABLE_APPLICATIONS_ALL ARA
         WHERE ARA.CASH_RECEIPT_ID = 304387)
   AND ADA.SOURCE_TABLE = 'RA';
--收款录入时产生两条分录：现金和未核销，下面语句获取的是现金科目
SELECT *
  FROM AR_DISTRIBUTIONS_ALL ADA
 WHERE ADA.SOURCE_ID IN
       (SELECT ACRH.CASH_RECEIPT_HISTORY_ID
          FROM AR_CASH_RECEIPT_HISTORY_ALL ACRH
         WHERE ACRH.CASH_RECEIPT_ID = 304387)
   AND ADA.SOURCE_TABLE = 'CRH';

--应收发票收货方 收单方 到期日等

SELECT CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
      ,CT.TRX_NUMBER TRX_NUMBER
      ,CT.OLD_TRX_NUMBER OLD_TRX_NUMBER
      ,CT_REL.TRX_NUMBER CT_RELATED_TRX_NUMBER
      ,CT.RECURRED_FROM_TRX_NUMBER CT_MODEL_TRX_NUMBER
      ,CT.TRX_DATE TRX_DATE
      ,ARPT_SQL_FUNC_UTIL.GET_FIRST_REAL_DUE_DATE(CT.CUSTOMER_TRX_ID
                                                 ,CT.TERM_ID
                                                 ,CT.TRX_DATE) TERM_DUE_DATE --到期日
      ,CT.PREVIOUS_CUSTOMER_TRX_ID PREVIOUS_CUSTOMER_TRX_ID
      ,CT.INITIAL_CUSTOMER_TRX_ID INITIAL_CUSTOMER_TRX_ID
      ,CT.RELATED_BATCH_SOURCE_ID RELATED_BATCH_SOURCE_ID
      ,CT.RELATED_CUSTOMER_TRX_ID RELATED_CUSTOMER_TRX_ID
      ,CT.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
      ,CT.BATCH_ID BATCH_ID
      ,CT.BATCH_SOURCE_ID BATCH_SOURCE_ID
      ,CT.REASON_CODE REASON_CODE
      ,CT.TERM_ID TERM_ID
      ,CT.PRIMARY_SALESREP_ID PRIMARY_SALESREP_ID
      ,CT.AGREEMENT_ID AGREEMENT_ID
      ,CT.CREDIT_METHOD_FOR_RULES CREDIT_METHOD_FOR_RULES
      ,CT.CREDIT_METHOD_FOR_INSTALLMENTS CREDIT_METHOD_FOR_INSTALLMENTS
      ,CT.RECEIPT_METHOD_ID RECEIPT_METHOD_ID
      ,CT.INVOICING_RULE_ID INVOICING_RULE_ID
      ,CT.SHIP_VIA SHIP_VIA
      ,CT.FOB_POINT FOB_POINT
      ,CT.FINANCE_CHARGES
      ,CT.COMPLETE_FLAG COMPLETE_FLAG
      ,CT.CUSTOMER_BANK_ACCOUNT_ID CUSTOMER_BANK_ACCOUNT_ID
      ,CT.RECURRED_FROM_TRX_NUMBER RECURRED_FROM_TRX_NUMBER
      ,CT.STATUS_TRX STATUS_TRX
      ,CT.DEFAULT_TAX_EXEMPT_FLAG DEFAULT_TAX_EXEMPT_FLAG
      ,CT.SOLD_TO_CUSTOMER_ID SOLD_TO_CUSTOMER_ID
      ,CT.SOLD_TO_SITE_USE_ID SOLD_TO_SITE_USE_ID
      ,CT.SOLD_TO_CONTACT_ID SOLD_TO_CONTACT_ID
      ,CT.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID
      ,CT.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
      ,RAA_BILL.CUST_ACCT_SITE_ID RAA_BILL_TO_ADDRESS_ID
      ,CT.BILL_TO_CONTACT_ID BILL_TO_CONTACT_ID
      ,RAC_BILL_PARTY.JGZZ_FISCAL_CODE BILL_TO_TAXPAYER_ID
      ,CT.SHIP_TO_CUSTOMER_ID
      ,CT.SHIP_TO_SITE_USE_ID
      ,RAA_SHIP.CUST_ACCT_SITE_ID RAA_SHIP_TO_ADDRESS_ID
      ,CT.SHIP_TO_CONTACT_ID
      ,RAC_SHIP_PARTY.JGZZ_FISCAL_CODE SHIP_TO_TAXPAYER_ID
      ,CT.REMIT_TO_ADDRESS_ID
      ,CT.INVOICE_CURRENCY_CODE
      ,CT.CREATED_FROM
      ,CT.SET_OF_BOOKS_ID
      ,CT.PRINTING_ORIGINAL_DATE
      ,CT.PRINTING_LAST_PRINTED
      ,CT.PRINTING_OPTION
      ,CT.PRINTING_COUNT
      ,CT.PRINTING_PENDING
      ,CT.LAST_PRINTED_SEQUENCE_NUM
      ,CT.PURCHASE_ORDER
      ,CT.PURCHASE_ORDER_REVISION
      ,CT.PURCHASE_ORDER_DATE
      ,CT.CUSTOMER_REFERENCE
      ,CT.CUSTOMER_REFERENCE_DATE
      ,CT.COMMENTS
      ,CT.INTERNAL_NOTES
      ,CT.EXCHANGE_RATE_TYPE
      ,CT.EXCHANGE_DATE
      ,CT.EXCHANGE_RATE
      ,CT.TERRITORY_ID
      ,CT.END_DATE_COMMITMENT
      ,CT.START_DATE_COMMITMENT
      ,CT.ORIG_SYSTEM_BATCH_NAME
      ,CT.SHIP_DATE_ACTUAL
      ,CT.WAYBILL_NUMBER
      ,CT.DOC_SEQUENCE_ID
      ,CT.DOC_SEQUENCE_VALUE
      ,CT.PAYING_CUSTOMER_ID
      ,CT.PAYING_SITE_USE_ID
      ,CT.DEFAULT_USSGL_TRANSACTION_CODE
      ,CT.LAST_UPDATE_DATE
      ,CT.LAST_UPDATED_BY
      ,CT.CREATION_DATE
      ,CT.CREATED_BY
      ,CT.LAST_UPDATE_LOGIN
      ,CT.REQUEST_ID
      ,RAC_BILL_PARTY.PARTY_NAME RAC_BILL_TO_CUSTOMER_NAME --收单方
      ,RAC_BILL.ACCOUNT_NUMBER RAC_BILL_TO_CUSTOMER_NUM
      ,SU_BILL.LOCATION SU_BILL_TO_LOCATION --收单地点
      ,RAA_BILL_LOC.ADDRESS1 RAA_BILL_TO_ADDRESS1 --收单地址
      ,RAA_BILL_LOC.ADDRESS2 RAA_BILL_TO_ADDRESS2
      ,RAA_BILL_LOC.ADDRESS3 RAA_BILL_TO_ADDRESS3_DB
      ,DECODE(RAA_BILL.CUST_ACCT_SITE_ID
             ,NULL
             ,NULL
             ,ARH_ADDR_PKG.FORMAT_LAST_ADDRESS_LINE(RAA_BILL_LOC.ADDRESS_STYLE
                                                   ,RAA_BILL_LOC.ADDRESS3
                                                   ,RAA_BILL_LOC.ADDRESS4
                                                   ,RAA_BILL_LOC.CITY
                                                   ,RAA_BILL_LOC.COUNTY
                                                   ,RAA_BILL_LOC.STATE
                                                   ,RAA_BILL_LOC.PROVINCE
                                                   ,FT_BILL.TERRITORY_SHORT_NAME
                                                   ,RAA_BILL_LOC.POSTAL_CODE)) RAA_BILL_TO_ADDRESS3
      ,RAA_BILL_LOC.CITY RAA_BILL_TO_CITY
      ,RAA_BILL_LOC.COUNTY RAA_BILL_TO_COUNTY
      ,RAA_BILL_LOC.STATE RAA_BILL_TO_STATE
      ,RAA_BILL_LOC.PROVINCE RAA_BILL_TO_PROVINCE
      ,RAA_BILL_LOC.POSTAL_CODE RAA_BILL_TO_POSTAL_CODE
      ,FT_BILL.TERRITORY_SHORT_NAME FT_BILL_TO_COUNTRY
      ,DECODE(RAA_BILL.CUST_ACCT_SITE_ID
             ,NULL
             ,NULL
             ,ARH_ADDR_PKG.ARXTW_FORMAT_ADDRESS(RAA_BILL_LOC.ADDRESS_STYLE
                                               ,RAA_BILL_LOC.ADDRESS1
                                               ,RAA_BILL_LOC.ADDRESS2
                                               ,RAA_BILL_LOC.ADDRESS3
                                               ,RAA_BILL_LOC.ADDRESS4
                                               ,RAA_BILL_LOC.CITY
                                               ,RAA_BILL_LOC.COUNTY
                                               ,RAA_BILL_LOC.STATE
                                               ,RAA_BILL_LOC.PROVINCE
                                               ,RAA_BILL_LOC.POSTAL_CODE
                                               ,FT_BILL.TERRITORY_SHORT_NAME)) RAA_BILL_TO_CONCAT_ADDRESS
       
      ,DECODE(SUBSTRB(RACO_BILL_PARTY.PERSON_LAST_NAME
                     ,1
                     ,50)
             ,NULL
             ,SUBSTRB(RACO_BILL_PARTY.PERSON_FIRST_NAME
                     ,1
                     ,40)
             ,SUBSTRB(RACO_BILL_PARTY.PERSON_LAST_NAME
                     ,1
                     ,50) || ', ' || SUBSTRB(RACO_BILL_PARTY.PERSON_FIRST_NAME
                                            ,1
                                            ,40)) RACO_BILL_TO_CONTACT_NAME
      ,RAC_SHIP_PARTY.PARTY_NAME RAC_SHIP_TO_CUSTOMER_NAME --收货方
      ,RAC_SHIP.ACCOUNT_NUMBER RAC_SHIP_TO_CUSTOMER_NUM
      ,SU_SHIP.LOCATION SU_SHIP_TO_LOCATION --收货地点
      ,RAA_SHIP_LOC.ADDRESS1 RAA_SHIP_TO_ADDRESS1 --收货地址
      ,RAA_SHIP_LOC.ADDRESS2 RAA_SHIP_TO_ADDRESS2
      ,RAA_SHIP_LOC.ADDRESS3 RAA_SHIP_TO_ADDRESS3_DB
      ,DECODE(RAA_SHIP.CUST_ACCT_SITE_ID
             ,NULL
             ,NULL
             ,ARH_ADDR_PKG.FORMAT_LAST_ADDRESS_LINE(RAA_SHIP_LOC.ADDRESS_STYLE
                                                   ,RAA_SHIP_LOC.ADDRESS3
                                                   ,RAA_SHIP_LOC.ADDRESS4
                                                   ,RAA_SHIP_LOC.CITY
                                                   ,RAA_SHIP_LOC.COUNTY
                                                   ,RAA_SHIP_LOC.STATE
                                                   ,RAA_SHIP_LOC.PROVINCE
                                                   ,FT_SHIP.TERRITORY_SHORT_NAME
                                                   ,RAA_SHIP_LOC.POSTAL_CODE)) RAA_SHIP_TO_ADDRESS3
       
      ,RAA_SHIP_LOC.CITY RAA_SHIP_TO_CITY
      ,RAA_SHIP_LOC.COUNTY RAA_SHIP_TO_COUNTY
      ,RAA_SHIP_LOC.STATE RAA_SHIP_TO_STATE
      ,RAA_SHIP_LOC.PROVINCE RAA_SHIP_TO_PROVINCE
      ,RAA_SHIP_LOC.POSTAL_CODE RAA_SHIP_TO_POSTAL_CODE
      ,FT_SHIP.TERRITORY_SHORT_NAME FT_SHIP_TO_COUNTRY
      ,DECODE(RAA_SHIP.CUST_ACCT_SITE_ID
             ,NULL
             ,NULL
             ,ARH_ADDR_PKG.ARXTW_FORMAT_ADDRESS(RAA_SHIP_LOC.ADDRESS_STYLE
                                               ,RAA_SHIP_LOC.ADDRESS1
                                               ,RAA_SHIP_LOC.ADDRESS2
                                               ,RAA_SHIP_LOC.ADDRESS3
                                               ,RAA_SHIP_LOC.ADDRESS4
                                               ,RAA_SHIP_LOC.CITY
                                               ,RAA_SHIP_LOC.COUNTY
                                               ,RAA_SHIP_LOC.STATE
                                               ,RAA_SHIP_LOC.PROVINCE
                                               ,RAA_SHIP_LOC.POSTAL_CODE
                                               ,FT_SHIP.TERRITORY_SHORT_NAME)) RAA_SHIP_TO_CONCAT_ADDRESS
      ,DECODE(SUBSTRB(RACO_SHIP_PARTY.PERSON_LAST_NAME
                     ,1
                     ,50)
             ,NULL
             ,SUBSTRB(RACO_SHIP_PARTY.PERSON_FIRST_NAME
                     ,1
                     ,40)
             ,SUBSTRB(RACO_SHIP_PARTY.PERSON_LAST_NAME
                     ,1
                     ,50) || ', ' || SUBSTRB(RACO_SHIP_PARTY.PERSON_FIRST_NAME
                                            ,1
                                            ,40)) RACO_SHIP_TO_CONTACT_NAME
       
      ,RAC_SOLD_PARTY.PARTY_NAME RAC_SOLD_TO_CUSTOMER_NAME --采购方
      ,RAC_SOLD.ACCOUNT_NUMBER RAC_SOLD_TO_CUSTOMER_NUM
      ,RAC_PAYING_PARTY.PARTY_NAME RAC_PAYING_CUSTOMER_NAME --付款客户
      ,RAC_PAYING.ACCOUNT_NUMBER RAC_PAYING_CUSTOMER_NUM
      ,SU_PAYING.LOCATION SU_PAYING_CUSTOMER_LOCATION
      ,RAA_REMIT_LOC.ADDRESS1 RAA_REMIT_TO_ADDRESS1
      ,RAA_REMIT_LOC.ADDRESS2 RAA_REMIT_TO_ADDRESS2
      ,RAA_REMIT_LOC.ADDRESS3 RAA_REMIT_TO_ADDRESS3_DB
      ,DECODE(RAA_REMIT.CUST_ACCT_SITE_ID
             ,NULL
             ,NULL
             ,ARH_ADDR_PKG.FORMAT_LAST_ADDRESS_LINE(RAA_REMIT_LOC.ADDRESS_STYLE
                                                   ,RAA_REMIT_LOC.ADDRESS3
                                                   ,RAA_REMIT_LOC.ADDRESS4
                                                   ,RAA_REMIT_LOC.CITY
                                                   ,RAA_REMIT_LOC.COUNTY
                                                   ,RAA_REMIT_LOC.STATE
                                                   ,RAA_REMIT_LOC.PROVINCE
                                                   ,FT_REMIT.TERRITORY_SHORT_NAME
                                                   ,RAA_REMIT_LOC.POSTAL_CODE)) RAA_REMIT_TO_ADDRESS3
       
      ,RAA_REMIT_LOC.CITY RAA_REMIT_TO_CITY
      ,RAA_REMIT_LOC.COUNTY RAA_REMIT_TO_COUNTY
      ,RAA_REMIT_LOC.STATE RAA_REMIT_TO_STATE
      ,RAA_REMIT_LOC.PROVINCE RAA_REMIT_TO_PROVINCE
      ,RAA_REMIT_LOC.POSTAL_CODE RAA_REMIT_TO_POSTAL_CODE
      ,FT_REMIT.TERRITORY_SHORT_NAME FT_REMIT_TO_COUNTRY
      ,DECODE(RAA_REMIT.CUST_ACCT_SITE_ID
             ,NULL
             ,NULL
             ,ARH_ADDR_PKG.ARXTW_FORMAT_ADDRESS(RAA_REMIT_LOC.ADDRESS_STYLE
                                               ,RAA_REMIT_LOC.ADDRESS1
                                               ,RAA_REMIT_LOC.ADDRESS2
                                               ,RAA_REMIT_LOC.ADDRESS3
                                               ,RAA_REMIT_LOC.ADDRESS4
                                               ,RAA_REMIT_LOC.CITY
                                               ,RAA_REMIT_LOC.COUNTY
                                               ,RAA_REMIT_LOC.STATE
                                               ,RAA_REMIT_LOC.PROVINCE
                                               ,RAA_REMIT_LOC.POSTAL_CODE
                                               ,FT_REMIT.TERRITORY_SHORT_NAME)) RAA_CONCAT_REMIT_TO_ADDRESS
       
      ,APBA.BANK_ACCOUNT_NAME APBA_BANK_ACCOUNT_NAME
      ,DECODE(NVL(FND_PROFILE.VALUE('AR_MASK_BANK_ACCOUNT_NUMBERS')
                 ,'F')
             ,'N'
             ,APBA.BANK_ACCOUNT_NUM
             ,'F'
             ,RPAD(SUBSTRB(APBA.BANK_ACCOUNT_NUM
                          ,1
                          ,4)
                  ,LENGTH(APBA.BANK_ACCOUNT_NUM)
                  ,'*')
             ,'L'
             ,LPAD(SUBSTRB(APBA.BANK_ACCOUNT_NUM
                          ,-4)
                  ,LENGTH(APBA.BANK_ACCOUNT_NUM)
                  ,'*')) APBA_BANK_ACCOUNT_NUM
      ,APBA.INACTIVE_DATE APBA_INACTIVE_DATE
      ,APB.BANK_NAME APB_CUSTOMER_BANK_NAME
      ,APB.BANK_BRANCH_NAME APB_CUSTOMER_BANK_BRANCH_NAME
      ,ARM.NAME ARM_RECEIPT_METHOD_NAME
      ,ARM.PAYMENT_TYPE_CODE ARM_PAYMENT_TYPE_CODE
      ,ARC.CREATION_METHOD_CODE ARC_CREATION_METHOD_CODE
      ,BS.NAME BS_BATCH_SOURCE_NAME
      ,BS.AUTO_TRX_NUMBERING_FLAG BS_AUTO_TRX_NUMBERING_FLAG
      ,BS.COPY_DOC_NUMBER_FLAG BS_COPY_DOC_NUMBER_FLAG
      ,RAB.NAME RAB_BATCH_NAME
       
      ,CTT.NAME CTT_TYPE_NAME
      ,CTT.TYPE CTT_CLASS
      ,ARPT_SQL_FUNC_UTIL.GET_SALESREP_NAME_NUMBER(CT.PRIMARY_SALESREP_ID
                                                  ,'NAME') RAS_PRIMARY_SALESREP_NAME
      ,ARPT_SQL_FUNC_UTIL.GET_SALESREP_NAME_NUMBER(CT.PRIMARY_SALESREP_ID
                                                  ,'NUMBER') RAS_PRIMARY_SALESREP_NUM
      ,RAT.NAME RAT_TERM_NAME
      ,RAT.IN_USE RAT_TERM_IN_USE_FLAG
      ,SOA.NAME SOA_AGREEMENT_NAME
      ,ORF.DESCRIPTION OF_SHIP_VIA_NAME
      ,ORF.ORGANIZATION_ID OF_ORGANIZATION_ID
      ,AL_FOB.MEANING AL_FOB_MEANING
      ,AL_TAX.MEANING AL_DEFAULT_TAX_EXEMPT_FLAG
      ,CT.CT_REFERENCE CT_REFERENCE
      ,GD.GL_DATE GD_GL_DATE
      ,GDCT.USER_CONVERSION_TYPE GDCT_USER_EXCHANGE_RATE_TYPE
      ,ARPT_SQL_FUNC_UTIL.GET_CB_INVOICE(CT.CUSTOMER_TRX_ID
                                        ,CTT.TYPE) CT_INVOICE_FOR_CB
      ,ARPT_SQL_FUNC_UTIL.GET_DISPUTE_AMOUNT(CT.CUSTOMER_TRX_ID
                                            ,CTT.TYPE
                                            ,CTT.ACCOUNTING_AFFECT_FLAG) PS_DISPUTE_AMOUNT
      ,ARPT_SQL_FUNC_UTIL.GET_DISPUTE_DATE(CT.CUSTOMER_TRX_ID
                                          ,CTT.TYPE
                                          ,CTT.ACCOUNTING_AFFECT_FLAG) PS_DISPUTE_DATE
      ,ARPT_SQL_FUNC_UTIL.GET_MAX_DISPUTE_DATE(CT.CUSTOMER_TRX_ID
                                              ,CTT.TYPE
                                              ,CTT.ACCOUNTING_AFFECT_FLAG) DH_MAX_DISPUTE_DATE
       
      ,ARPT_SQL_FUNC_UTIL.GET_REVENUE_RECOG_RUN_FLAG(CT.CUSTOMER_TRX_ID
                                                    ,CT.INVOICING_RULE_ID) REV_RECOG_RUN_FLAG
      ,ARPT_SQL_FUNC_UTIL.GET_POSTED_FLAG(CT.CUSTOMER_TRX_ID
                                         ,CTT.POST_TO_GL
                                         ,CT.COMPLETE_FLAG) POSTED_FLAG --过账标识？
      ,ARPT_SQL_FUNC_UTIL.GET_SELECTED_FOR_PAYMENT_FLAG(CT.CUSTOMER_TRX_ID
                                                       ,CTT.ACCOUNTING_AFFECT_FLAG
                                                       ,CT.COMPLETE_FLAG) SELECTED_FOR_PAYMENT_FLAG
      ,ARPT_SQL_FUNC_UTIL.GET_ACTIVITY_FLAG(CT.CUSTOMER_TRX_ID
                                           ,CTT.ACCOUNTING_AFFECT_FLAG
                                           ,CT.COMPLETE_FLAG
                                           ,CTT.TYPE
                                           ,CT.INITIAL_CUSTOMER_TRX_ID
                                           ,CT.PREVIOUS_CUSTOMER_TRX_ID) ACTIVITY_FLAG
      ,CTT.POST_TO_GL CTT_POST_TO_GL_FLAG
      ,CTT.ACCOUNTING_AFFECT_FLAG CTT_OPEN_RECEIVABLES_FLAG
      ,CTT.ALLOW_FREIGHT_FLAG CTT_ALLOW_FREIGHT_FLAG
      ,CTT.CREATION_SIGN CTT_CREATION_SIGN
      ,CTT.ALLOW_OVERAPPLICATION_FLAG CTT_ALLOW_OVERAPPLICATION_FLAG
      ,CTT.NATURAL_APPLICATION_ONLY_FLAG CTT_NATURAL_APP_ONLY_FLAG
      ,CTT.TAX_CALCULATION_FLAG CTT_TAX_CALCULATION_FLAG
      ,CTT.DEFAULT_STATUS CTT_DEFAULT_STATUS
      ,CTT.DEFAULT_TERM CTT_DEFAULT_TERM
      ,CTT.DEFAULT_PRINTING_OPTION CTT_DEFAULT_PRINTING_OPTION
      ,DECODE(CT.INVOICING_RULE_ID
             ,NULL
             ,'N'
             ,'Y') RULES_FLAG
       
      ,DECODE(CT.PRINTING_LAST_PRINTED
             ,NULL
             ,'N'
             ,'Y') PRINTED_FLAG
      ,DECODE(CT.PREVIOUS_CUSTOMER_TRX_ID
             ,NULL
             ,'N'
             ,'Y') CM_AGAINST_TRX_FLAG
      ,SU_BILL.STATUS SITE_STATUS
      ,RAC_BILL.STATUS CUSTOMER_STATUS
      ,ARPT_SQL_FUNC_UTIL.GET_OVERRIDE_TERMS(CT.BILL_TO_CUSTOMER_ID
                                            ,CT.BILL_TO_SITE_USE_ID) OVERRIDE_TERMS
      ,DECODE(CT.INITIAL_CUSTOMER_TRX_ID
             ,NULL
             ,DECODE(CTT.TYPE
                    ,'DEP'
                    ,'N'
                    ,'GUAR'
                    ,'N'
                    ,'CB'
                    ,'N'
                    ,'Y')
             ,'Y') COMMITMENTS_EXIST_FLAG
      ,DECODE(CT.AGREEMENT_ID
             ,NULL
             ,DECODE(CTT.TYPE
                    ,'CM'
                    ,'N'
                    ,ARPT_SQL_FUNC_UTIL.GET_AGREEMENTS_EXIST_FLAG(CT.BILL_TO_CUSTOMER_ID
                                                                 ,CT.TRX_DATE))
             ,'Y') AGREEMENTS_EXIST_FLAG
      ,FND_ATTACHMENT_UTIL_PKG.GET_ATCHMT_EXISTS('RA_CUSTOMER_TRX'
                                                ,CT.CUSTOMER_TRX_ID) ATCHMT_FLAG
       
      ,CT.REVERSED_CASH_RECEIPT_ID REVERSED_CASH_RECEIPT_ID
  FROM RA_CUST_TRX_LINE_GL_DIST_ALL GD
      ,RA_CUSTOMER_TRX_ALL          CT
      ,HZ_CUST_ACCOUNTS             RAC_BILL
      ,HZ_PARTIES                   RAC_BILL_PARTY
      ,HZ_CUST_ACCOUNTS             RAC_SHIP
      ,HZ_PARTIES                   RAC_SHIP_PARTY
      ,HZ_CUST_ACCOUNTS             RAC_SOLD
      ,HZ_PARTIES                   RAC_SOLD_PARTY
      ,HZ_CUST_ACCOUNTS             RAC_PAYING
      ,HZ_PARTIES                   RAC_PAYING_PARTY
      ,HZ_CUST_SITE_USES_ALL        SU_BILL
      ,HZ_CUST_SITE_USES_ALL        SU_SHIP
      ,HZ_CUST_SITE_USES_ALL        SU_PAYING
      ,FND_TERRITORIES_VL           FT_BILL
      ,FND_TERRITORIES_VL           FT_SHIP
      ,FND_TERRITORIES_VL           FT_REMIT
      ,HZ_CUST_ACCT_SITES_ALL       RAA_BILL
      ,HZ_PARTY_SITES               RAA_BILL_PS
      ,HZ_LOCATIONS                 RAA_BILL_LOC
      ,HZ_CUST_ACCT_SITES_ALL       RAA_SHIP
      ,HZ_PARTY_SITES               RAA_SHIP_PS
      ,HZ_LOCATIONS                 RAA_SHIP_LOC
      ,HZ_CUST_ACCT_SITES_ALL       RAA_REMIT
      ,HZ_PARTY_SITES               RAA_REMIT_PS
      ,HZ_LOCATIONS                 RAA_REMIT_LOC
      ,HZ_CUST_ACCOUNT_ROLES        RACO_SHIP
      ,HZ_PARTIES                   RACO_SHIP_PARTY
      ,HZ_RELATIONSHIPS             RACO_SHIP_REL
      ,HZ_CUST_ACCOUNT_ROLES        RACO_BILL
      ,HZ_PARTIES                   RACO_BILL_PARTY
      ,HZ_RELATIONSHIPS             RACO_BILL_REL
      ,AP_BANK_ACCOUNTS_ALL         APBA
      ,AP_BANK_BRANCHES             APB
      ,AR_RECEIPT_METHODS           ARM
      ,AR_RECEIPT_CLASSES           ARC
      ,RA_BATCH_SOURCES_ALL         BS
      ,RA_BATCHES_ALL               RAB
      ,RA_CUST_TRX_TYPES_ALL        CTT
      ,RA_TERMS                     RAT
      ,SO_AGREEMENTS                SOA
      ,ORG_FREIGHT                  ORF
      ,GL_DAILY_CONVERSION_TYPES    GDCT
      ,RA_CUSTOMER_TRX_ALL          CT_REL
      ,AR_LOOKUPS                   AL_FOB
      ,AR_LOOKUPS                   AL_TAX
 WHERE CT.CUSTOMER_TRX_ID = GD.CUSTOMER_TRX_ID
   AND 'REC' = GD.ACCOUNT_CLASS
   AND 'Y' = GD.LATEST_REC_FLAG
   AND CT.RELATED_CUSTOMER_TRX_ID = CT_REL.CUSTOMER_TRX_ID(+)
   AND CT.BILL_TO_CUSTOMER_ID = RAC_BILL.CUST_ACCOUNT_ID
   AND RAC_BILL.PARTY_ID = RAC_BILL_PARTY.PARTY_ID
   AND CT.SHIP_TO_CUSTOMER_ID = RAC_SHIP.CUST_ACCOUNT_ID(+)
   AND RAC_SHIP.PARTY_ID = RAC_SHIP_PARTY.PARTY_ID(+)
   AND CT.SOLD_TO_CUSTOMER_ID = RAC_SOLD.CUST_ACCOUNT_ID(+)
   AND RAC_SOLD.PARTY_ID = RAC_SOLD_PARTY.PARTY_ID(+)
   AND CT.PAYING_CUSTOMER_ID = RAC_PAYING.CUST_ACCOUNT_ID(+)
   AND RAC_PAYING.PARTY_ID = RAC_PAYING_PARTY.PARTY_ID(+)
   AND CT.BILL_TO_SITE_USE_ID = SU_BILL.SITE_USE_ID
   AND CT.SHIP_TO_SITE_USE_ID = SU_SHIP.SITE_USE_ID(+)
   AND CT.PAYING_SITE_USE_ID = SU_PAYING.SITE_USE_ID(+)
   AND SU_BILL.CUST_ACCT_SITE_ID = RAA_BILL.CUST_ACCT_SITE_ID
   AND RAA_BILL.PARTY_SITE_ID = RAA_BILL_PS.PARTY_SITE_ID
   AND RAA_BILL_LOC.LOCATION_ID = RAA_BILL_PS.LOCATION_ID
   AND SU_SHIP.CUST_ACCT_SITE_ID = RAA_SHIP.CUST_ACCT_SITE_ID(+)
   AND RAA_SHIP.PARTY_SITE_ID = RAA_SHIP_PS.PARTY_SITE_ID(+)
   AND RAA_SHIP_LOC.LOCATION_ID(+) = RAA_SHIP_PS.LOCATION_ID
   AND CT.BILL_TO_CONTACT_ID = RACO_BILL.CUST_ACCOUNT_ROLE_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 CT.SHIP_TO_CONTACT_ID = RACO_SHIP.CUST_ACCOUNT_ROLE_ID(+)
   AND RACO_SHIP.PARTY_ID = RACO_SHIP_REL.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 RACO_SHIP.ROLE_TYPE(+) = 'CONTACT'
   AND RACO_SHIP_REL.SUBJECT_ID = RACO_SHIP_PARTY.PARTY_ID(+)
   AND CT.REMIT_TO_ADDRESS_ID = RAA_REMIT.CUST_ACCT_SITE_ID(+)
   AND RAA_REMIT.PARTY_SITE_ID = RAA_REMIT_PS.PARTY_SITE_ID(+)
   AND RAA_REMIT_LOC.LOCATION_ID(+) = RAA_REMIT_PS.LOCATION_ID
   AND RAA_BILL_LOC.COUNTRY = FT_BILL.TERRITORY_CODE(+)
   AND RAA_SHIP_LOC.COUNTRY = FT_SHIP.TERRITORY_CODE(+)
   AND RAA_REMIT_LOC.COUNTRY = FT_REMIT.TERRITORY_CODE(+)
   AND CT.CUSTOMER_BANK_ACCOUNT_ID = APBA.BANK_ACCOUNT_ID(+)
   AND APBA.BANK_BRANCH_ID = APB.BANK_BRANCH_ID(+)
   AND CT.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID(+)
   AND ARM.RECEIPT_CLASS_ID = ARC.RECEIPT_CLASS_ID(+)
   AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
   AND CT.BATCH_ID = RAB.BATCH_ID(+)
   AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
   AND CTT.TYPE <> 'BR'
   AND CT.TERM_ID = RAT.TERM_ID(+)
   AND CT.AGREEMENT_ID = SOA.AGREEMENT_ID(+)
   AND CT.EXCHANGE_RATE_TYPE = GDCT.CONVERSION_TYPE(+)
   AND 'FOB' = AL_FOB.LOOKUP_TYPE(+)
   AND CT.FOB_POINT = AL_FOB.LOOKUP_CODE(+)
   AND CT.SHIP_VIA = ORF.FREIGHT_CODE(+)
   AND CT.ORG_ID = ORF.ORGANIZATION_ID(+)
   AND 'TAX_CONTROL_FLAG' = AL_TAX.LOOKUP_TYPE(+)
   AND CT.DEFAULT_TAX_EXEMPT_FLAG = AL_TAX.LOOKUP_CODE(+)
   AND RACO_SHIP_REL.STATUS(+) = 'A'
   AND RACO_BILL_REL.STATUS(+) = 'A'
   AND CT.CUSTOMER_TRX_ID = 1485432;
评论列表
本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。
试试其它关键字
　收款录入后产生的信息　
同语言下
可能有用的
专注Oracle EBS贡献的其它代码(66)