VIEW: APPS.IGS_AS_ORDER_PAYMENT_INFO_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'