Reports2017-11-18T12:27:27+00:00

IGI Standing Charges: Listing - draft

Description
Categories: BI Publisher, Financials
Application: Public Sector Financials International
Source: Standing Charges: Listing (XML) - Not Supported: Reserved For Future Use
Short Name: IGIRRSCL_XML
DB package: IGI_IGIRRSCL_XMLP_PKG
SELECT   distinct  gsob.NAME "sob_name"
      , sc.description "Standing_Charge"
      , sc.charge_reference "Charge_Reference"
      , ty.NAME "Transaction"
      , sc.status "Status"
      , sc.standing_charge_date "Date"
      , bs.name "Batch_Source"
      , sr.name "Sales_Person"
      , hp1.party_name Bill_to_name
      , cu2.party_name Ship_to_name
      , sc.suppress_inv_print "Suppress_Inv_Prt"
      , ca1.account_number Bill_to_num
      , cu2.account_number Ship_to_num
      , sc.review_date
      , sc.reminder_days
      , sc.period_name frequency
      , sc.next_due_date
      , sc.start_date
      , sc.end_date
      , decode(bb.instrument_type, 'CREDITCARD',bb.card_issuer_name,'BANKACCOUNT', bb.bank_name, NULL) bcname
      , rm.name Payment_Method
      , decode(bb.instrument_type, 'CREDITCARD',bb.card_holder_name,'BANKACCOUNT', bb.bank_branch_name, NULL) brholname
      , decode(bb.instrument_type, 'CREDITCARD',bb.card_number,'BANKACCOUNT', bb.account_number, NULL) num
      , ld.Charge_item_number
      , it.item_code
      , it.description
      , ld.price Current_Price
      , ld.current_effective_date 
      , ld.revised_price 
      , ld.revised_effective_date
      , ld.previous_price 
      , ld.period_name unit_of_measure
      , ld.Quantity
      , tx.tax_rate_code
      ,fcv.name currency_name
      ,fcv.currency_code currency_code
FROM   
       ZX_SCO_RATES    tx
     , iby_trxn_extensions_v bb
     , IGI_RPI_ITEMS it            
     , IGI_RPI_LINE_DETAILS ld
     , AR_RECEIPT_METHODS rm
     , HZ_PARTIES hp1		
     , HZ_CUST_ACCOUNTS ca1	
     , (select party_name, cust_account_id customer_id, account_number from hz_parties, hz_cust_accounts
	where hz_parties.party_id = hz_cust_accounts.party_id) cu2
     , RA_CUST_TRX_TYPES ty
     , RA_SALESREPS sr
     , IGI_RPI_STANDING_CHARGES sc
     , RA_BATCH_SOURCES bs
     , GL_SETS_OF_BOOKS gsob
     ,FND_CURRENCIES_VL fcv
WHERE hp1.party_id = ca1.party_id
AND     gsob.SET_OF_BOOKS_ID = :P_SOB_ID 
AND     bs.BATCH_SOURCE_ID LIKE nvl(:P_BATCH_SOURCE_ID, '%')
AND     ((:P_REVIEW_DATE IS NOT NULL
                  AND sc.REVIEW_DATE <= :P_REVIEW_DATE)
             OR :P_REVIEW_DATE IS NULL)
AND     ((:P_DUE_DATE IS NOT NULL
                   AND sc.NEXT_DUE_DATE <= :P_DUE_DATE)
             OR :P_DUE_DATE IS NULL)
AND     ((:P_REMIND_FLAG = 'Y'
                 AND sysdate >= (sc.END_DATE - sc.REMINDER_DAYS))
            OR :P_REMIND_FLAG = 'N')
AND     sc.BATCH_SOURCE_ID = bs.BATCH_SOURCE_ID
AND     sc.CUST_TRX_TYPE_ID = ty.CUST_TRX_TYPE_ID
AND     sc.PAYMENT_TRXN_EXTENSION_ID = bb.trxn_extension_id(+)
AND     sc.SALESREP_ID = sr.SALESREP_ID
AND     sc.BILL_TO_CUSTOMER_ID = ca1.CUST_ACCOUNT_ID
AND     sc.SHIP_TO_CUSTOMER_ID = cu2.CUSTOMER_ID(+)
AND     sc.RECEIPT_METHOD_ID = rm.RECEIPT_METHOD_ID(+)
AND     ld.STANDING_CHARGE_ID = sc.STANDING_CHARGE_ID
AND     ld.VAT_TAX_ID = tx.TAX_RATE_ID(+)
AND     ld.ITEM_ID = it.ITEM_ID
AND     gsob.currency_code = fcv.currency_code
ORDER BY   
           bs.NAME
         ,  hp1.PARTY_NAME
         , sc.CHARGE_REFERENCE
Parameter Name SQL text Validation
SOB ID
 
LOV Oracle
Remind Flag
 
LOV Oracle
Review Date
 
Date
Due Date
 
Date
Batch Source Name
 
LOV Oracle
By continuing to use the site, you agree to the use of cookies. Accept