Oracle ERP供應(yīng)鏈常用信息查詢

1、Cajan.Z ORACLE EBS常用表查詢語句OU、庫存組織SELECT anization_id ou_org_id, -org_id hou.nname oou_namme, -ouu名稱 ood.oorganiizatioon_id org_oorg_idd, -庫存組織織id ood.oorganiizatioon_codde orgg_org_code, -庫存組織織代碼 msi.sseconddary_iinventtory_nname, -子庫存名名稱 msi.ddescriiptionn -子庫存描描述 FROM hr_orrganizzationn_infoormatii

2、on hoi, -組織分類類表 hr_ooperatting_uunits hou, -oou視圖 org_organnizatiion_deefinittions ood, -庫存組織織定義視圖 mtl_seconndary_invenntoriees msi -子庫存信信息表 WHERE hoi.oorg_innformaation11 = OOPERATTING_UUNIT AND hoi.oorganiizatioon_id = ganizaation_id AND ood.ooperatting_uunit = ganizaation_id AND ood.oorganiizatio

3、on_id = ganizaation_id-獲取系統(tǒng)IIDcall fnnd_gloobal.AAPPS_IINITIAALIZE(1318,505833,401)select fnd_pprofille.VALLUE(OORG_IDD) FROMM DUALLselect * froom hr_operaating_unitss hou wheree annizatiion_idd=204用戶、責(zé)任及HHR-系統(tǒng)責(zé)任定定義VIEWW(FROMM FND_RESPOONSIBIILITY_TL, FFND_REESPONSSIBILIITY)SELECT APPLIICATIOON_ID,

4、RESPOONSIBIILITY_ID, RESPOONSIBIILITY_KEY, END_DDATE, RESPOONSIBIILITY_NAME, DESCRRIPTIOON FROM FND_RRESPONNSIBILLITY_VVL;-用戶責(zé)任關(guān)關(guān)系SELECT USER_ID, RRESPONNSIBILLITY_IID FROOM FNDD_USERR_RESPP_GROUUPS;-用戶表SELECT USER_ID, UUSER_NNAME, EMPLOOYEE_IID, PEERSON_PARTYY_ID, END_DDATE FROM FND_UUSER;-人員表VIIEW

5、SELECT PERSOON_ID, STARTT_DATEE, DATE_OF_BIIRTH, EMPLOOYEE_NNUMBERR, NATIOONAL_IIDENTIIFIER, SEX, FULL_NAME FROM per_ppeoplee_f;-綜合查詢SELECT USER_NAME, FULLL_NAMEE, RESSPONSIIBILITTY_NAMME, CCC.DESCCRIPTIION FROM FND_UUSER AA, FND_UUSER_RRESP_GGROUPSS BB, FND_RRESPONNSIBILLITY_VVL CC, per_ppeoplee_f

6、DD WHERE AA.USSER_IDD = BBB.USERR_ID AND BB.REESPONSSIBILIITY_IDD = CCC.RESPPONSIBBILITYY_ID AND AA.EMMPLOYEEE_ID = DD.PERSOON_ID AND RESPOONSIBIILITY_NAME like %供應(yīng)處% ORDER BY USEER_NAMME;-綜合查詢-人員狀況基基本信息表SELECT PAF.PPERSONN_ID 系系統(tǒng)ID, PAF.FFULL_NNAME 姓姓名, PAF.DDATE_OOF_BIRRTH 出生生日期, PAF.RREGIONN_OF_B

7、BIRTH 出生地區(qū), PAF.NNATIONNAL_IDDENTIFFIER 身身份證號, PAF.AATTRIBBUTE1 招工來源, PAF.AATTRIBBUTE3 員工類型, PAF.AATTRIBBUTE111 集團(tuán)合同同號, PAF.ooriginnal_daate_off_hiree 參加工作作日期, PAF.PPER_INNFORMAATION117 省份, DECODDE(PAFF.SEX,M,男,F,女,NULLL) 性別, -deecode 適合和同一一值做比較有有多種結(jié)果，不不適合和多種種值比較有多多種結(jié)果 CASE PPAF.SEEX WHEN M THEN 男 W

