[Home] [Help]
233: l_req_id NUMBER :=0 ;
234: l_req_number_count NUMBER ;
235: l_req_status AMS_USER_STATUSES_B.SYSTEM_STATUS_CODE%TYPE;
236: l_request_status OZF_SD_REQUEST_HEADERS_ALL_B.USER_STATUS_ID%TYPE;
237: l_curr_prod_context OZF_SD_REQUEST_LINES_ALL.PRODUCT_CONTEXT%TYPE ;
238: l_curr_code OZF_SD_REQUEST_HEADERS_ALL_B.REQUEST_CURRENCY_CODE%TYPE;
239: l_approved_lines NUMBER :=0;
240: l_error_count NUMBER := 0 ;
241: l_currency_count NUMBER :=0 ;
256: PRODINTF.APPROVED_DISCOUNT_CURR INTF_CURR_CODE,
257: NVL(PRODINTF.APPROVED_DISCOUNT_VALUE,PRODLINES.REQUESTED_DISCOUNT_VALUE) INTF_APPROVED_DISCOUNT_VALUE,
258: NVL(PRODINTF.PROD_AUTHORIZED_QUANTITY,PRODLINES.MAX_QTY) INTF_APPROVED_QUANTITY
259: FROM OZF_SD_RES_PROD_INTF PRODINTF,
260: OZF_SD_REQUEST_LINES_ALL PRODLINES,
261: mtl_system_items_b_kfv PROD
262: where NVL(PRODINTF.PROCESSED_FLAG,'N') <>'Y' and
263: PRODINTF.REQUEST_NUMBER=p_request_number
264: AND PRODLINES.REQUEST_HEADER_ID=request_header_id
280: PRODINTF.APPROVED_DISCOUNT_CURR INTF_CURR_CODE,
281: NVL(PRODINTF.APPROVED_DISCOUNT_VALUE,PRODLINES.REQUESTED_DISCOUNT_VALUE) INTF_APPROVED_DISCOUNT_VALUE,
282: NVL(PRODINTF.PROD_AUTHORIZED_QUANTITY,PRODLINES.MAX_QTY) INTF_APPROVED_QUANTITY
283: FROM OZF_SD_RES_PROD_INTF PRODINTF,
284: OZF_SD_REQUEST_LINES_ALL PRODLINES,
285: ENI_PROD_DEN_HRCHY_PARENTS_V D
286: WHERE NVL(PRODINTF.PROCESSED_FLAG,'N') <>'Y' and
287: PRODINTF.REQUEST_NUMBER=p_request_number
288: AND PRODLINES.REQUEST_HEADER_ID=request_header_id
330: -- If the request status is "PENDING_SUPPLIER_APPROVAL" then process the product lines in loop
331: IF l_req_status='PENDING_SUPPLIER_APPROVAL' THEN
332:
333: -- Update the non responded lines as 'rejected' and rejection code as ' No response from vendor'
334: UPDATE OZF_SD_REQUEST_LINES_ALL SET VENDOR_APPROVED_FLAG='N',REJECTION_CODE='OZF_SD_NO_RESPONSE'
335: WHERE REQUEST_HEADER_ID=request_header_id
336: AND REQUEST_LINE_ID IN ( SELECT REQUEST_LINE_ID FROM OZF_SD_REQUEST_LINES_ALL
337: WHERE REQUEST_HEADER_ID=request_header_id
338: MINUS
332:
333: -- Update the non responded lines as 'rejected' and rejection code as ' No response from vendor'
334: UPDATE OZF_SD_REQUEST_LINES_ALL SET VENDOR_APPROVED_FLAG='N',REJECTION_CODE='OZF_SD_NO_RESPONSE'
335: WHERE REQUEST_HEADER_ID=request_header_id
336: AND REQUEST_LINE_ID IN ( SELECT REQUEST_LINE_ID FROM OZF_SD_REQUEST_LINES_ALL
337: WHERE REQUEST_HEADER_ID=request_header_id
338: MINUS
339: SELECT REQUEST_LINE_ID FROM OZF_SD_RES_PROD_INTF
340: WHERE REQUEST_NUMBER=p_request_number
431: -- Check the validation status of line : If an valid line then update the base table data
432: IF l_line_status <> 'E' THEN
433: -- Update the status for product line as 'Rejected'
434: IF PROD_REC.INF_REJ_CODE IS NOT NULL THEN
435: UPDATE OZF_SD_REQUEST_LINES_ALL SET REJECTION_CODE=PROD_REC.INF_REJ_CODE,VENDOR_APPROVED_FLAG='N'
436: WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID;
437:
438: END IF;
439: -- Update the approved amount,approved currency and type in the base line table
436: WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID;
437:
438: END IF;
439: -- Update the approved amount,approved currency and type in the base line table
440: UPDATE OZF_SD_REQUEST_LINES_ALL SET APPROVED_DISCOUNT_TYPE=PROD_REC.LINE_DISCOUNT_TYPE,
441: APPROVED_DISCOUNT_VALUE=PROD_REC.INTF_APPROVED_DISCOUNT_VALUE,
442: APPROVED_MAX_QTY=PROD_REC.INTF_APPROVED_QUANTITY,
443: APPROVED_DISCOUNT_CURRENCY=l_curr_code
444: WHERE REQUEST_LINE_ID=PROD_REC.INF_REQUEST_LINE_ID;
448: END LOOP;
449:
450:
451: -- UPDATE THE REQUEST STATUS IN HEADER TABLE : OZF_SD_REQUEST_HEADERS_ALL_B
452: SELECT COUNT(*) INTO l_approved_lines FROM OZF_SD_REQUEST_LINES_ALL
453: WHERE REQUEST_HEADER_ID=l_req_id
454: AND VENDOR_APPROVED_FLAG = 'Y' ;
455:
456: