[Home] [Help]
3233: -- SQL What: Retrieve the Temp Labor lines that match either of the
3234: -- reminder criteria (Amount Billed Exceeds Budget, Contractor
3235: -- Assignment Nearing Completion), ignoring those that
3236: -- already had a notification sent.
3237: -- We only consider Standard PO lines with approved, open,
3238: -- non-cancelled shipments.
3239: -- SQL Why: To send notifications for these lines.
3240: --
3241: --
3249: POL2.expiration_date,
3250: POL2.contractor_first_name,
3251: POL2.contractor_last_name,
3252: PJ.name job_name
3253: FROM po_lines_all POL2,
3254: per_jobs_vl PJ,
3255: ( SELECT PLL.po_line_id
3256: , SUM(PLL.amount_billed) total_amount_billed
3257: FROM po_line_locations PLL
3254: per_jobs_vl PJ,
3255: ( SELECT PLL.po_line_id
3256: , SUM(PLL.amount_billed) total_amount_billed
3257: FROM po_line_locations PLL
3258: , po_lines_all POL
3259: , po_headers_all poh
3260: WHERE poh.type_lookup_code = 'STANDARD'
3261: AND poh.po_header_id = POL.po_header_id
3262: AND POL.purchase_basis = 'TEMP LABOR'
3360: p_expiration_date => l_tl_line_rec.expiration_date
3361: );
3362:
3363: -- SQL What: Set the "Amount Billed notification sent" flag to Y.
3364: UPDATE po_lines_all
3365: SET svc_amount_notif_sent = 'Y',
3366: last_update_date = sysdate,
3367: last_updated_by = fnd_global.user_id
3368: WHERE po_line_id = l_tl_line_rec.po_line_id;
3384: p_expiration_date => l_tl_line_rec.expiration_date
3385: );
3386:
3387: -- SQL What: Set the "Assignment Completion notification sent" flag to Y.
3388: UPDATE po_lines_all
3389: SET svc_completion_notif_sent = 'Y',
3390: last_update_date = sysdate,
3391: last_updated_by = fnd_global.user_id
3392: WHERE po_line_id = l_tl_line_rec.po_line_id;
3432: -- type of reminder - ex. SVC_AMOUNT (for Amount Approaching Budget)
3433: --End of Comments
3434: -------------------------------------------------------------------------------
3435: PROCEDURE start_po_line_reminder_wf (
3436: p_po_line_id IN PO_LINES.po_line_id%TYPE,
3437: p_line_reminder_type IN VARCHAR2,
3438: p_requester_id IN NUMBER,
3439: p_contractor_or_job IN VARCHAR2,
3440: p_expiration_date IN DATE
3544: select POH.po_header_id,
3545: POH.segment1
3546: INTO l_po_header_id,
3547: l_document_number
3548: FROM po_lines POL,
3549: po_headers_all POH
3550: WHERE POL.po_line_id = p_po_line_id
3551: AND POL.po_header_id = POH.po_header_id;
3552:
3553: BEGIN
3554:
3555: select PRL.requisition_header_id
3556: INTO l_req_header_id
3557: FROM po_lines POL,
3558: po_line_locations_all PLL,
3559: po_requisition_lines_all PRL
3560: WHERE POL.po_line_id = p_po_line_id
3561: AND POL.po_line_id = PLL.po_line_id