[Home] [Help]
3199: -- SQL What: Retrieve the Temp Labor lines that match either of the
3200: -- reminder criteria (Amount Billed Exceeds Budget, Contractor
3201: -- Assignment Nearing Completion), ignoring those that
3202: -- already had a notification sent.
3203: -- We only consider Standard PO lines with approved, open,
3204: -- non-cancelled shipments.
3205: -- SQL Why: To send notifications for these lines.
3206: --
3207: --
3215: POL2.expiration_date,
3216: POL2.contractor_first_name,
3217: POL2.contractor_last_name,
3218: PJ.name job_name
3219: FROM po_lines_all POL2,
3220: per_jobs_vl PJ,
3221: ( SELECT PLL.po_line_id
3222: , SUM(PLL.amount_billed) total_amount_billed
3223: FROM po_line_locations PLL
3220: per_jobs_vl PJ,
3221: ( SELECT PLL.po_line_id
3222: , SUM(PLL.amount_billed) total_amount_billed
3223: FROM po_line_locations PLL
3224: , po_lines_all POL
3225: , po_headers_all poh
3226: WHERE poh.type_lookup_code = 'STANDARD'
3227: AND poh.po_header_id = POL.po_header_id
3228: AND POL.purchase_basis = 'TEMP LABOR'
3326: p_expiration_date => l_tl_line_rec.expiration_date
3327: );
3328:
3329: -- SQL What: Set the "Amount Billed notification sent" flag to Y.
3330: UPDATE po_lines_all
3331: SET svc_amount_notif_sent = 'Y',
3332: last_update_date = sysdate,
3333: last_updated_by = fnd_global.user_id
3334: WHERE po_line_id = l_tl_line_rec.po_line_id;
3350: p_expiration_date => l_tl_line_rec.expiration_date
3351: );
3352:
3353: -- SQL What: Set the "Assignment Completion notification sent" flag to Y.
3354: UPDATE po_lines_all
3355: SET svc_completion_notif_sent = 'Y',
3356: last_update_date = sysdate,
3357: last_updated_by = fnd_global.user_id
3358: WHERE po_line_id = l_tl_line_rec.po_line_id;
3398: -- type of reminder - ex. SVC_AMOUNT (for Amount Approaching Budget)
3399: --End of Comments
3400: -------------------------------------------------------------------------------
3401: PROCEDURE start_po_line_reminder_wf (
3402: p_po_line_id IN PO_LINES.po_line_id%TYPE,
3403: p_line_reminder_type IN VARCHAR2,
3404: p_requester_id IN NUMBER,
3405: p_contractor_or_job IN VARCHAR2,
3406: p_expiration_date IN DATE
3510: select POH.po_header_id,
3511: POH.segment1
3512: INTO l_po_header_id,
3513: l_document_number
3514: FROM po_lines POL,
3515: po_headers_all POH
3516: WHERE POL.po_line_id = p_po_line_id
3517: AND POL.po_header_id = POH.po_header_id;
3518:
3519: BEGIN
3520:
3521: select PRL.requisition_header_id
3522: INTO l_req_header_id
3523: FROM po_lines POL,
3524: po_line_locations_all PLL,
3525: po_requisition_lines_all PRL
3526: WHERE POL.po_line_id = p_po_line_id
3527: AND POL.po_line_id = PLL.po_line_id