PO权限控制查询

CREATE OR REPLACE FUNCTION CUX_PO_ACCESS(V_AGENT_ID IN NUMBER,V_USER_ID IN NUMBER) RETURN VARCHAR2ISl_exists varchar2(10);BEGINbeginselect'Y'into l_existsfrom dualwhere V_AGENT_ID in (select ppf.person_idfrom per_all_people_f ppf,per_all_assignments_f paf,fnd_user fuwhere ppf.person_id = paf.person_idand sysdate betweengreatest(ppf.effective_start_date,paf.effective_start_date)andleast(nvl(ppf.effective_end_date,sysdate +1),nvl(paf.effective_end_date,sysdate + 1))and fu.employee_id = ppf.person_id-- connect by ppf.person_id = PRIORpaf.supervisor_id-- update根据上一层找下一层(主管去找对应的雇员)connect by paf.supervisor_id = PRIORppf.person_idSTART WITH er_id = V_USER_ID);exceptionwhen others thenl_exists := 'N';end;return l_exists;END CUX_PO_ACCESS;CREATE OR REPLACE VIEW PO_HEADERS_INQ_V(row_id, po_release_flag, creation_date, acceptance_due_date,amount_limit, approved_date, authorization_status,blanket_total_amount, closed_date, comments, end_date, end_date_active, firm_date, government_context, min_release_amount, note_to_authorizer, note_to_receiver, note_to_vendor, print_count, printed_date,quote_vendor_quote_number, quote_warning_delay,quote_warning_delay_unit, rate, rate_date, rate_type, reply_date, revised_date, revision_num, rfq_close_date, start_date,start_date_active, vendor_order_num, agent_id, bill_to_location_id, from_header_id, po_header_id, ship_to_location_id, terms_id,vendor_contact_id, vendor_id, vendor_site_id, closed_code,currency_code, firm_status_lookup_code, fob_lookup_code,freight_terms_lookup_code, from_type_lookup_code, quotation_class_code, quote_type_lookup_code, reply_method_lookup_code, ship_via_lookup_code, status_lookup_code, type_lookup_code, ussgl_transaction_code, acceptance_required_flag, approval_required_flag, approved_flag,cancel_flag, confirming_order_flag, enabled_flag, frozen_flag,summary_flag, user_hold_flag, created_by, order_date, last_updated_by, last_update_date, last_update_login, program_application_id,program_id, program_update_date, request_id, po_num, segment2, segment3, segment4, segment5, attribute_category, attribute1, attribute2,attribute3, attribute4, attribute5, attribute6, attribute7, attribute8, attribute9, attribute10, attribute11, attribute12, attribute13,attribute14, attribute15, doc_type_name, can_preparer_approve_flag, security_level_code, vendor_name, type_1099, vat_code, vendor_site_code, address_line1, address_line2, address_line3, city, state, zip, country, phone, fax, vendor_contact, terms_name, ship_to_location,bill_to_location, rate_conversion_type, authorization_status_dsp,fob_dsp, freight_terms_dsp, closed_code_dsp, cancel_date, cancel_reason, cancelled_by, hold_by, hold_date, hold_reason, release_num, release_type, po_release_id, pcard_id, price_update_tolerance, pay_on_code,pay_on_dsp, global_agreement_flag, enable_all_sites, owning_org_id,org_id, cbc_accounting_date, consigned_consumption_flag,shipping_control, shipping_control_dsp, conterms_exist_flag,conterms_articles_upd_date, conterms_deliv_upd_date, change_summary, pending_signature_flag, style_id)ASSELECT POH.ROWID,'PO',POH.CREATION_DATE,POH.ACCEPTANCE_DUE_DATE,POH.AMOUNT_LIMIT,POH.APPROVED_DATE,NVL(POH.AUTHORIZATION_STATUS, 'INCOMPLETE'), POH.BLANKET_TOTAL_AMOUNT,POH.CLOSED_DATE,MENTS,POH.END_DATE,POH.END_DATE_ACTIVE,POH.FIRM_DATE,ERNMENT_CONTEXT,POH.MIN_RELEASE_AMOUNT,POH.NOTE_TO_AUTHORIZER,POH.NOTE_TO_RECEIVER,POH.NOTE_TO_VENDOR,POH.PRINT_COUNT,POH.PRINTED_DATE,POH.QUOTE_VENDOR_QUOTE_NUMBER,POH.QUOTE_WARNING_DELAY,POH.QUOTE_WARNING_DELAY_UNIT,POH.RATE,POH.RATE_DATE,POH.RATE_TYPE,POH.REPLY_DATE,POH.REVISED_DATE,POH.REVISION_NUM,POH.RFQ_CLOSE_DATE,POH.START_DATE,POH.START_DATE_ACTIVE,POH.VENDOR_ORDER_NUM,POH.AGENT_ID,POH.BILL_TO_LOCATION_ID,POH.FROM_HEADER_ID,POH.PO_HEADER_ID,POH.SHIP_TO_LOCATION_ID,POH.TERMS_ID,POH.VENDOR_CONTACT_ID,POH.VENDOR_ID,POH.VENDOR_SITE_ID,NVL(POH.CLOSED_CODE, 'OPEN'),POH.CURRENCY_CODE,NVL(POH.FIRM_STATUS_LOOKUP_CODE, 'N'),POH.FOB_LOOKUP_CODE,POH.FREIGHT_TERMS_LOOKUP_CODE,POH.FROM_TYPE_LOOKUP_CODE,POH.QUOTATION_CLASS_CODE,POH.QUOTE_TYPE_LOOKUP_CODE,POH.REPLY_METHOD_LOOKUP_CODE,POH.SHIP_VIA_LOOKUP_CODE,POH.STATUS_LOOKUP_CODE,POH.TYPE_LOOKUP_CODE,SGL_TRANSACTION_CODE,POH.ACCEPTANCE_REQUIRED_FLAG,POH.APPROVAL_REQUIRED_FLAG,POH.APPROVED_FLAG,DECODE(POH.CANCEL_FLAG, 'I', NULL, POH.CANCEL_FLAG), POH.CONFIRMING_ORDER_FLAG,POH.ENABLED_FLAG,NVL(POH.FROZEN_FLAG, 'N'),POH.SUMMARY_FLAG,NVL(ER_HOLD_FLAG, 'N'),POH.CREATED_BY,POH.CREATION_DATE,ST_UPDATED_BY,ST_UPDATE_DATE,ST_UPDATE_LOGIN,POH.PROGRAM_APPLICATION_ID,POH.PROGRAM_ID,POH.PROGRAM_UPDATE_DATE,POH.REQUEST_ID,POH.SEGMENT1,POH.SEGMENT2,POH.SEGMENT3,POH.SEGMENT4,POH.SEGMENT5,POH.ATTRIBUTE_CATEGORY,POH.ATTRIBUTE1,POH.ATTRIBUTE2,POH.ATTRIBUTE3,POH.ATTRIBUTE4,POH.ATTRIBUTE5,POH.ATTRIBUTE6,POH.ATTRIBUTE7,POH.ATTRIBUTE8,POH.ATTRIBUTE9,POH.ATTRIBUTE10,POH.ATTRIBUTE11,POH.ATTRIBUTE12,POH.ATTRIBUTE13,POH.ATTRIBUTE14,POH.ATTRIBUTE15,PDTL.TYPE_NAME,PDT.CAN_PREPARER_APPROVE_FLAG,PDT.SECURITY_LEVEL_CODE,V.VENDOR_NAME,V.TYPE_1099,NULL,VS.VENDOR_SITE_CODE,VS.ADDRESS_LINE1,VS.ADDRESS_LINE2,VS.ADDRESS_LINE3,VS.CITY,VS.STATE,VS.ZIP,VS.COUNTRY,DECODE(VS.PHONE, NULL, NULL, '(' || VS.AREA_CODE || ') ' || VS.PHONE),DECODE(VS.FAX, NULL, NULL, '(' || VS.FAX_AREA_CODE || ') ' || VS.FAX),DECODE(ST_NAME,NULL,NULL,ST_NAME || ', ' || VC.FIRST_NAME),,HRL1.LOCATION_CODE,HRL2.LOCATION_CODE,NULL,NULL,NULL,NULL,NULL,TO_DATE(NULL),NULL,TO_NUMBER(NULL),TO_NUMBER(NULL),TO_DATE(NULL),NULL,TO_NUMBER(NULL),NULL,TO_NUMBER(NULL),POH.PCARD_ID,POH.PRICE_UPDATE_TOLERANCE,POH.PAY_ON_CODE,NULL,POH.GLOBAL_AGREEMENT_FLAG,POH.ENABLE_ALL_SITES,_ID,_ID,POH.CBC_ACCOUNTING_DATE,POH.CONSIGNED_CONSUMPTION_FLAG,POH.SHIPPING_CONTROL,NULL,POH.CONTERMS_EXIST_FLAG,POH.CONTERMS_ARTICLES_UPD_DATE,POH.CONTERMS_DELIV_UPD_DATE,POH.CHANGE_SUMMARY,POH.PENDING_SIGNATURE_FLAG,POH.STYLE_IDFROM PO_DOCUMENT_TYPES_ALL_B PDT,PO_DOCUMENT_TYPES_ALL_TL PDTL,PO_VENDORS V,PO_VENDOR_SITES_ALL VS,PO_VENDOR_CONTACTS VC,AP_TERMS AT,HR_LOCATIONS_ALL_TL HRL1,HR_LOCATIONS_ALL_TL HRL2,PO_HEADERS_ALL POHWHERE PDT.DOCUMENT_TYPE_CODE IN ('PO', 'PA')AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODEAND PDT.DOCUMENT_TYPE_CODE = PDTL.DOCUMENT_TYPE_CODEAND PDT.DOCUMENT_SUBTYPE = PDTL.DOCUMENT_SUBTYPEAND NGUAGE = USERENV('LANG')AND _ID = _IDAND _ID = _IDAND V.VENDOR_ID(+) = POH.VENDOR_IDAND VS.VENDOR_SITE_ID(+) = POH.VENDOR_SITE_IDAND VC.VENDOR_CONTACT_ID(+) = POH.VENDOR_CONTACT_IDAND AT.TERM_ID(+) = POH.TERMS_IDAND HRL1.LOCATION_ID(+) = POH.SHIP_TO_LOCATION_IDAND NGUAGE(+) = USERENV('LANG')AND HRL2.LOCATION_ID(+) = POH.BILL_TO_LOCATION_IDAND NGUAGE(+) = USERENV('LANG')AND (POH.VENDOR_CONTACT_ID IS NULL ORPOH.VENDOR_SITE_ID = VC.VENDOR_SITE_ID)AND ((NVL(POH.GLOBAL_AGREEMENT_FLAG, 'N') = 'Y'AND(_ID IN (SELECT ORG_ID FROM PO_SYSTEM_PARAMETERS) OR (EXISTS (SELECT' CURRENT OU IS ROU OR POU'FROM PO_GA_ORG_ASSIGNMENTS POGAWHERE POH.PO_HEADER_ID = POGA.PO_HEADER_IDAND POGA.ENABLED_FLAG = 'Y'AND (ANIZATION_ID IN(SELECT ORG_ID FROM PO_SYSTEM_PARAMETERS) ORPOGA.PURCHASING_ORG_ID IN(SELECT ORG_ID FROM PO_SYSTEM_PARAMETERS))) AND POH.AUTHORIZATION_STATUS = 'APPROVED'ANDNVL(POH.CLOSED_CODE, 'OPEN') <> 'FINALLY CLOSED'ANDNVL(POH.CANCEL_FLAG, 'N') = 'N'ANDNVL(POH.FROZEN_FLAG, 'N') = 'N'AND(TRUNC(SYSDATE) BETWEENNVL(TRUNC(POH.START_DATE), TRUNC(SYSDATE) - 1) ANDNVL(TRUNC(POH.END_DATE), TRUNC(SYSDATE) + 1) OR(TRUNC(POH.START_DATE) IS NOT NULL ANDTRUNC(SYSDATE) <= TRUNC(POH.START_DATE)))))) OR(NVL(POH.GLOBAL_AGREEMENT_FLAG, 'N') = 'N'AND_ID IN (SELECT ORG_ID FROM PO_SYSTEM_PARAMETERS))) -- addand CUX_PO_ACCESS(POH.AGENT_ID,FND_ER_ID) = 'Y'UNION ALLSELECT POR.ROWID,'RELEASE',POR.CREATION_DATE,POR.ACCEPTANCE_DUE_DATE,POH.AMOUNT_LIMIT,POR.APPROVED_DATE,NVL(POR.AUTHORIZATION_STATUS, 'INCOMPLETE'),POH.BLANKET_TOTAL_AMOUNT,POH.CLOSED_DATE,MENTS,POH.END_DATE,POH.END_DATE_ACTIVE,POR.FIRM_DATE,ERNMENT_CONTEXT,POH.MIN_RELEASE_AMOUNT,POH.NOTE_TO_AUTHORIZER,POH.NOTE_TO_RECEIVER,POR.NOTE_TO_VENDOR,POR.PRINT_COUNT,POR.PRINTED_DATE,POH.QUOTE_VENDOR_QUOTE_NUMBER,POH.QUOTE_WARNING_DELAY,POH.QUOTE_WARNING_DELAY_UNIT,POH.RATE,POH.RATE_DATE,POH.RATE_TYPE,POH.REPLY_DATE,POR.REVISED_DATE,POR.REVISION_NUM,POH.RFQ_CLOSE_DATE,POH.START_DATE,POH.START_DATE_ACTIVE,POH.VENDOR_ORDER_NUM,POR.AGENT_ID,POH.BILL_TO_LOCATION_ID,POH.FROM_HEADER_ID,POR.PO_HEADER_ID,POH.SHIP_TO_LOCATION_ID,POH.TERMS_ID,POH.VENDOR_CONTACT_ID,POH.VENDOR_ID,POH.VENDOR_SITE_ID,NVL(POR.CLOSED_CODE, 'OPEN'),POH.CURRENCY_CODE,NVL(POR.FIRM_STATUS_LOOKUP_CODE, 'N'),POH.FOB_LOOKUP_CODE,POH.FREIGHT_TERMS_LOOKUP_CODE,POH.FROM_TYPE_LOOKUP_CODE,POH.QUOTATION_CLASS_CODE,POH.QUOTE_TYPE_LOOKUP_CODE,POH.REPLY_METHOD_LOOKUP_CODE,POH.SHIP_VIA_LOOKUP_CODE,POH.STATUS_LOOKUP_CODE,POH.TYPE_LOOKUP_CODE,SGL_TRANSACTION_CODE,POR.ACCEPTANCE_REQUIRED_FLAG,POH.APPROVAL_REQUIRED_FLAG,POR.APPROVED_FLAG,DECODE(POR.CANCEL_FLAG, 'I', NULL, POR.CANCEL_FLAG), POH.CONFIRMING_ORDER_FLAG,POH.ENABLED_FLAG,NVL(POR.FROZEN_FLAG, 'N'),POH.SUMMARY_FLAG,NVL(POR.HOLD_FLAG, 'N'),POR.CREATED_BY,POR.CREATION_DATE,ST_UPDATED_BY,ST_UPDATE_DATE,ST_UPDATE_LOGIN,POR.PROGRAM_APPLICATION_ID,POR.PROGRAM_ID,POR.PROGRAM_UPDATE_DATE,POR.REQUEST_ID,POH.SEGMENT1,POH.SEGMENT2,POH.SEGMENT3,POH.SEGMENT4,POH.SEGMENT5,POR.ATTRIBUTE_CATEGORY,POR.ATTRIBUTE1,POR.ATTRIBUTE2,POR.ATTRIBUTE3,POR.ATTRIBUTE4,POR.ATTRIBUTE5,POR.ATTRIBUTE6,POR.ATTRIBUTE7,POR.ATTRIBUTE8,POR.ATTRIBUTE9,POR.ATTRIBUTE10,POR.ATTRIBUTE11,POR.ATTRIBUTE12,POR.ATTRIBUTE13,POR.ATTRIBUTE14,POR.ATTRIBUTE15,PDTL.TYPE_NAME,PDT.CAN_PREPARER_APPROVE_FLAG,PDT.SECURITY_LEVEL_CODE,V.VENDOR_NAME,V.TYPE_1099,NULL,VS.VENDOR_SITE_CODE,VS.ADDRESS_LINE1,VS.ADDRESS_LINE2,VS.ADDRESS_LINE3,VS.CITY,VS.STATE,VS.ZIP,VS.COUNTRY,DECODE(VS.PHONE, NULL, NULL, '(' || VS.AREA_CODE || ') ' || VS.PHONE),DECODE(VS.FAX, NULL, NULL, '(' || VS.FAX_AREA_CODE || ') ' ||VS.FAX),DECODE(ST_NAME,NULL,NULL,ST_NAME || ', ' || VC.FIRST_NAME), ,HRL1.LOCATION_CODE,HRL2.LOCATION_CODE,NULL,NULL,NULL,NULL,NULL,POR.CANCEL_DATE,POR.CANCEL_REASON,POR.CANCELLED_BY,POR.HOLD_BY,POR.HOLD_DATE,POR.HOLD_REASON,POR.RELEASE_NUM,POR.RELEASE_TYPE,POR.PO_RELEASE_ID,POR.PCARD_ID,TO_NUMBER(NULL),POR.PAY_ON_CODE,NULL,POH.GLOBAL_AGREEMENT_FLAG,POH.ENABLE_ALL_SITES,_ID,_ID,POH.CBC_ACCOUNTING_DATE,POR.CONSIGNED_CONSUMPTION_FLAG,POR.SHIPPING_CONTROL,NULL,POH.CONTERMS_EXIST_FLAG,POH.CONTERMS_ARTICLES_UPD_DATE,POH.CONTERMS_DELIV_UPD_DATE,POH.CHANGE_SUMMARY,NULL,POH.STYLE_IDFROM PO_DOCUMENT_TYPES_ALL_B PDT,PO_DOCUMENT_TYPES_ALL_TL PDTL,PO_VENDORS V,PO_VENDOR_SITES_ALL VS,PO_VENDOR_CONTACTS VC,AP_TERMS AT,HR_LOCATIONS_ALL_TL HRL1,HR_LOCATIONS_ALL_TL HRL2,PO_RELEASES POR,PO_HEADERS_ALL POHWHERE POH.PO_HEADER_ID = POR.PO_HEADER_IDAND (POH.VENDOR_CONTACT_ID IS NULL ORPOH.VENDOR_SITE_ID = VC.VENDOR_SITE_ID)AND _ID(+) = _IDAND NGUAGE(+) = USERENV('LANG')AND PDT.DOCUMENT_TYPE_CODE(+) = 'RELEASE'AND PDT.DOCUMENT_SUBTYPE(+) = POR.RELEASE_TYPEAND _ID = _ID(+)AND PDT.DOCUMENT_TYPE_CODE = PDTL.DOCUMENT_TYPE_CODE(+) AND PDT.DOCUMENT_SUBTYPE = PDTL.DOCUMENT_SUBTYPE(+)AND V.VENDOR_ID(+) = POH.VENDOR_IDAND VS.VENDOR_SITE_ID(+) = POH.VENDOR_SITE_IDAND VC.VENDOR_CONTACT_ID(+) = POH.VENDOR_CONTACT_IDAND AT.TERM_ID(+) = POH.TERMS_IDAND HRL1.LOCATION_ID(+) = POH.SHIP_TO_LOCATION_IDAND NGUAGE(+) = USERENV('LANG')AND HRL2.LOCATION_ID(+) = POH.BILL_TO_LOCATION_IDAND NGUAGE(+) = USERENV('LANG')-- addand CUX_PO_ACCESS(POH.AGENT_ID,FND_ER_ID) = 'Y';。