VIEW: APPS.PN_EXP_PAYMENT_ITEMS_V

Source

View Text - Preformatted

SELECT pnpi.export_to_ap_flag export_to_ap_flag, to_char(NULL) export_to_ar_flag, pnpi.transferred_to_ap_flag transferred_to_ap_flag, to_char(NULL) transferred_to_ar_flag, pnpi.vendor_id vendor_id, to_number(NULL) customer_id, pnpi.vendor_site_id vendor_site_id, to_number(NULL) customer_site_use_id, pov.vendor_name vendor_name, to_char(NULL) customer_name, pnpi.ap_invoice_num ap_invoice_num, to_char(NULL) ar_trx_num, fndlva.meaning payment_purpose, fndlvb.meaning payment_type, pnpi.due_date due_date, pnpi.set_of_books_id set_of_books_id, pnpi.currency_code currency_code, pnpi.export_currency_code export_currency_code, pnpi.rate rate, pnpi.actual_amount actual_amount, pnpi.export_currency_amount export_currency_amount, pnpt.code_combination_id code_combination_id, pnpi.comments comments, pnl.lease_id lease_id, pnl.name lease_name, pnl.lease_num lease_number, pnps.schedule_date schedule_date, pnps.period_name period_name, pnpi.payment_item_id payment_item_id, pnpi.last_update_date last_update_date, pnpi.last_updated_by last_updated_by, pnpi.creation_date creation_date, pnpi.created_by created_by, pnpi.last_update_login last_update_login, pnpt.payment_term_type_code payment_term_type_code, pnpi.payment_schedule_id payment_schedule_id, pnpi.payment_term_id payment_term_id, pnpi.estimated_amount estimated_amount, pnpi.period_fraction period_fraction, pnpi.description description, pnpi.org_id org_id, pnpt.payment_purpose_code payment_purpose_code, pd.account_id rec_lia_account_id, pnpi.grouping_rule_id grouping_rule_id, pngr.name inv_group_name FROM po_vendors pov, fnd_lookups fndlva, fnd_lookups fndlvb, pn_leases_all pnl, pn_payment_schedules_all pnps, pn_payment_terms_all pnpt, pn_payment_items pnpi, pn_distributions_all pd, pn_pay_group_rules pngr WHERE fndlva.lookup_type = 'PN_PAYMENT_PURPOSE_TYPE' AND fndlva.lookup_code = pnpt.payment_purpose_code AND fndlvb.lookup_type = 'PN_PAYMENT_TERM_TYPE' AND fndlvb.lookup_code = pnpt.payment_term_type_code AND pnpi.payment_item_type_lookup_code = 'CASH' AND pnps.payment_status_lookup_code = 'APPROVED' AND pnl.lease_id = pnps.lease_id AND pnpi.payment_schedule_id = pnps.payment_schedule_id AND pnpi.payment_term_id = pnpt.payment_term_id AND pnpi.vendor_id = pov.vendor_id AND 'AP' in ( select PNP_UTIL_FUNC.get_view_context from dual ) AND pnpi.actual_amount <> 0 AND pd.PAYMENT_TERM_ID (+)= pnpt.PAYMENT_TERM_ID AND pd.ACCOUNT_CLASS (+)= 'LIA' AND pngr.grouping_rule_id (+)= pnpi.GROUPING_RULE_ID UNION ALL SELECT to_char(NULL) export_to_ap_flag, pnpi.export_to_ar_flag export_to_ar_flag, to_char(NULL) transferred_to_ap_flag, pnpi.transferred_to_ar_flag transferred_to_ar_flag, to_number(NULL) vendor_id, pnpi.customer_id customer_id, to_number(NULL) vendor_site_id, pnpi.customer_site_use_id customer_site_use_id, to_char(NULL) vendor_name, hzp.party_name customer_name, to_char(NULL) ap_invoice_num, pnpi.ar_trx_num ar_trx_num, fndlva.meaning payment_purpose, fndlvb.meaning payment_type, pnpi.due_date due_date, pnpi.set_of_books_id set_of_books_id, pnpi.currency_code currency_code, pnpi.export_currency_code export_currency_code, pnpi.rate rate, pnpi.actual_amount actual_amount, pnpi.export_currency_amount export_currency_amount, pnpt.code_combination_id code_combination_id, pnpi.comments comments, pnl.lease_id lease_id, pnl.name lease_name, pnl.lease_num lease_number, pnps.schedule_date schedule_date, pnps.period_name period_name, pnpi.payment_item_id payment_item_id, pnpi.last_update_date last_update_date, pnpi.last_updated_by last_updated_by, pnpi.creation_date creation_date, pnpi.created_by created_by, pnpi.last_update_login last_update_login, pnpt.payment_term_type_code payment_term_type_code, pnpi.payment_schedule_id payment_schedule_id, pnpi.payment_term_id payment_term_id, pnpi.estimated_amount estimated_amount, pnpi.period_fraction period_fraction, pnpi.description description, pnpi.org_id org_id, pnpt.payment_purpose_code payment_purpose_code, pd.account_id rec_lia_account_id, to_number(null) grouping_rule_id, to_char(null) inv_group_name FROM hz_parties hzp, hz_cust_accounts hca, fnd_lookups fndlva, fnd_lookups fndlvb, pn_leases_all pnl, pn_payment_schedules_all pnps, pn_payment_terms_all pnpt, pn_payment_items pnpi, pn_distributions_all pd WHERE fndlva.lookup_type = 'PN_PAYMENT_PURPOSE_TYPE' AND fndlva.lookup_code = pnpt.payment_purpose_code AND fndlvb.lookup_type = 'PN_PAYMENT_TERM_TYPE' AND fndlvb.lookup_code = pnpt.payment_term_type_code AND pnpi.payment_item_type_lookup_code = 'CASH' AND pnps.payment_status_lookup_code = 'APPROVED' AND pnl.lease_id = pnps.lease_id AND pnpi.payment_schedule_id = pnps.payment_schedule_id AND pnpi.payment_term_id = pnpt.payment_term_id AND pnpi.customer_id = hca.cust_account_id AND hzp.party_id = hca.party_id AND 'AR' in ( select PNP_UTIL_FUNC.get_view_context from dual ) AND pnpi.actual_amount <> 0 AND pd.PAYMENT_TERM_ID (+)= pnpt.PAYMENT_TERM_ID AND pd.ACCOUNT_CLASS (+)= 'REC'

