[Home] [Help]
3163: END IF;
3164: END ExpenseSetOrgContext;
3165:
3166: PROCEDURE UpdateExpenseStatusCode(
3167: p_invoice_id AP_INVOICES_ALL.invoice_id%TYPE
3168: ) IS
3169:
3170: t_paid_status CONSTANT VARCHAR2(10) := 'PAID';
3171: t_partially_paid_status CONSTANT VARCHAR2(10) := 'PARPAID';
3170: t_paid_status CONSTANT VARCHAR2(10) := 'PAID';
3171: t_partially_paid_status CONSTANT VARCHAR2(10) := 'PARPAID';
3172: t_invoiced_status CONSTANT VARCHAR2(10) := 'INVOICED';
3173:
3174: CURSOR c_report_status(l_invoice_id IN AP_INVOICES_ALL.invoice_id%TYPE)
3175: IS
3176: -- cc in bp
3177: SELECT 'PARENT' Identifier,
3178: parent_aerh.report_header_id report_header_id,
3180: DECODE(parent_AI.Payment_status_flag,
3181: 'Y','PAID','N','INVOICED','P','PARPAID',NULL) ) invoice_status
3182: FROM ap_expense_report_headers_all parent_aerh,
3183: ap_expense_report_headers_all main_aerh,
3184: ap_invoices_all parent_ai,
3185: ap_payment_schedules_all parent_aps
3186: WHERE
3187: main_aerh.bothpay_parent_id = parent_aerh.report_header_id (+) and
3188: parent_aerh.vouchno = parent_ai.invoice_id and
3196: DECODE(main_AI.Payment_status_flag,
3197: 'Y','PAID','N','INVOICED','P','PARPAID',NULL) ) invoice_status
3198: FROM
3199: ap_expense_report_headers_all main_aerh,
3200: ap_invoices_all main_ai,
3201: ap_payment_schedules_all main_aps
3202: WHERE
3203: main_aerh.vouchno = main_ai.invoice_id and
3204: main_ai.invoice_id = main_aps.invoice_id and
3212: 'Y','PAID','N','INVOICED','P','PARPAID',NULL) ) invoice_status
3213: FROM
3214: ap_expense_report_headers_all child_aerh,
3215: ap_expense_report_headers_all main_aerh,
3216: ap_invoices_all child_ai,
3217: ap_payment_schedules_all child_aps
3218: WHERE child_aerh.bothpay_parent_id (+) = main_aerh.report_header_id and
3219: child_aerh.vouchno = child_ai.invoice_id and
3220: child_ai.invoice_id = child_aps.invoice_id and