eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AP_WEB_UTILITIES_PKG SQL Statements

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

Line: 41


      select nvl(version, C_WebApplicationVersion)
      into l_version
      from fnd_responsibility
      where application_id = fnd_global.resp_appl_id() and responsibility_id = fnd_global.resp_id();

Line: 2987


   SELECT DEFAULT_APPROVER_ID, DEFAULT_SOURCE into p_ApproverId, l_DefaultSource
   FROM AP_WEB_PREFERENCES
   WHERE EMPLOYEE_ID = p_EmpId;

Line: 2995


        SELECT OVERRIDE_APPROVER_ID into p_ApproverId
        FROM
        (    SELECT OVERRIDE_APPROVER_ID
             FROM AP_EXPENSE_REPORT_HEADERS_ALL
             WHERE EMPLOYEE_ID = p_EmpId AND BOTHPAY_PARENT_ID IS  NULL
             ORDER BY report_header_id DESC
        ) WHERE ROWNUM=1;

Line: 3137


         RETURN 'EXISTS (SELECT 1
                         FROM mo_glob_org_access_tmp oa
                         WHERE oa.organization_id = org_id
                        )
                 OR    org_id = -99';

Line: 3169


PROCEDURE UpdateExpenseStatusCode(
        p_invoice_id AP_INVOICES_ALL.invoice_id%TYPE,
 	p_pay_status_flag       AP_INVOICES_ALL.payment_status_flag%TYPE DEFAULT NULL
) IS

  t_paid_status                 CONSTANT VARCHAR2(10) := 'PAID';

Line: 3178


  l_expenses_to_update          ExpensesToUpdate;

Line: 3210


  AP_WEB_UTILITIES_PKG.GetExpensesToUpdate(p_invoice_id, p_pay_status_flag, l_expenses_to_update);

Line: 3213


    FETCH l_expenses_to_update
    INTO  l_identifier, l_report_header_id, l_invoice_status;

Line: 3216


    EXIT WHEN l_expenses_to_update%NOTFOUND;

Line: 3305


    UPDATE ap_expense_report_headers_all
    SET    expense_status_code = l_parent_report_status,
 	   last_update_date = sysdate,
 	   last_updated_by = nvl(l_web_user_id, last_updated_by),
	   -- Bug: 10044058, to update expense_last_status_date
	   expense_last_status_date = sysdate
    WHERE report_header_id = l_parent_report_header_id;

Line: 3318


    UPDATE ap_expense_report_headers_all
    SET    expense_status_code = l_main_report_status,
 	   last_update_date = sysdate,
 	   last_updated_by = nvl(l_web_user_id, last_updated_by),
	   -- Bug: 10044058, to update expense_last_status_date
	   expense_last_status_date = sysdate
    WHERE report_header_id = l_main_report_header_id;

Line: 3328


END UpdateExpenseStatusCode;

Line: 3356


 PROCEDURE GetExpensesToUpdate(p_invoice_id         IN    AP_INVOICES_ALL.invoice_id%TYPE,
			       p_pay_status_flag    IN    AP_INVOICES_ALL.payment_status_flag%TYPE,
			       p_expenses_to_update OUT NOCOPY ExpensesToUpdate) IS
 BEGIN
  IF (p_pay_status_flag IS NULL) THEN
   -- For the trigger on AP_INVOICE_PAYMENTS_ALL
   OPEN p_expenses_to_update FOR
    -- cc in bp
    SELECT 'PARENT' Identifier,
	   parent_aerh.report_header_id report_header_id,
	   DECODE(parent_APS.GROSS_AMOUNT ,0,'PAID',
				   DECODE(parent_AI.Payment_status_flag,
					    'Y','PAID','N','INVOICED','P','PARPAID',NULL) ) invoice_status
    FROM ap_expense_report_headers_all parent_aerh,
	 ap_expense_report_headers_all main_aerh,
	 ap_invoices_all parent_ai,
	 ap_payment_schedules_all parent_aps
 WHERE
	  main_aerh.bothpay_parent_id = parent_aerh.report_header_id (+) and
	  parent_aerh.vouchno = parent_ai.invoice_id and
	  parent_ai.invoice_id = parent_aps.invoice_id and
	  main_aerh.vouchno = p_invoice_id
    UNION
    -- main/actual cash or cc
    SELECT 'MAIN' Identifier,
	   main_aerh.report_header_id report_header_id,
	   DECODE(main_APS.GROSS_AMOUNT ,0,'PAID',
				   DECODE(main_AI.Payment_status_flag,
					    'Y','PAID','N','INVOICED','P','PARPAID',NULL) ) invoice_status
    FROM
	 ap_expense_report_headers_all main_aerh,
	 ap_invoices_all main_ai,
	 ap_payment_schedules_all main_aps
    WHERE
	 main_aerh.vouchno = main_ai.invoice_id and
	 main_ai.invoice_id = main_aps.invoice_id and
	 main_aerh.vouchno = p_invoice_id
    UNION
    -- cash in bp
    SELECT 'CHILD' Identifier,
	   child_aerh.report_header_id report_header_id,
	   DECODE(child_APS.GROSS_AMOUNT ,0,'PAID',
				   DECODE(child_AI.Payment_status_flag,
					    'Y','PAID','N','INVOICED','P','PARPAID',NULL) ) invoice_status
    FROM
	 ap_expense_report_headers_all child_aerh,
	 ap_expense_report_headers_all main_aerh,
	 ap_invoices_all child_ai,
	 ap_payment_schedules_all child_aps
    WHERE child_aerh.bothpay_parent_id (+) = main_aerh.report_header_id and
	 child_aerh.vouchno = child_ai.invoice_id and
	 child_ai.invoice_id = child_aps.invoice_id and
	 main_aerh.vouchno = p_invoice_id;

