APPS.AP_WFAPPROVAL_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 21

	SELECT chart_of_accounts_id
        INTO l_chart_of_accounts_id
        FROM gl_sets_of_books
       WHERE set_of_books_id = p_set_of_books_id;

Line: 81

	SELECT dist_code_combination_id,set_of_books_id
	INTO l_ccid,l_sob
	FROM ap_invoice_distributions_all
	WHERE invoice_distribution_id=p_dist_id;

Line: 86

	SELECT chart_of_accounts_id
        INTO l_chart_of_accounts_id
        FROM gl_sets_of_books
       WHERE set_of_books_id = l_sob;

Line: 147

	SELECT org_id
	INTO l_org_id
	FROM ap_invoices_all
	WHERE invoice_id = substr(itemkey, 1, instr(itemkey,'_')-1);

Line: 164

		SELECT count(invoice_distribution_id)
		INTO l_po_count
		FROM ap_invoice_distributions
		WHERE po_distribution_id is null
		AND invoice_id = substr(itemkey, 1, instr(itemkey,'_')-1);

Line: 174

                        UPDATE AP_INVOICES
                        SET wfapproval_status = 'NOT REQUIRED'
                        WHERE invoice_id = substr(itemkey, 1, instr(itemkey,'_')-1)
			AND wfapproval_status <> 'MANUALLY APPROVED';

Line: 193

        WF_CORE.CONTEXT('APINV','SELECT_APPROVER',itemtype, itemkey, to_char(actid), funcmode);

Line: 198

        WF_CORE.CONTEXT('APINV','SELECT_APPROVER',itemtype, itemkey, to_char(actid), funcmode);

Line: 203

        WF_CORE.CONTEXT('APINV','SELECT_APPROVER',itemtype, itemkey, to_char(actid), funcmode);

Line: 262

		SELECT count(*)
		INTO l_count
		FROM ap_inv_aprvl_hist
		WHERE invoice_id = l_invoice_id
		AND iteration = l_iteration
		AND RESPONSE <> 'MANUALLY APPROVED';

Line: 270

        		--update invoice header status
        		UPDATE AP_INVOICES
        		SET wfapproval_status = 'WFAPPROVED'
        		WHERE invoice_id = l_invoice_id
			AND wfapproval_status <> 'MANUALLY APPROVED';

Line: 276

			UPDATE AP_INVOICES
                        SET wfapproval_status = 'NOT REQUIRED'
                        WHERE invoice_id = l_invoice_id
                        AND wfapproval_status <> 'MANUALLY APPROVED';

Line: 332

		insert_history(itemtype,itemkey);

Line: 347

PROCEDURE update_history(itemtype IN VARCHAR2,
                        itemkey IN VARCHAR2,
                        actid   IN NUMBER,
                        funcmode IN VARCHAR2,
                        resultout  OUT NOCOPY VARCHAR2 ) IS

l_next_approver AME_UTIL.approverRecord;

Line: 419

	--update AME with response
	IF l_result = 'APPROVED' THEN
       		AME_API.updateApprovalStatus2(applicationIdIn => 200,
                                transactionIdIn     => to_char(l_invoice_id),
                                approvalStatusIn    => AME_UTIL.approvedStatus,
                                approverPersonIdIn  => l_approver_id,
                                approverUserIdIn    => NULL,
                                transactionTypeIn =>  'APINV');

Line: 429

	        AME_API.updateApprovalStatus2(applicationIdIn => 200,
                                transactionIdIn     => to_char(l_invoice_id),
                                approvalStatusIn    => AME_UTIL.rejectStatus,
                                approverPersonIdIn  => l_approver_id,
                                approverUserIdIn    => NULL,
                                transactionTypeIn =>  'APINV');

Line: 445

	--update the history table
	UPDATE AP_INV_APRVL_HIST
	SET	RESPONSE = l_result,
		APPROVER_COMMENTS = l_comments,
		AMOUNT_APPROVED = l_amount,
		LAST_UPDATE_DATE = sysdate,
		LAST_UPDATED_BY = l_user_id,
		LAST_UPDATE_LOGIN = l_login_id
	WHERE APPROVAL_HISTORY_ID = l_hist_id;

