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.OZF_SD_UTIL_PVT SQL Statements

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

Line: 29


     select UTILIZATION_ID,CLAIM_AMOUNT_CURRENCY_CODE into l_util_id,l_from_currency from OZF_SD_BATCH_LINES_ALL where batch_line_id=p_batch_line_id;

Line: 33


     select currency_code into l_to_currency from ozf_sd_batch_headers_all
            where batch_id=(select batch_id from ozf_sd_batch_lines_all
                                   where batch_line_id = p_batch_line_id);

Line: 37


     select exchange_rate_date into l_conv_date from OZF_FUNDS_UTILIZED_ALL_B where utilization_id=l_util_id;

Line: 64


 select UTILIZATION_ID ,claim_amount,acctd_amount_remaining,univ_curr_amount_remaining,amount_remaining
 into  l_util_id, l_claim_amount ,l_acctd_amount_remaining,l_univ_curr_amount_remaining,l_amount_remaining
 from OZF_SD_BATCH_LINES_ALL
 where batch_line_id=p_batch_line_id;

Line: 71


	UPDATE OZF_FUNDS_UTILIZED_ALL_B
	SET amount_remaining=amount_remaining+l_amount_remaining,
        PLAN_CURR_AMOUNT_REMAINING=PLAN_CURR_AMOUNT_REMAINING+l_claim_amount,
	acctd_amount_remaining=acctd_amount_remaining+l_acctd_amount_remaining,
	univ_curr_amount_remaining=univ_curr_amount_remaining+l_amount_remaining

	WHERE UTILIZATION_ID=l_util_id;

Line: 79


        DELETE FROM OZF_SD_BATCH_LINES_ALL WHERE batch_line_id=p_batch_line_id;

Line: 194


  PROCEDURE UPDATE_SD_REQ_PRICES(p_request_number IN VARCHAR2,p_request_line_id IN NUMBER)
   IS
      l_req_header_id    NUMBER ;

Line: 201


    UPDATE OZF_SD_RES_DIST_PRICES_INTF SET REQUEST_LINE_ID=p_request_line_id
    WHERE REQUEST_NUMBER=p_request_number
    AND REQUEST_LINE_ID IS NULL;

Line: 207


   END UPDATE_SD_REQ_PRICES;

Line: 247


      SELECT	PRODINTF.REQUEST_LINE_ID INF_REQUEST_LINE_ID,
		PRODINTF.PROD_TYPE INF_PROD_TYPE,
		PRODINTF.PROD_REJECTION_CODE INF_REJ_CODE,
		PRODLINES.PRODUCT_CONTEXT LINE_PROD_CONTEXT,
		PROD.CONCATENATED_SEGMENTS LINE_PROD_CODE,
		PRODINTF.SUPP_PROD_CODE INF_PROD_CODE,
		PRODINTF.APPROVED_DISCOUNT_TYPE INTF_DISCOUNT_TYE,
		PRODLINES.REQUESTED_DISCOUNT_TYPE LINE_DISCOUNT_TYPE,
		PRODLINES.REQUESTED_DISCOUNT_CURRENCY LINE_DISCOUNT_CUR,
		PRODINTF.APPROVED_DISCOUNT_CURR INTF_CURR_CODE,
		NVL(PRODINTF.APPROVED_DISCOUNT_VALUE,PRODLINES.REQUESTED_DISCOUNT_VALUE) INTF_APPROVED_DISCOUNT_VALUE,
		NVL(PRODINTF.PROD_AUTHORIZED_QUANTITY,PRODLINES.MAX_QTY) INTF_APPROVED_QUANTITY
    FROM OZF_SD_RES_PROD_INTF PRODINTF,
	OZF_SD_REQUEST_LINES_ALL PRODLINES,
	mtl_system_items_b_kfv PROD
    where  NVL(PRODINTF.PROCESSED_FLAG,'N') <>'Y' and
    PRODINTF.REQUEST_NUMBER=p_request_number
    AND PRODLINES.REQUEST_HEADER_ID=request_header_id
    AND PRODINTF.REQUEST_LINE_ID = PRODLINES.REQUEST_LINE_ID
    AND PRODLINES.PRODUCT_CONTEXT = 'PRODUCT'
    AND PRODLINES.ORG_ID=PROD.ORGANIZATION_ID
    AND PRODLINES.INVENTORY_ITEM_ID=PROD.INVENTORY_ITEM_ID

    UNION
    SELECT	PRODINTF.REQUEST_LINE_ID INF_REQUEST_LINE_ID,
		PRODINTF.PROD_TYPE INF_PROD_TYPE,
		PRODINTF.PROD_REJECTION_CODE INF_REJ_CODE,
		PRODLINES.PRODUCT_CONTEXT LINE_PROD_CONTEXT,
		NVL(D.CATEGORY_DESC, 'NA') PROD_CODE,
		PRODINTF.SUPP_PROD_CODE INF_PROD_CODE,
		PRODINTF.APPROVED_DISCOUNT_TYPE INTF_DISCOUNT_TYE,
		PRODLINES.REQUESTED_DISCOUNT_TYPE LINE_DISCOUNT_TYPE,
		PRODLINES.REQUESTED_DISCOUNT_CURRENCY LINE_DISCOUNT_CUR,
		PRODINTF.APPROVED_DISCOUNT_CURR INTF_CURR_CODE,
		NVL(PRODINTF.APPROVED_DISCOUNT_VALUE,PRODLINES.REQUESTED_DISCOUNT_VALUE) INTF_APPROVED_DISCOUNT_VALUE,
		NVL(PRODINTF.PROD_AUTHORIZED_QUANTITY,PRODLINES.MAX_QTY) INTF_APPROVED_QUANTITY
    FROM OZF_SD_RES_PROD_INTF PRODINTF,
	OZF_SD_REQUEST_LINES_ALL PRODLINES,
	ENI_PROD_DEN_HRCHY_PARENTS_V D
    WHERE  NVL(PRODINTF.PROCESSED_FLAG,'N') <>'Y' and
    PRODINTF.REQUEST_NUMBER=p_request_number
    AND PRODLINES.REQUEST_HEADER_ID=request_header_id
    AND PRODINTF.REQUEST_LINE_ID = PRODLINES.REQUEST_LINE_ID
    AND PRODLINES.PRODUCT_CONTEXT ='PRODUCT_CATEGORY'
    AND PRODLINES.PROD_CATG_ID=D.CATEGORY_ID
    AND PRODLINES.PRODUCT_CAT_SET_ID = D.CATEGORY_SET_ID ;