8、HEN F THEN 女 ELSE NULLL END 性別1, case 用法一 CASE WHEN TTO_CHAAR(PAFF.DATEE_OF_BBIRTH, YYYYY) 19660 THEN 50年代 WHEN TTO_CHAAR(PAFF.DATEE_OF_BBIRTH, YYYYY) 19770 THEN 60年代 WHEN TTO_CHAAR(PAFF.DATEE_OF_BBIRTH, YYYYY) 19880 THEN 70年代 WHEN TTO_CHAAR(PAFF.DATEE_OF_BBIRTH, YYYYY) 19990 THEN 80年代 WHEN TTO_CHAA

9、R(PAFF.DATEE_OF_BBIRTH, YYYYY) PARTYY_ID = 213002SELECT * FROOM hz_cust_accouunts AAA WHEERE AAA.CUSTT_ACCOOUNT_IID = 11063;-客戶名稱及及地址全局信信息表 PAARTY_NNUMBERR = 199316SELECT * FROOM hz_partiies AAA WHERRE AA.PARTYY_ID = 213002;-客戶地點賬賬戶主文件SELECT * FROOM hz_cust_acct_sitess_all WHEREE CUSTT_ACCOOUNT_IID

10、= 11063;-客戶地點 (關(guān)聯(lián)hz_ccust_aacct_ssites_all)SELECT * FROOM HZ_PARTYY_SITEES WHEERE PAARTY_IID = 221302;-地點地址名名稱 (關(guān)聯(lián)hz_ccust_aacct_ssites_all)SELECT AA.ADDDRESSS1, AAA.ADDRRESS_KKEY FROM HZ_LOOCATIOONS AAA, HZ_PARTYY_SITEES BB WHERE AA.LOOCATIOON_ID = BB.LOCATTION_IID AND BB.PAARTY_IID = 221302;-客戶地點業(yè)

11、業(yè)務(wù)目的 (關(guān)聯(lián)hz_ccust_aacct_ssites_all 用用CUST_ACCT_SITE_ID)SELECT * FROOM HZ_CUST_SITE_USES_ALL;-客戶地點詳詳細(xì)信息表，以以供應(yīng)處OUU的身份 ORRG_ID = 1199SELECT AA.PAARTY_SSITE_IID 客戶戶組織地點IID, AA.PAARTY_IID 客戶戶組織ID, AA.LOOCATIOON_ID 地點點ID, AA.PAARTY_SSITE_NNUMBERR 地點點編號, AA.IDDENTIFFYING_ADDREESS_FLLAG 地址址標(biāo)示, AA.STTATUS 有效效

12、否, AA.PAARTY_SSITE_NNAME, BB.ORRG_ID 業(yè)務(wù)務(wù)實體, BB.biill_too_flagg 收單單標(biāo)示, BB.shhip_too_flagg 收貨貨標(biāo)示, CC.ADDDRESSS1 地點點名稱, DD.SIITE_USSE_ID, DD.SIITE_USSE_CODDE, DD.PRRIMARYY_FLAGG, DD.STTATUS, DD.LOOCATIOON 業(yè)務(wù)務(wù)目的,DD.BILLL_TO_SSITE_UUSE_IDD 收單地ID, DD.TAAX_CODDE FROM hz_paarty_ssites AAA, hz_cuust_accct_si

13、ites_aall BBB, hz_loocatioons CCC, HZ_CUUST_SIITE_USSES_ALLL DDD WHERE AA.PAARTY_SSITE_IID = BBB.PARRTY_SIITE_IDD AND BB.CUUST_ACCCOUNTT_ID = 10633 AND BB.ORRG_ID = 1199 AND AA.STTATUS = A AND AA.LOOCATIOON_ID = CC.LOCATTION_IID AND BB.CUUST_ACCCT_SIITE_IDD(+) = DD.CCUST_AACCT_SSITE_IID AND DD.STTAT

14、US II; -*綜合合查詢*客戶主數(shù)據(jù)據(jù)SELECT hca.ccust_aaccounnt_id ccustommer_idd, hp.paarty_nnumberr ccustommer_nuumber, hp.paarty_nname ccustommer_naame, hp.paarty_nname ccustommer_shhort_nname, hca.ccustommer_tyype ccustommer_tyype, alt.mmeaninng ccustommer_tyype_meeaningg, hca.ccustommer_cllass_ccode ccustommer