Line: 457

               UPDATE AP_INVOICES
               SET wfapproval_status = l_result
               WHERE invoice_id = substr(itemkey, 1, instr(itemkey,'_')-1)
		AND wfapproval_status <> 'MANUALLY APPROVED';

Line: 465

    Wf_Core.Context('APINV', 'update_history',
                     itemtype, itemkey, to_char(actid), l_debug_info);

Line: 469

END update_history;

Line: 472

PROCEDURE insert_history(itemtype IN VARCHAR2,
                        itemkey IN VARCHAR2 ) IS

l_next_approver AME_UTIL.approverRecord;

Line: 523

	SELECT AP_INV_APRVL_HIST_S.nextval
	INTO l_hist_id
	FROM dual;

Line: 528

        INSERT INTO  AP_INV_APRVL_HIST
	(APPROVAL_HISTORY_ID
        ,INVOICE_ID
        ,ITERATION
        ,RESPONSE
        ,APPROVER_ID
        ,APPROVER_NAME
        ,CREATED_BY
        ,CREATION_DATE
        ,LAST_UPDATE_DATE
        ,LAST_UPDATED_BY
        ,LAST_UPDATE_LOGIN
        ,ORG_ID
	,AMOUNT_APPROVED)
        VALUES (
	l_hist_id,
	l_invoice_id,
	l_iteration,
	'PENDING',
	l_approver_id,
	l_approver,
	nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1),
	sysdate,
	sysdate,
	nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1),
	nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1),
	l_org_id,
	l_amount);

Line: 564

    		Wf_Core.Context('APINV', 'insert_history',
                     itemtype, itemkey, l_debug_info);

Line: 568

END insert_history;

Line: 570

PROCEDURE insert_history(p_invoice_id  IN NUMBER,
                        p_iteration IN NUMBER,
                        p_org_id IN NUMBER,
                        p_status IN VARCHAR2) IS
	l_hist_id	NUMBER;

Line: 577

		--insert into the history table
		SELECT AP_INV_APRVL_HIST_S.nextval
        	INTO l_hist_id
        	FROM dual;

Line: 582

		SELECT invoice_amount
		INTO l_amount
		FROM AP_INVOICES_ALL
		WHERE invoice_id = p_invoice_id;

Line: 587

        	INSERT INTO  AP_INV_APRVL_HIST
        	(APPROVAL_HISTORY_ID
        	,INVOICE_ID
        	,ITERATION
        	,RESPONSE
        	,APPROVER_ID
        	,APPROVER_NAME
		,AMOUNT_APPROVED
        	,CREATED_BY
        	,CREATION_DATE
        	,LAST_UPDATE_DATE
        	,LAST_UPDATED_BY
        	,LAST_UPDATE_LOGIN
        	,ORG_ID)
        	VALUES (
        	l_hist_id,
        	p_invoice_id,
        	p_iteration,
		p_status,
        	 NULL,
        	FND_PROFILE.VALUE('USERNAME'),
		l_amount,
		TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
        	sysdate,
        	sysdate,
        	TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
        	TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
        	p_org_id);

Line: 618

END insert_history;

Line: 654

	--see if we have an HR api for this select
	SELECT supervisor_id, first_name, last_name
	INTO l_manager_id, l_esc_approver.first_name, l_esc_approver.last_name
	FROM per_employees_current_x
	WHERE employee_id = l_employee_id;

Line: 675

	l_esc_approver.api_insertion := ame_util.apiInsertion;

Line: 680

        /*AME_API.updateApprovalStatus2(200,
                                l_invoice_id,
                                ame_util.noResponseStatus,
                                l_employee_id,
                                null,
                                'APINV',
                                  l_esc_approver);*/

Line: 688

	AME_API.updateApprovalStatus2(applicationIdIn => 200,
                                transactionIdIn     => to_char(l_invoice_id),
                                approvalStatusIn    => AME_UTIL.noResponseStatus,
                                approverPersonIdIn  => l_employee_id,
                                approverUserIdIn    => NULL,
                                transactionTypeIn =>  'APINV',
				forwardeeIn       => l_esc_approver);

Line: 697

        UPDATE AP_INV_APRVL_HIST
        SET     RESPONSE = 'ESCALATED'
        WHERE APPROVAL_HISTORY_ID = l_hist_id;

Line: 718

	insert_history(itemtype,itemkey);

