IGI Standing Charges: Preliminary Invoice Register - draft

Description
Categories: BI Publisher, Financials
Application: Public Sector Financials International
Source: Standing Charges: Preliminary Invoice Register (XML) - Not Supported: Reserved For Future Use
Short Name: IGIRRPIV_XML
DB package: IGI_IGIRRPIV_XMLP_PKG
SELECT  gsob.NAME "sob_name"
            , bs.name "Batch_Source"
            , sc.charge_reference "Charge Reference" 
            , hp.party_name "Customer Name"
            , sc.next_due_date "Due Date"
            , sc.advance_arrears_ind "Billing Method"
            , sc.period_name "Billing Period"
            , sc.suppress_inv_print "Suppress_Inv_Prt"
            , sr.name "Sales_Person"
            , sc.start_date
            , sc.end_date
            , sc.description "Standing Charge Description"
            , ld.Charge_item_number
            , it.item_code
            , ld.period_name "Charge Period"
            , ld.Quantity
            , tx.tax_rate_code
           , ld.price Current_Price
           , ld.current_effective_date 
           , ld.revised_price 
           , ld.revised_effective_date
           , ld.previous_price 
           , it.description
FROM   
             ZX_SCO_RATES tx
           , IGI_RPI_ITEMS it            
           , IGI_RPI_LINE_DETAILS ld
           , HZ_PARTIES hp 
           , HZ_CUST_ACCOUNTS ca 
           , RA_SALESREPS sr
           , IGI_RPI_STANDING_CHARGES sc
          , RA_BATCH_SOURCES bs
          , GL_SETS_OF_BOOKS gsob
WHERE ca.PARTY_ID = hp.PARTY_ID 
AND     gsob.SET_OF_BOOKS_ID = :P_SOB_ID 
AND     bs.BATCH_SOURCE_ID LIKE nvl(:P_BATCH_SOURCE_ID, '%')
AND     sc.NEXT_DUE_DATE <= :P_RUN_DATE
AND     sc.SET_OF_BOOKS_ID = gsob.SET_OF_BOOKS_ID
AND     sc.BATCH_SOURCE_ID = bs.BATCH_SOURCE_ID
AND     sc.STATUS = 'ACTIVE'
AND     ((nvl(sc.ADVANCE_ARREARS_IND,sc.DEFAULT_INVOICING_RULE ) = 'ADVANCE'
              AND sc.NEXT_DUE_DATE <= NVL(sc.END_DATE, sc.NEXT_DUE_DATE))
              OR
              (nvl(sc.ADVANCE_ARREARS_IND,sc.DEFAULT_INVOICING_RULE) = 'ARREARS'
              AND NVL(sc.PREVIOUS_DUE_DATE, sc.START_DATE) <= NVL(sc.END_DATE, sc.NEXT_DUE_DATE)))
AND     sc.SALESREP_ID = sr.SALESREP_ID
AND     sc.BILL_TO_CUSTOMER_ID = ca.CUST_ACCOUNT_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    nvl(sc.date_synchronized_flag,'Y') = 'Y'
AND    ca.STATUS = 'A'
ORDER BY   gsob.NAME,
           bs.NAME , sc.CHARGE_REFERENCE
         , hp.PARTY_NAME,sc.next_due_date,sc.advance_arrears_ind,sc.advance_arrears_ind,
		 sr.name,sc.suppress_inv_print, sc.start_date
            , sc.end_date
            , sc.description
Parameter Name SQL text Validation
Ledger Id
 
LOV Oracle
Run Date
 
Date
Batch Source Name
 
LOV Oracle
Ask a question