15、_cllass, alc.mmeaninng ccustommer_cllass_mmeaninng, hp.taax_refferencce ttax_reegisteered_nname, rt.naame tterm_nname, hca.ccreatiion_daate ccreatiion_daate, hca.ccreateed_by ccreateed_by, hca.llast_uupdatee_datee llast_uupdatee_datee, hca.llast_uupdateed_by llast_uupdateed_by, hca.llast_uupdatee_lo

16、giin llast_uupdatee_logiin FROM hz_paartiess hp, hz_cuust_acccountts hca, ar_loookupss alt, ar_loookupss alc, hz_cuustomeer_proofiless hcp, ra_teerms rt WHERE hp.paarty_iid = hhca.paarty_iid AND hca.ccustommer_tyype = alt.llookupp_codee(+) AND alt.llookupp_typee = CCUSTOMMER_TYYPE AND hca.ccustommer

17、_cllass_ccode = alc.lookuup_codde(+) AND alc.llookupp_typee(+) = CUSSTOMERR CLASSS AND hca.ccust_aaccounnt_id = hcpp.custt_accoount_iid(+) AND hcp.sstandaard_teerms = rt.tterm_iid(+)-客戶收款方方法SQLSELECT arm.nname rreceippt_metthod_nnameFROM hzz_custt_accoounts hcaa, raa_custt_receeipt_mmethodds rcrrm,

18、arr_receeipt_mmethodds armmWHERE hhca.cuust_acccountt_id = rcrmm.custtomer_id AAND rccrm.reeceiptt_methhod_idd = arrm.recceipt_methood_idORDER BBY rcrrm.creeationn_datee;-客戶賬戶層層銀行賬戶信信息SQLSELECT hca.ccust_aaccounnt_id cusst_acccount_id, hp.paarty_iid parrty_idd, bank.partyy_id bannk_id, bank.partyy_n

19、amee bannk_namme, brancch.parrty_idd braanch_iid, brancch.parrty_naame bannk_braanch_nname, ieba.bank_accouunt_nuum bannk_acccount_numFROM hzz_custt_accoounts hca, hz_paartiess hp, iby_aaccounnt_ownners iaoo, iby_eext_baank_acccountts iebba, hz_paartiess bannk, hz_paartiess braanchWHERE hhca.paarty_

20、iid = hhp.parrty_idd AND hp.paarty_iid = iiao.acccountt_owneer_parrty_idd(+) AND iao.eext_baank_acccountt_id = iebaa.ext_bank_accouunt_idd(+) AND ieba.bank_id = bank.partyy_id(+) AND ieba.brancch_id = braanch.pparty_id(+) ORDER BY iebba.creeationn_datee;-客戶開戶行行地址信息SSQLSELECT hl.coountryy | - | hl.pp

21、rovinnce | - | hl.ccity | - | hl.adddresss1 | - | hl.aaddresss2 | - | hl.aaddresss3 | - | hl.adddresss4 bannk_adddressFROM hzz_partty_sittes hpps, hzz_locaationss hlWHERE hhps.loocatioon_id = hl.locattion_iidORDER BBY hpss.creaation_date;-客戶賬戶層層聯(lián)系人信息息：聯(lián)系人、電電話、手機(jī)和和Emaill SQLSELECT hr.paarty_iid parrt

22、y_idd, hcar.cust_accouunt_idd custt_accoount_iid, hcar.cust_acct_site_id cuust_accct_siite_idd, hp.peerson_last_name | | hp.peerson_middlle_namme | | hp.peerson_firstt_namee conttact_ppersonn, hcpp.phonee_areaa_codee phonne_areea_codde, hcpp.phonee_numbber phhone_nnumberr, hcpp.phonee_exteensionn ph

23、onne_exttensioon, hcpm.phonee_areaa_codee mobiile_phhone_aarea_ccode, hcpm.phonee_numbber moobile_phonee_numbber, hcpe.emaill_addrress eemail_addreess FROM hz_reelatioonshipps hr, hz_cuust_acccountt_rolees hcaar, hz_orrg_conntactss hocc, hz_coontactt_poinnts hcppp, hz_coontactt_poinnts hcppm, hz_coo

