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 NameSQL textValidation
SOB ID
 
LOV Oracle
Remind Flag
 
LOV Oracle
Review Date
 
Date
Due Date
 
Date
Batch Source Name
 
LOV Oracle