View Text - HTML Formatted

SELECT PNPI.EXPORT_TO_AP_FLAG EXPORT_TO_AP_FLAG
, TO_CHAR(NULL) EXPORT_TO_AR_FLAG
, PNPI.TRANSFERRED_TO_AP_FLAG TRANSFERRED_TO_AP_FLAG
, TO_CHAR(NULL) TRANSFERRED_TO_AR_FLAG
, PNPI.VENDOR_ID VENDOR_ID
, TO_NUMBER(NULL) CUSTOMER_ID
, PNPI.VENDOR_SITE_ID VENDOR_SITE_ID
, TO_NUMBER(NULL) CUSTOMER_SITE_USE_ID
, POV.VENDOR_NAME VENDOR_NAME
, TO_CHAR(NULL) CUSTOMER_NAME
, PNPI.AP_INVOICE_NUM AP_INVOICE_NUM
, TO_CHAR(NULL) AR_TRX_NUM
, FNDLVA.MEANING PAYMENT_PURPOSE
, FNDLVB.MEANING PAYMENT_TYPE
, PNPI.DUE_DATE DUE_DATE
, PNPI.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, PNPI.CURRENCY_CODE CURRENCY_CODE
, PNPI.EXPORT_CURRENCY_CODE EXPORT_CURRENCY_CODE
, PNPI.RATE RATE
, PNPI.ACTUAL_AMOUNT ACTUAL_AMOUNT
, PNPI.EXPORT_CURRENCY_AMOUNT EXPORT_CURRENCY_AMOUNT
, PNPT.CODE_COMBINATION_ID CODE_COMBINATION_ID
, PNPI.COMMENTS COMMENTS
, PNL.LEASE_ID LEASE_ID
, PNL.NAME LEASE_NAME
, PNL.LEASE_NUM LEASE_NUMBER
, PNPS.SCHEDULE_DATE SCHEDULE_DATE
, PNPS.PERIOD_NAME PERIOD_NAME
, PNPI.PAYMENT_ITEM_ID PAYMENT_ITEM_ID
, PNPI.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PNPI.LAST_UPDATED_BY LAST_UPDATED_BY
, PNPI.CREATION_DATE CREATION_DATE
, PNPI.CREATED_BY CREATED_BY
, PNPI.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PNPT.PAYMENT_TERM_TYPE_CODE PAYMENT_TERM_TYPE_CODE
, PNPI.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, PNPI.PAYMENT_TERM_ID PAYMENT_TERM_ID
, PNPI.ESTIMATED_AMOUNT ESTIMATED_AMOUNT
, PNPI.PERIOD_FRACTION PERIOD_FRACTION
, PNPI.DESCRIPTION DESCRIPTION
, PNPI.ORG_ID ORG_ID
, PNPT.PAYMENT_PURPOSE_CODE PAYMENT_PURPOSE_CODE
, PD.ACCOUNT_ID REC_LIA_ACCOUNT_ID
, PNPI.GROUPING_RULE_ID GROUPING_RULE_ID
, PNGR.NAME INV_GROUP_NAME
FROM PO_VENDORS POV
, FND_LOOKUPS FNDLVA
, FND_LOOKUPS FNDLVB
, PN_LEASES_ALL PNL
, PN_PAYMENT_SCHEDULES_ALL PNPS
, PN_PAYMENT_TERMS_ALL PNPT
, PN_PAYMENT_ITEMS PNPI
, PN_DISTRIBUTIONS_ALL PD
, PN_PAY_GROUP_RULES PNGR
WHERE FNDLVA.LOOKUP_TYPE = 'PN_PAYMENT_PURPOSE_TYPE'
AND FNDLVA.LOOKUP_CODE = PNPT.PAYMENT_PURPOSE_CODE
AND FNDLVB.LOOKUP_TYPE = 'PN_PAYMENT_TERM_TYPE'
AND FNDLVB.LOOKUP_CODE = PNPT.PAYMENT_TERM_TYPE_CODE
AND PNPI.PAYMENT_ITEM_TYPE_LOOKUP_CODE = 'CASH'
AND PNPS.PAYMENT_STATUS_LOOKUP_CODE = 'APPROVED'
AND PNL.LEASE_ID = PNPS.LEASE_ID
AND PNPI.PAYMENT_SCHEDULE_ID = PNPS.PAYMENT_SCHEDULE_ID
AND PNPI.PAYMENT_TERM_ID = PNPT.PAYMENT_TERM_ID
AND PNPI.VENDOR_ID = POV.VENDOR_ID
AND 'AP' IN ( SELECT PNP_UTIL_FUNC.GET_VIEW_CONTEXT
FROM DUAL )
AND PNPI.ACTUAL_AMOUNT <> 0
AND PD.PAYMENT_TERM_ID (+)= PNPT.PAYMENT_TERM_ID
AND PD.ACCOUNT_CLASS (+)= 'LIA'
AND PNGR.GROUPING_RULE_ID (+)= PNPI.GROUPING_RULE_ID UNION ALL SELECT TO_CHAR(NULL) EXPORT_TO_AP_FLAG
, PNPI.EXPORT_TO_AR_FLAG EXPORT_TO_AR_FLAG
, TO_CHAR(NULL) TRANSFERRED_TO_AP_FLAG
, PNPI.TRANSFERRED_TO_AR_FLAG TRANSFERRED_TO_AR_FLAG
, TO_NUMBER(NULL) VENDOR_ID
, PNPI.CUSTOMER_ID CUSTOMER_ID
, TO_NUMBER(NULL) VENDOR_SITE_ID
, PNPI.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, TO_CHAR(NULL) VENDOR_NAME
, HZP.PARTY_NAME CUSTOMER_NAME
, TO_CHAR(NULL) AP_INVOICE_NUM
, PNPI.AR_TRX_NUM AR_TRX_NUM
, FNDLVA.MEANING PAYMENT_PURPOSE
, FNDLVB.MEANING PAYMENT_TYPE
, PNPI.DUE_DATE DUE_DATE
, PNPI.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, PNPI.CURRENCY_CODE CURRENCY_CODE
, PNPI.EXPORT_CURRENCY_CODE EXPORT_CURRENCY_CODE
, PNPI.RATE RATE
, PNPI.ACTUAL_AMOUNT ACTUAL_AMOUNT
, PNPI.EXPORT_CURRENCY_AMOUNT EXPORT_CURRENCY_AMOUNT
, PNPT.CODE_COMBINATION_ID CODE_COMBINATION_ID
, PNPI.COMMENTS COMMENTS
, PNL.LEASE_ID LEASE_ID
, PNL.NAME LEASE_NAME
, PNL.LEASE_NUM LEASE_NUMBER
, PNPS.SCHEDULE_DATE SCHEDULE_DATE
, PNPS.PERIOD_NAME PERIOD_NAME
, PNPI.PAYMENT_ITEM_ID PAYMENT_ITEM_ID
, PNPI.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PNPI.LAST_UPDATED_BY LAST_UPDATED_BY
, PNPI.CREATION_DATE CREATION_DATE
, PNPI.CREATED_BY CREATED_BY
, PNPI.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PNPT.PAYMENT_TERM_TYPE_CODE PAYMENT_TERM_TYPE_CODE
, PNPI.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, PNPI.PAYMENT_TERM_ID PAYMENT_TERM_ID
, PNPI.ESTIMATED_AMOUNT ESTIMATED_AMOUNT
, PNPI.PERIOD_FRACTION PERIOD_FRACTION
, PNPI.DESCRIPTION DESCRIPTION
, PNPI.ORG_ID ORG_ID
, PNPT.PAYMENT_PURPOSE_CODE PAYMENT_PURPOSE_CODE
, PD.ACCOUNT_ID REC_LIA_ACCOUNT_ID
, TO_NUMBER(NULL) GROUPING_RULE_ID
, TO_CHAR(NULL) INV_GROUP_NAME
FROM HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HCA
, FND_LOOKUPS FNDLVA
, FND_LOOKUPS FNDLVB
, PN_LEASES_ALL PNL
, PN_PAYMENT_SCHEDULES_ALL PNPS
, PN_PAYMENT_TERMS_ALL PNPT
, PN_PAYMENT_ITEMS PNPI
, PN_DISTRIBUTIONS_ALL PD
WHERE FNDLVA.LOOKUP_TYPE = 'PN_PAYMENT_PURPOSE_TYPE'
AND FNDLVA.LOOKUP_CODE = PNPT.PAYMENT_PURPOSE_CODE
AND FNDLVB.LOOKUP_TYPE = 'PN_PAYMENT_TERM_TYPE'
AND FNDLVB.LOOKUP_CODE = PNPT.PAYMENT_TERM_TYPE_CODE
AND PNPI.PAYMENT_ITEM_TYPE_LOOKUP_CODE = 'CASH'
AND PNPS.PAYMENT_STATUS_LOOKUP_CODE = 'APPROVED'
AND PNL.LEASE_ID = PNPS.LEASE_ID
AND PNPI.PAYMENT_SCHEDULE_ID = PNPS.PAYMENT_SCHEDULE_ID
AND PNPI.PAYMENT_TERM_ID = PNPT.PAYMENT_TERM_ID
AND PNPI.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
AND HZP.PARTY_ID = HCA.PARTY_ID
AND 'AR' IN ( SELECT PNP_UTIL_FUNC.GET_VIEW_CONTEXT
FROM DUAL )
AND PNPI.ACTUAL_AMOUNT <> 0
AND PD.PAYMENT_TERM_ID (+)= PNPT.PAYMENT_TERM_ID
AND PD.ACCOUNT_CLASS (+)= 'REC'