24、ntactt_poinnts hcppe, hz_paartiess hp, hz_cuust_acccountts hcaa WHERE hr.obbject_id = hp.paarty_iid AND hr.paarty_iid = hhcar.pparty_id AND hr.reelatioonshipp_id = hoc.partyy_relaationsship_iid(+) AND hcpp.ownerr_tablle_id(+) = hr.paarty_iid AND hcpm.ownerr_tablle_id(+) = hr.paarty_iid AND hcpe.owne

25、rr_tablle_id(+) = hr.paarty_iid AND hr.obbject_type = PEERSON AND hr.reelatioonshipp_codee(+) = CONNTACT AND hcpp.ownerr_tablle_namme(+) = HZZ_PARTTIES AND hcpm.ownerr_tablle_namme(+) = HZZ_PARTTIES AND hcpe.ownerr_tablle_namme(+) = HZZ_PARTTIES AND hcpp.contaact_pooint_ttype(+) = PHONEE AND hcpp.ph

26、onee_linee_typee(+) = GENN AND hcpm.contaact_pooint_ttype(+) = PHONEE AND hcpm.phonee_linee_typee(+) = MOBBILE AND hcpe.contaact_pooint_ttype(+) = EMAILL AND hcpe.phonee_linee_typee IS NULL AND hr.suubjectt_id = hca.partyy_id AND hcar.cust_acct_site_id ISS NULL ORDER BY hr.creattion_ddate;-客戶地址SELEC

27、T hcasaa.custt_acctt_sitee_id ccustommer_siite_idd, hcasaa.custt_accoount_iid ccustommer_idd, hps.pparty_site_numbeer ccustommer_siite_coode, hps.pparty_site_name ccustommer_siite_naame, hl.adddresss1 aaddresss_linne1, hl.adddresss2 aaddresss_linne2, hl.adddresss3 aaddresss_linne3, hl.adddresss4 aad

28、dresss_linne4, _id oorg_idd, hl.coountryy ccountrry, hl.prrovincce pprovinnce, hl.ciity ccity, hl.coounty ccountyy, hl.poostal_code zzip, hcasaa.billl_to_fflag bbill_tto_flaag, hcasaa.shipp_to_fflag sship_tto_flaag, hca.ccreatiion_daate ccreatiion_daate, hca.ccreateed_by ccreateed_by, hca.llast_uupd

29、atee_datee llast_uupdatee_datee, hca.llast_uupdateed_by llast_uupdateed_by, hca.llast_uupdatee_logiin llast_uupdatee_logiin FROM hz_cuust_acccountts hcca, hz_cuust_accct_siites_aall hccasa, hz_paarty_ssites hpps, hz_loocatioons hll WHERE hca.ccust_aaccounnt_id = hcaasa.cuust_acccountt_id AND hcasaa.

30、partty_sitte_id = hpss.partty_sitte_id AND hps.llocatiion_idd = hll.locaation_id;-客戶賬戶層層地址conntact persoon信息:phoone,moobile,emaill SELECT hr.paarty_iid parrty_idd, hcar.cust_accouunt_idd custt_accoount_iid, hcar.cust_acct_site_id cuust_accct_siite_idd, hp.peerson_last_name | | hp.peerson_middlle_nam

31、me | | hp.peerson_firstt_namee conttact_ppersonn, hcpp.phonee_areaa_codee phonne_areea_codde, hcpp.phonee_numbber phhone_nnumberr, hcpp.phonee_exteensionn phonne_exttensioon, hcpm.phonee_areaa_codee mobiile_phhone_aarea_ccode, hcpm.phonee_numbber moobile_phonee_numbber, hcpe.emaill_addrress eemail_a

32、ddreess FROM hz_reelatioonshipps hr, hz_cuust_acccountt_rolees hcaar, hz_orrg_conntactss hocc, hz_coontactt_poinnts hcppp, hz_coontactt_poinnts hcppm, hz_coontactt_poinnts hcppe, hz_paartiess hp, hz_cuust_acccountts hcaa WHERE hr.obbject_id = hp.paarty_iid AND hr.paarty_iid = hhcar.pparty_id AND hr.