Line: 301


	Select count(*) into l_req_number_count from OZF_SD_REQUEST_HEADERS_ALL_B
	where REQUEST_NUMBER = p_request_number ;

Line: 309


		-- DO THE MASS UPDATE FOR THE INTERFACE TABLES
		UPDATE OZF_SD_RES_HEADER_INTF SET PROCESSED_FLAG='Y',ERROR_TXT=x_msg_data WHERE REQUEST_NUMBER=p_request_number and PROCESSED_FLAG in('N',null) ;

Line: 312


		UPDATE OZF_SD_RES_CUST_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and PROCESSED_FLAG in('N',null) ;

Line: 314


		UPDATE OZF_SD_RES_PROD_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and PROCESSED_FLAG in('N',null) ;

Line: 316


		UPDATE OZF_SD_RES_DIST_PRICES_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and PROCESSED_FLAG in('N',null) ;

Line: 324


	Select REQ.request_header_id,STATUS.SYSTEM_STATUS_CODE,REQ.request_currency_code into l_req_id,l_req_status,l_curr_code
	from OZF_SD_REQUEST_HEADERS_ALL_B REQ,AMS_USER_STATUSES_VL STATUS
	where  REQ.REQUEST_NUMBER = p_request_number
        AND REQ.USER_STATUS_ID=STATUS.USER_STATUS_ID ;

Line: 333


	      -- Update the non responded lines as 'rejected' and rejection code as ' No response from vendor'
	      UPDATE OZF_SD_REQUEST_LINES_ALL SET VENDOR_APPROVED_FLAG='N',REJECTION_CODE='OZF_SD_NO_RESPONSE'
	      WHERE REQUEST_HEADER_ID=request_header_id
	      AND REQUEST_LINE_ID IN (	SELECT REQUEST_LINE_ID FROM OZF_SD_REQUEST_LINES_ALL
					WHERE REQUEST_HEADER_ID=request_header_id
					MINUS
					SELECT REQUEST_LINE_ID FROM OZF_SD_RES_PROD_INTF
					WHERE REQUEST_NUMBER=p_request_number
					AND NVL(PROCESSED_FLAG,'N') <>'Y') ;

Line: 357


			   UPDATE OZF_SD_RES_PROD_INTF SET ERROR_TXT = l_msg_data
				WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID
				AND NVL(PROCESSED_FLAG,'N') <>'Y';

Line: 374


			   UPDATE OZF_SD_RES_PROD_INTF SET ERROR_TXT = l_msg_data
			      WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID
			      AND NVL(PROCESSED_FLAG,'N') <>'Y' ;

Line: 391


			   UPDATE OZF_SD_RES_PROD_INTF SET ERROR_TXT = 'Discount type mismatch'
				  WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID
				  AND NVL(PROCESSED_FLAG,'N') <>'Y' ;

Line: 412


			    SELECT count(*) INTO l_currency_count
			    FROM FND_CURRENCIES
			    WHERE currency_code = l_curr_code;

Line: 420


				UPDATE OZF_SD_RES_PROD_INTF SET ERROR_TXT = 'Currency code mismatch'
        			        WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID
					AND NVL(PROCESSED_FLAG,'N') <>'Y' ;