Line: 761

        SELECT org_id
        INTO l_org_id
        FROM ap_invoices_all
        WHERE invoice_id = substr(itemkey, 1, instr(itemkey,'_')-1);

Line: 776

	SELECT approval_iteration,
		vendor_id,
		vendor_site_id,
		invoice_amount,
		description,
		invoice_currency_code,
		org_id,
		invoice_id,
		invoice_num,
		invoice_date,
		requester_id
	INTO
		l_iteration,
		l_vendor_id,
		l_vendor_site_id,
		l_amount,
		l_description,
		l_currency,
		l_org_id,
		l_invoice_id,
		l_invoice_num,
		l_invoice_date,
		l_requester_id
	FROM AP_INVOICES
	WHERE invoice_id = substr(itemkey, 1, instr(itemkey,'_')-1);

Line: 802

	SELECT vendor_name
	INTO l_vendor_name
	FROM PO_VENDORS
	WHERE vendor_id = l_vendor_id;

Line: 807

        SELECT vendor_site_code
        INTO l_vendor_site_code
        FROM PO_VENDOR_SITES
        WHERE vendor_site_id = l_vendor_site_id;

Line: 812

	SELECT count(invoice_distribution_id)
	INTO l_po_count
	FROM ap_invoice_distributions
	WHERE invoice_id = l_invoice_id
	and po_distribution_id is not null;

Line: 819

		SELECT displayed_field
		INTO l_po_num
		FROM ap_lookup_codes
		WHERE lookup_code = 'MULTIPLE'
		AND lookup_type = 'NLS TRANSLATION';

Line: 825

		SELECT poh.segment1
		INTO l_po_num
		FROM ap_invoice_distributions aid,
		po_distributions pod,
		po_headers poh
		WHERE aid.invoice_id = l_invoice_id
		AND aid.po_distribution_id = pod.po_distribution_id
		AND pod.po_header_id = poh.po_header_id;

Line: 839

        	SELECT full_name
        	INTO l_requester_name
        	FROM per_all_people_f pap
        	WHERE person_id = l_requester_id
                and trunc(sysdate) between effective_start_date --bug3815124
                                     and nvl(effective_end_date,trunc(sysdate));

Line: 1037

			SELECT organization
			INTO l_return_val
			FROM PA_EXP_ORGS_IT
			WHERE organization_id=(SELECT expenditure_organization_id
					       FROM ap_invoices_all
					       WHERE invoice_id = p_invoice_id);

Line: 1046

			SELECT count(invoice_distribution_id)
			INTO l_count_pa_rel
			FROM ap_invoice_distributions_all
			WHERE invoice_id = p_invoice_id
			AND project_id is not null;

Line: 1063

			SELECT employee_number
			INTO l_return_val
			FROM per_all_people_f pap
			WHERE person_id = (SELECT ph.agent_id
					   FROM ap_invoice_distributions_all aid,
						po_distributions_all pd,
						po_headers_all ph
					   WHERE pd.po_distribution_id =
						aid.po_distribution_id
					   AND  pd.po_header_id = ph.po_header_id
					   AND aid.invoice_distribution_id =
									p_dist_id
					   AND pd.creation_date >= pap.effective_start_date
                                           AND pd.creation_date <= nvl(pap.effective_end_date,sysdate));

Line: 1081

			SELECT employee_number
                        INTO l_return_val
                        FROM per_all_people_f pap
			WHERE person_id = (SELECT pd.deliver_to_person_id
                                           FROM ap_invoice_distributions_all aid,
                                                po_distributions_all pd
                                           WHERE pd.po_distribution_id =
                                                aid.po_distribution_id
                                           AND aid.invoice_distribution_id =
                                                                        p_dist_id
					   AND pd.creation_date >= pap.effective_start_date
					   AND pd.creation_date <= nvl(pap.effective_end_date,sysdate));

Line: 1107

PROCEDURE Update_Invoice_Status(
                               p_invoice_id IN ap_invoices_all.invoice_id%TYPE) IS

PRAGMA autonomous_transaction;

Line: 1112

        UPDATE ap_inv_aprvl_hist_all
        SET RESPONSE ='CANCELLED'
        WHERE invoice_id = p_invoice_id
        AND response ='PENDING';

Line: 1117

END Update_Invoice_Status;