33、reelatioonshipp_id = hoc.partyy_relaationsship_iid(+) AND hcpp.ownerr_tablle_id(+) = hr.paarty_iid AND hcpm.ownerr_tablle_id(+) = hr.paarty_iid AND hcpe.ownerr_tablle_id(+) = hr.paarty_iid AND hr.obbject_type = PEERSON AND hr.reelatioonshipp_codee(+) = CONNTACT AND hcpp.ownerr_tablle_namme(+) = HZZ_

34、PARTTIES AND hcpm.ownerr_tablle_namme(+) = HZZ_PARTTIES AND hcpe.ownerr_tablle_namme(+) = HZZ_PARTTIES AND hcpp.contaact_pooint_ttype(+) = PHONEE AND hcpp.phonee_linee_typee(+) = GENN AND hcpm.contaact_pooint_ttype(+) = PHONEE AND hcpm.phonee_linee_typee(+) = MOBBILE AND hcpe.contaact_pooint_ttype(+

35、) = EMAILL AND hcpe.phonee_linee_typee IS NULL AND hr.suubjectt_id = hca.partyy_id AND hca.ccust_aaccounnt_id = hcaar.cusst_acccount_id ORDER BY hr.creattion_ddate;-客戶賬戶地地點地址SELECT hp.paarty_iid, hca.ccust_aaccounnt_id, hcasaa.custt_acctt_sitee_id, hcasaa.billl_to_fflag, hcasaa.shipp_to_fflag, hcsua

36、a.sitee_use_id, hcasaa.partty_sitte_id, hcsuaa.sitee_use_code, hcsuaa.primmary_fflag, hcsuaa.locaation, _id FROM hz_paartiess hpp, hz_cuust_acccountts hcca, hz_paarty_ssites hpps, hz_cuust_accct_siites_aall hccasa, hz_cuust_siite_usses_alll hccsua WHERE hp.paarty_iid = hhca.paarty_iid AND hca.ccust_

37、aaccounnt_id = hcaasa.cuust_acccountt_id AND hcasaa.partty_sitte_id = hpss.partty_sitte_id AND hcsuaa.custt_acctt_sitee_id = hcassa.cusst_accct_sitte_id-客戶主配置置文件SELECT * FROOM hz_cust_profiile_cllassess;SELECT * FROOM hz_custoomer_pprofilles;SELECT * FROOM hz_cust_prof_classs_amtss;SELECT * FROOM hz

38、_cust_profiile_ammts;訂單OE-select * froom oe_orderr_headders_aall 銷售售頭select * froom oe_orderr_linees_alll 銷售行select * froom wshh_new_delivveriess 發(fā)送select * froom wshh_deliivery_detaiilsselect * froom wshh_deliivery_assiggnmentts-綜合查詢11-未結(jié)銷售售訂單SELECT H.ORDDER_NUUMBER 銷售訂單, h.cusst_po_numbeer 客戶PO, c

39、ust.accouunt_nuumber 客戶編碼, hp.paarty_nname 客戶名稱, ship_use.llocatiion 收貨地, bill_use.llocatiion 收單地, h.orddered_date 訂單日期期, H.ATTTRIBUTTE1 合同號, h.atttributte2 屏號, h.atttributte3 來源編碼碼, l.linne_nummber 行號, l.orddered_item 物料, msi.ddescriiptionn 物料說明明, l.ordder_quuantitty_uomm 訂購單位位, l.orddered_quanttit

40、y 訂購數(shù)量量, l.canncelleed_quaantityy 取消數(shù)量量, l.shiipped_quanttity 發(fā)運(yùn)數(shù)量量, l.schhedulee_shipp_datee 計劃發(fā)運(yùn)運(yùn)日期, l.boooked_fflag 登記標(biāo)記記, ol.MEEANINGG 工作流狀狀態(tài), l.canncelleed_flaag 取消標(biāo)記記 FROM OE_ORRDER_HHEADERRS_ALLL H, OE_ORRDER_LLINES_ALL L, HZ_CUUST_ACCCOUNTTS CUSST, hz_paartiess hp, hz_cuust_siite_usses_alll