Line: 3410


    OPEN p_expenses_to_update FOR
     -- For the trigger on AP_INVOICES_ALL
     -- cc in bp
     SELECT 'PARENT' Identifier,
	    parent_aerh.report_header_id report_header_id,
	    DECODE(parent_APS.GROSS_AMOUNT ,0,'PAID',
				   DECODE(p_pay_status_flag,
					    'Y','PAID','N','INVOICED','P','PARPAID',NULL) ) invoice_status
     FROM ap_expense_report_headers_all parent_aerh,
	  ap_expense_report_headers_all main_aerh,
	  ap_payment_schedules_all parent_aps
     WHERE
	  main_aerh.bothpay_parent_id = parent_aerh.report_header_id (+) and
	  parent_aerh.vouchno = parent_aps.invoice_id and
	  main_aerh.vouchno = p_invoice_id
     UNION
     -- main/actual cash or cc
     SELECT 'MAIN' Identifier,
	    main_aerh.report_header_id report_header_id,
	    DECODE(main_APS.GROSS_AMOUNT ,0,'PAID',
				   DECODE(p_pay_status_flag,
					    'Y','PAID','N','INVOICED','P','PARPAID',NULL) ) invoice_status
     FROM
	  ap_expense_report_headers_all main_aerh,
	  ap_payment_schedules_all main_aps
     WHERE
	  main_aerh.vouchno =  main_aps.invoice_id and
	  main_aerh.vouchno = p_invoice_id
     UNION
     -- cash in bp
     SELECT 'CHILD' Identifier,
	  child_aerh.report_header_id report_header_id,
	  DECODE(child_APS.GROSS_AMOUNT ,0,'PAID',
				 DECODE(p_pay_status_flag,
					  'Y','PAID','N','INVOICED','P','PARPAID',NULL) ) invoice_status
     FROM
	 ap_expense_report_headers_all child_aerh,
	 ap_expense_report_headers_all main_aerh,
	 ap_payment_schedules_all child_aps
     WHERE child_aerh.bothpay_parent_id (+) = main_aerh.report_header_id and
	   child_aerh.vouchno = child_aps.invoice_id and
	   main_aerh.vouchno = p_invoice_id;

Line: 3456


 END GetExpensesToUpdate;

Line: 3471


  select  decode(FC.minimum_accountable_unit,
            null, decode(FC.precision, null, null, round(P_Amount,FC.precision)),
                  round(P_Amount/FC.minimum_accountable_unit) *
                               FC.minimum_accountable_unit)
  into    l_rounded_amount
  from    fnd_currencies FC
  where   FC.currency_code = P_Currency_Code;

Line: 3488


PROCEDURE UpdateImageReceiptStatus(p_report_header_id IN NUMBER) IS
l_mgr_appr_flag	VARCHAR2(10);

Line: 3496


  SELECT nvl(workflow_approved_flag,'X') into l_mgr_appr_flag FROM AP_EXPENSE_REPORT_HEADERS_ALL
  WHERE report_header_id = p_report_header_id;

Line: 3499


  UPDATE ap_expense_report_headers_all SET image_receipts_status = 'RECEIVED',
	image_receipts_received_date = sysdate,
	last_update_date = sysdate,
        last_updated_by = Decode(Nvl(fnd_global.user_id,-1),-1,last_updated_by,fnd_global.user_id)
	WHERE report_header_id = to_number(p_report_header_id);

Line: 3505


    SELECT count(*) INTO l_org_count
    FROM ap_expense_report_lines_all
    WHERE report_header_id = to_number(p_report_header_id)
    AND NVL(receipt_required_flag, 'N') = 'Y';

Line: 3511


      UPDATE ap_expense_report_headers_all
      SET receipts_status = 'NOT_REQUIRED'
      WHERE report_header_id = to_number(p_report_header_id)
      and receipts_status IN ('OVERDUE', 'MISSING', 'REQUIRED');

Line: 3524


  select nvl(rs.recpt_assign_stage_code,'X') into l_stage_code
    from   ap_expense_report_headers_all aerh,
           ap_aud_rule_sets rs,
           ap_aud_rule_assignments_all rsa
    where aerh.report_header_id = p_report_header_id
    and   aerh.org_id = rsa.org_id
    and   rsa.rule_set_id = rs.rule_set_id
    and   rs.rule_set_type = 'RULE'
    and   TRUNC(SYSDATE)
            BETWEEN TRUNC(NVL(rsa.START_DATE,SYSDATE))
            AND     TRUNC(NVL(rsa.END_DATE,SYSDATE));