Line: 431


			-- Check the validation status of line : If an valid line then update the base table data
			IF l_line_status <> 'E'	THEN
				-- Update the status for product line as 'Rejected'
				IF PROD_REC.INF_REJ_CODE IS NOT NULL THEN
					 UPDATE OZF_SD_REQUEST_LINES_ALL SET REJECTION_CODE=PROD_REC.INF_REJ_CODE,VENDOR_APPROVED_FLAG='N'
					 WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID;

Line: 439


					-- Update the approved amount,approved currency and type in the base line table
					UPDATE OZF_SD_REQUEST_LINES_ALL SET APPROVED_DISCOUNT_TYPE=PROD_REC.LINE_DISCOUNT_TYPE,
									    APPROVED_DISCOUNT_VALUE=PROD_REC.INTF_APPROVED_DISCOUNT_VALUE,
									    APPROVED_MAX_QTY=PROD_REC.INTF_APPROVED_QUANTITY,
									    APPROVED_DISCOUNT_CURRENCY=l_curr_code
					 WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID;

Line: 451


		-- UPDATE THE REQUEST STATUS IN HEADER TABLE : OZF_SD_REQUEST_HEADERS_ALL_B
		SELECT COUNT(*) INTO l_approved_lines FROM OZF_SD_REQUEST_LINES_ALL
		WHERE REQUEST_HEADER_ID=l_req_id
		AND VENDOR_APPROVED_FLAG = 'Y' ;

Line: 458


			SELECT user_status_id INTO l_request_status FROM ams_user_statuses_vl
					where system_status_TYPE='OZF_SD_REQUEST_STATUS'
						and SYSTEM_STATUS_CODE='SUPPLIER_APPROVED'
						and default_flag='Y'
						and enabled_flag='Y' ;

Line: 464


			SELECT user_status_id INTO l_request_status FROM ams_user_statuses_vl
					where system_status_TYPE='OZF_SD_REQUEST_STATUS'
						and SYSTEM_STATUS_CODE='SUPPLIER_REJECTED'
						and default_flag='Y'
						and enabled_flag='Y' ;

Line: 471


		SELECT AUTH_NUMBER INTO l_authorization_number FROM OZF_SD_RES_HEADER_INTF WHERE request_number=p_request_number
		AND NVL(PROCESSED_FLAG,'N') <>'Y' ;

Line: 474


		UPDATE OZF_SD_REQUEST_HEADERS_ALL_B SET user_status_id= l_request_status,AUTHORIZATION_NUMBER=l_authorization_number
		WHERE REQUEST_HEADER_ID=l_req_id ;

Line: 483


		UPDATE OZF_SD_RES_HEADER_INTF SET ERROR_TXT=x_msg_data WHERE REQUEST_NUMBER=p_request_number
		and NVL(PROCESSED_FLAG,'N') <>'Y';

Line: 490


	-- DO THE MASS UPDATE FOR THE INTERFACE TABLES

	UPDATE OZF_SD_RES_HEADER_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';

Line: 493


	UPDATE OZF_SD_RES_CUST_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';

Line: 494


	UPDATE OZF_SD_RES_PROD_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';

Line: 495


	UPDATE OZF_SD_RES_DIST_PRICES_INTF SET PROCESSED_FLAG='Y' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';

Line: 501


	-- DO THE MASS UPDATE FOR THE INTERFACE TABLES
	UPDATE OZF_SD_RES_HEADER_INTF SET PROCESSED_FLAG='Y',ERROR_TXT='Error' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';

Line: 503


	UPDATE OZF_SD_RES_CUST_INTF SET PROCESSED_FLAG='Y',ERROR_TXT='Error' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';

Line: 504


	UPDATE OZF_SD_RES_PROD_INTF SET PROCESSED_FLAG='Y',ERROR_TXT='Error' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';

Line: 505


	UPDATE OZF_SD_RES_DIST_PRICES_INTF SET PROCESSED_FLAG='Y',ERROR_TXT='Error' WHERE REQUEST_NUMBER=p_request_number and NVL(PROCESSED_FLAG,'N') <>'Y';

Line: 594


PROCEDURE UPDATE_SD_REQ_STALE_DATA(p_request_number IN VARCHAR2)
 IS
  l_req_number_count NUMBER :=0;

Line: 599


	UPDATE OZF_SD_RES_HEADER_INTF SET PROCESSED_FLAG='S' WHERE REQUEST_NUMBER=p_request_number AND PROCESSED_FLAG='N' ;

Line: 600


	UPDATE OZF_SD_RES_CUST_INTF SET PROCESSED_FLAG='S' WHERE REQUEST_NUMBER=p_request_number AND PROCESSED_FLAG='N' ;

Line: 601


	UPDATE OZF_SD_RES_PROD_INTF SET PROCESSED_FLAG='S' WHERE REQUEST_NUMBER=p_request_number AND PROCESSED_FLAG='N' ;

Line: 602


	UPDATE OZF_SD_RES_DIST_PRICES_INTF SET PROCESSED_FLAG='S' WHERE REQUEST_NUMBER=p_request_number AND PROCESSED_FLAG='N' ;

Line: 605


 END UPDATE_SD_REQ_STALE_DATA;