41、shiip_usee, hz_cuust_siite_usses_alll billl_usee, mtl_ssystemm_itemms_b msii, oe_loookupss ol WHERE 1 = 1 AND H.HEAADER_IID = LL.HEADDER_IDD AND H.SOLLD_TO_ORG_IID = CCUST.CCUST_AACCOUNNT_ID and cust.partyy_id = hp.pparty_id and h.shiip_to_org_iid = sship_uuse.siite_usse_id and h.invvoice_to_orrg_id

42、 = billl_usee.sitee_use_id and l.floow_staatus_ccode nnot in (CLOOSED, CANNCELLEED) and l.invventorry_iteem_id = msii.inveentoryy_itemm_id and msi.oorganiizatioon_id = 1411 and l.floow_staatus_ccode = ol.LLOOKUPP_CODEE and ol.LOOOKUP_TYPE = LIINE_FLLOW_STTATUS AND CUST.ACCOUUNT_NUUMBER IN (910010072

43、2, 9100100677, 9100100366) order by parrty_naame，收貨貨地，銷售訂訂單;采購申請PR-申請單頭 （以電網(wǎng)組組織ORG_ID=1112 內(nèi)部申申請 =1441400002781 為例SELECT PRH.RREQUISSITIONN_HEADDER_IDD 申請單單頭ID, PRH.PPREPARRER_IDD, PRH.OOrg_Idd OU_ID, PRH.SSEGMENNT1 申請單單編號, PRH.CCreatiion_Daate 創(chuàng)建日日期, PRH.CCreateed_By 編制人人ID, FU.USSER_NAAME 用戶名名稱, P

44、P.FUULL_NAAME 用戶姓姓名, PRH.AApprovved_Daate 批準(zhǔn)日日期, PRH.DDescriiptionn 說明, PRH.AAuthorrizatiion_Sttatus 狀態(tài), PRH.TType_LLookupp_Codee 類型, PRH.TTransfferredd_To_OOe_Flaag 傳遞標(biāo)標(biāo)示 FROM PPO_REQQUISITTION_HHEADERRS_ALLL PRH, FND_USER FU, pper_peeople_f PP WHERE PRH.CCREATEED_BY = FU.USER_ID AND FU.EMMPLOYEEE_

45、ID = PP.PERSOON_ID AND PRH.OORG_IDD = 1112 AND PRH.SSEGMENNT1 = 1414400027781;- 內(nèi)部部申請 =11414000027811 申請單單頭ID = 33799 -申請單行明明細(xì) SELECT PRL.RREQUISSITIONN_HEADDER_IDD 申請單單ID, PRL.RREQUISSITIONN_LINEE_ID 行ID, PRL.LLINE_NNUM 行號, PRL.CCATEGOORY_IDD 分類ID, PRL.IITEM_IID 物料ID, ITEM.SEGMEENT1 物料編編碼, PRL.IITE

46、M_DDESCRIIPTIONN 物料說說明, PRL.QQuantiity 需求數(shù)數(shù), PRL.QQuantiity_Deeliverred 送貨數(shù)數(shù), PRL.QQuantiity_Caancellled 取消數(shù)數(shù), PRL.UUnit_MMeas_LLookupp_Codee 單位, PRL.UUnit_PPrice 參考價價, PRL.NNeed_BBy_Datte 需求日日期, PRL.SSourcee_Typee_Codee 來源類類型, PRL.OOrg_Idd OU_ID, PRL.SSourcee_Orgaanizattion_IId 對方組組織ID, PRL.DDestin

47、nationn_Orgaanizattion_IId 本方組組織ID from PO_REEQUISIITION_LINESS_ALL PRL,MMTL_SYYSTEM_ITEMSS ITEMM WHERE PRL.OORG_IDD = 1112 AND PRL.IITEM_IID = IITEM.IINVENTTORY_IITEM_IID AND PRL.DDestinnationn_Orgaanizattion_IId = IITEM.OORGANIIZATIOON_ID AND PRL.RREQUISSITIONN_HEADDER_IDD = 33379;-申請單頭 (加對方訂單編編號)

