View: IGS_AS_ORDER_PAYMENT_INFO_V

View Text

SELECT HDR.ROWID
, HDR.ORDER_NUMBER
, HDR.ORDER_STATUS
, LKUP1.MEANING
, HDR.DATE_COMPLETED
, HDR.PERSON_ID
, HDR.ADDR_LINE_1
, HDR.ADDR_LINE_2
, HDR.ADDR_LINE_3
, HDR.ADDR_LINE_4
, HDR.CITY
, HDR.STATE
, HDR.PROVINCE
, HDR.COUNTY
, HDR.COUNTRY
, HDR.POSTAL_CODE
, HDR.EMAIL_ADDRESS
, HDR.PHONE_COUNTRY_CODE
, HDR.PHONE_AREA_CODE
, HDR.PHONE_NUMBER
, HDR.PHONE_EXTENSION
, HDR.FAX_COUNTRY_CODE
, HDR.FAX_AREA_CODE
, HDR.FAX_NUMBER
, HDR.DELIVERY_FEE
, HDR.ORDER_FEE
, HDR.REQUEST_TYPE
, LKUP2.MEANING
, HDR.SUBMIT_METHOD
, HDR.INVOICE_ID
, HDR.CREATED_BY
, HDR.CREATION_DATE
, HDR.LAST_UPDATED_BY
, HDR.LAST_UPDATE_DATE
, HDR.LAST_UPDATE_LOGIN
, HDR.PROGRAM_APPLICATION_ID
, HDR.REQUEST_ID
, HDR.PROGRAM_ID
, HDR.PROGRAM_UPDATE_DATE
, AP.APPLY_DATE
, CR.CREDIT_INSTRUMENT
, LKUP3.MEANING
, CR.CREDIT_CARD_APPROVAL_CODE
, AP.AMOUNT_APPLIED
, CH.BILL_DATE
, CH.INVOICE_AMOUNT
, CH.INVOICE_AMOUNT_DUE
, CH.BILL_NUMBER
FROM (SELECT INVOICE_ID
, SUM (AMOUNT_APPLIED) AMOUNT_APPLIED
, MAX (APPLY_DATE) APPLY_DATE
, MAX(APPLICATION_ID) APPLICATION_ID
FROM IGS_FI_APPLICATIONS GROUP BY INVOICE_ID) AP
, IGS_FI_INV_INT_ALL CH
, IGS_FI_CREDITS_ALL CR
, IGS_AS_ORDER_HDR HDR
, IGS_FI_APPLICATIONS AP2
, IGS_LOOKUPS_VIEW LKUP1
, IGS_LOOKUPS_VIEW LKUP2
, IGS_LOOKUPS_VIEW LKUP3
WHERE HDR.PERSON_ID = CH.PERSON_ID
AND HDR.INVOICE_ID = CH.INVOICE_ID
AND CR.PARTY_ID = CH.PERSON_ID
AND HDR.INVOICE_ID = AP.INVOICE_ID
AND AP2.INVOICE_ID = AP.INVOICE_ID
AND AP2.APPLICATION_ID = AP.APPLICATION_ID
AND AP2.CREDIT_ID = CR.CREDIT_ID
AND HDR.ORDER_STATUS = LKUP1.LOOKUP_CODE(+)
AND LKUP1.LOOKUP_TYPE(+) = 'IGS_AS_ORDER_STATUS'
AND HDR.REQUEST_TYPE = LKUP2.LOOKUP_CODE(+)
AND LKUP2.LOOKUP_TYPE(+) = 'IGS_AS_DOC_REQTYPE'
AND CR.CREDIT_INSTRUMENT = LKUP3.LOOKUP_CODE(+)
AND LKUP3.LOOKUP_TYPE(+) = 'IGS_FI_CREDIT_INSTRUMENT'

Columns