Line: 3540


END UpdateImageReceiptStatus;

Line: 3551


    SELECT auditor_id INTO l_auditor_id FROM ap_aud_queues WHERE expense_report_id = p_report_header_id;

Line: 3558


    SELECT nvl(workflow_approved_flag,'X') into l_mgr_appr_flag FROM AP_EXPENSE_REPORT_HEADERS_ALL
    WHERE report_header_id = p_report_header_id;

Line: 3560


    select nvl(rs.recpt_assign_stage_code,'X'), NVL(rs.AUD_IMG_RECEIPT_REQUIRED, 'N') ,
    nvl(rs.AUD_PAPER_RECEIPT_REQUIRED, 'N')
    into l_stage_code, l_aud_img_required, l_aud_org_required
    from   ap_expense_report_headers_all aerh,
           ap_aud_rule_sets rs,
           ap_aud_rule_assignments_all rsa
    where aerh.report_header_id = p_report_header_id
    and   aerh.org_id = rsa.org_id
    and   rsa.rule_set_id = rs.rule_set_id
    and   rs.rule_set_type = 'RULE'
    and   TRUNC(SYSDATE)
            BETWEEN TRUNC(NVL(rsa.START_DATE,SYSDATE))
            AND     TRUNC(NVL(rsa.END_DATE,SYSDATE));

Line: 3590


CURSOR line_cursor IS select report_line_id, NVL(image_receipt_required_flag,'N') image_receipt_required_flag
FROM ap_expense_report_lines_all where report_header_id = p_report_header_id
AND (itemization_parent_id is null or itemization_parent_id = -1);

Line: 3642


  SELECT 1 FROM FND_ATTACHED_DOCUMENTS
  WHERE entity_name = p_entity_name AND pk1_value = p_value AND ROWNUM = 1;

Line: 3663


  SELECT invoice_num FROM AP_EXPENSE_REPORT_HEADERS_ALL
  WHERE shortpay_parent_id = p_report_header_id
  AND receipts_status = 'IN_PARENT_PACKET';

Line: 3688


  SELECT rs.*
  FROM AP_AUD_RULE_SETS rs,
       AP_AUD_RULE_ASSIGNMENTS_ALL rsa
  WHERE rsa.org_id = p_org_id
  AND   rsa.rule_set_id = rs.rule_set_id
  AND   rs.rule_set_type = 'RECEIPT'
  AND   TRUNC(p_report_date) BETWEEN TRUNC(NVL(rsa.start_date,p_report_date)) AND TRUNC(NVL(rsa.end_date,p_report_date));

Line: 3707


    SELECT NVL(report_submitted_date, sysdate) INTO l_report_date
    FROM AP_EXPENSE_REPORT_HEADERS_ALL WHERE report_header_id = p_report_header_id;

Line: 3735


      SELECT DECODE(image_receipts_status,'NOT_REQUIRED','N','RECEIVED','N','Y') INTO l_receipts_status
      FROM AP_EXPENSE_REPORT_HEADERS_ALL WHERE report_header_id = p_report_header_id;

Line: 3740


      SELECT DECODE(receipts_status,'NOT_REQUIRED','N','RECEIVED','N','Y') INTO l_receipts_status
      FROM AP_EXPENSE_REPORT_HEADERS_ALL WHERE report_header_id = p_report_header_id;

Line: 3758


   select count(1) INTO l_violation_count
    from AP_POL_VIOLATIONS_ALL
    where report_header_id = p_report_header_id;

Line: 3779


  select count(1) INTO l_non_cc_line_count
    from ap_expense_report_lines_all aerl
    where aerl.report_header_id = p_report_header_id
    and   (     CREDIT_CARD_TRX_ID is null
           OR (     CREDIT_CARD_TRX_ID is not null
                AND (NVL(receipt_required_flag,'N') = 'Y' OR NVL(image_receipt_required_flag,'N') = 'Y')
              )
          );

Line: 3805


   select count(1) INTO l_non_pdm_line_count
    from ap_expense_report_lines_all aerl
    where aerl.report_header_id = p_report_header_id
    and   NVL(aerl.category_code,'NONE') not in ('PER_DIEM','MILEAGE');

Line: 3814


    select count(1) INTO l_rr_line_count
    from ap_expense_report_lines_all aerl
    where aerl.report_header_id = p_report_header_id
    and   (nvl(aerl.receipt_required_flag, 'N') = 'Y' OR  nvl(aerl.image_receipt_required_flag, 'N') = 'Y');

Line: 3841


        UpdateImageReceiptStatus(p_report_header_id);

Line: 3853


	SELECT nvl(receipts_status, 'NOT_REQUIRED'), nvl(image_receipts_status, 'NOT_REQUIRED')
        INTO l_receipts_status, l_img_receipts_status
        FROM ap_expense_report_headers_all
        WHERE report_header_id = p_report_header_id;