48、SELECT PRH.RREQUISSITIONN_HEADDER_IDD 申請單單頭ID, PRH.PPREPARRER_IDD, PRH.OOrg_Idd OU_ID, PRH.SSEGMENNT1 申請單單編號, PRH.CCreatiion_Daate 創(chuàng)建日日期, PRH.CCreateed_By 編制人人ID, FU.USSER_NAAME 用戶名名稱, PP.FUULL_NAAME 用戶姓姓名, PRH.AApprovved_Daate 批準(zhǔn)日日期, PRH.DDescriiptionn 說明, PRH.AAuthorrizatiion_Sttatus 狀態(tài), PRH.TType

49、_LLookupp_Codee 類型, PRH.TTransfferredd_To_OOe_Flaag 傳遞標(biāo)標(biāo)示, OEH.OORDER_NUMBEER 對方CO編號 FROM PPO_REQQUISITTION_HHEADERRS_ALLL PRH, FND_USER FU, pper_peeople_f PP,OE_ORRDER_HHEADERRS_ALLL OEH WHERE PRH.CCREATEED_BY = FU.USER_ID AND FU.EMMPLOYEEE_ID = PP.PERSOON_ID AND PRH.RREQUISSITIONN_HEADDER_IDD = OE

50、EH.SOUURCE_DDOCUMEENT_IDD(+) AND PRH.OORG_IDD = 1112 AND PRH.SSEGMENNT1 = 1414400027781;-(銷售訂單單記錄有對方方 OU_IID,申請單單關(guān)鍵字SOOURCE_DOCUMMENT_IID 申請單號SOOURCE_DOCEMMENT_RREF)*綜合查查詢類*-申請單頭綜綜合查詢 （進(jìn)限制只能查查詢 -電網(wǎng)組組織ORG_ID=1112)SELECT PRH.RREQUISSITIONN_HEADDER_IDD 申請單單頭ID, PRH.OOrg_Idd 組織ID, PRH.SSEGMENNT1 申請單單編號,

51、 PRH.CCreatiion_Daate 創(chuàng)建日日期, PRH.CCreateed_By 編制人人ID, FU.USSER_NAAME 用戶名名稱, PP.FUULL_NAAME 用戶姓姓名, PRH.AApprovved_Daate 批準(zhǔn)日日期, PRH.DDescriiptionn 說明, PRH.AAuthorrizatiion_Sttatus 狀態(tài), PRH.TType_LLookupp_Codee 類型, PRH.TTransfferredd_To_OOe_Flaag 傳遞標(biāo)標(biāo)示, PRL.RREQUISSITIONN_LINEE_ID 行ID, PRL.LLINE_NNUM 行號

52、, PRL.CCATEGOORY_IDD 分類ID, PRL.IITEM_IID 物料ID, ITEM.SEGMEENT1 物料編編碼, PRL.IITEM_DDESCRIIPTIONN 物料說說明, PRL.QQuantiity 需求數(shù)數(shù), PRL.QQuantiity_Deeliverred 送貨數(shù)數(shù), PRL.QQuantiity_Caancellled 取消數(shù)數(shù), PRL.UUnit_MMeas_LLookupp_Codee 單位, PRL.UUnit_PPrice 參考價價, PRL.NNeed_BBy_Datte 需求日日期, PRL.SSourcee_Typee_Codee 來源

53、類類型, PRL.SSourcee_Orgaanizattion_IId 對方組組織ID, PRL.DDestinnationn_Orgaanizattion_IId 本方組組織ID FROM PO_REEQUISIITION_HEADEERS_ALLL PRHH, FND_UUSER FU, per_ppeoplee_f PP, PO_REEQUISIITION_LINESS_ALL PRLL, MTL_SSYSTEMM_ITEMMS ITEEM WHERE PRH.CCREATEED_BY = FU.USER_ID AND FU.EMMPLOYEEE_ID = PP.PERSOON_ID

54、AND PRH.RREQUISSITIONN_HEADDER_IDD = PRRL.REQQUISITTION_HHEADERR_ID AND PRH.OOrg_Idd = PRRL.ORGG_ID AND PRL.IITEM_IID = IITEM.IINVENTTORY_IITEM_IID AND PRL.DDestinnationn_Orgaanizattion_IId = IITEM.OORGANIIZATIOON_ID AND PRH.OORG_IDD = 1112;-若需創(chuàng)建視視圖只需在 SELECCT 語句前前加上 CREATTE OR REPLAACE VIEW CCUX_IN

55、NV_PR1112 ASS采購訂單PO-采購單頭信信息 TYYPE_LOOOKUP_CODE=STANNDARD （以供應(yīng)應(yīng)處OU ORG_IID=1199 采購單單= 為例例）-類型說明 TYPE_LOOKUUP_CODDE=STTANDARRD為采購購單 TTYPE_LLOOKUPP_CODEE=BLAANKET為采購協(xié)議議SELECT POH.OORG_IDD OU_IDD, POH.PPO_HEAADER_IID 采購單頭IDD, POH.TTYPE_LLOOKUPP_CODEE 類型, POH.AAUTHORRIZATIION_STTATUS 狀態(tài), POH.VVENDORR_ID

56、供應(yīng)商ID, VENDOOR.VENNDOR_NNAME 供應(yīng)商名, POH.VVENDORR_SITEE_ID 供應(yīng)商地址址ID, POH.VVENDORR_CONTTACT_IID 供應(yīng)商聯(lián)系系人ID, POH.SSHIP_TTO_LOCCATIONN_ID 本方收貨地地ID, POH.BBILL_TTO_LOCCATIONN_ID 本方收單地地ID, POH.CCREATIION_DAATE 創(chuàng)建日期, POH.AAPPROVVED_FLLAG 審批YN, POH.AAPPROVVED_DAATE 審批日期, POH.CCOMMENNTS 采購單說明, POH.TTERMS_ID 條款I(lǐng)

57、D, POH.AAGENT_ID 采購員ID, AGT_PPP.LASST_NAMME 采購員, POH.CCREATEED_BY 創(chuàng)建者ID, FU.USSER_NAAME 創(chuàng)建用戶, PP.FUULL_NAAME 用戶姓名 FROM PO_HEEADERSS_ALL POH, FND_UUSER FFU, peer_peoople_ff PP,PPER_ALLL_PEOOPLE_FF AGT_PP,app_suppplierss VENDDOR WHERE POH.CCREATEED_BY = FU.USER_ID AND FU.EMMPLOYEEE_ID = PP.PERSOON_ID

58、AND POH.AAGENT_ID = AGT_PPP.PERRSON_IID AND POH.VVENDORR_ID=VVENDORR.VENDDOR_IDD AND POH.OORG_IDD = 1119 AND POH.TTYPE_LLOOKUPP_CODEE = SSTANDAARD AND POH.SSEGMENNT1 = 1473300054436; /* FND_USSER FUU, perr_peopple_f PP 用用戶相關(guān)表 po_ageents_nname_vv 采購員視視圖 PO_AGGENTS.AGENTT_ID = PER_ALL_PPEOPLEE_F.PEERS

59、ON_ID 采購購員相關(guān)表 ap_suppplierrs 供應(yīng)商商主表 */ - POOH.SEGGMENT11 = 11473000054366 POO_HEADDER_IDD = 100068-采購單行信信息SELECT POL.OORG_IDD OU_IID, POL.PPO_HEAADER_IID 采購單頭頭ID, POL.PPO_LINNE_ID 行ID, POL.LLINE_NNUM 行號, POL.IITEM_IID 物料ID, ITEM.SEGMEENT1 物料編碼碼, POL.IITEM_DDESCRIIPTIONN 物料說明明, POL.UUNIT_MMEAS_LLOOKU

60、PP_CODEE 單位, POL.UUNIT_PPRICE 單價, PO_LCCT.QUAANTITYY 訂購數(shù), PO_LCCT.QUAANTITYY_RECEEIVED 驗收數(shù), PO_LCCT.QUAANTITYY_ACCEEPTED 接收數(shù), PO_LCCT.QUAANTITYY_REJEECTED 拒絕數(shù), PO_LCCT.QUAANTITYY_CANCCELLEDD 取消數(shù), PO_LCCT.QUAANTITYY_BILLLED 到票數(shù), PO_LCCT.PROOMISEDD_DATEE 承諾日期期, PO_LCCT.NEEED_BY_DATE 需求日期期 FROM PO_LIIN