Oracle ERP供应链常用信息查询

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-获取系统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用户、责任及HHR-系统责任定定义VIEWW(FROMM FND_RESPOONSIBIILITY_TL, FFND_REESPONSSIBILIITY)SELECT APPLIICATIOON_ID,

4、RESPOONSIBIILITY_ID, RESPOONSIBIILITY_KEY, END_DDATE, RESPOONSIBIILITY_NAME, DESCRRIPTIOON FROM FND_RRESPONNSIBILLITY_VVL;-用户责任关关系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 %供应处% ORDER BY USEER_NAMME;-综合查询-人员状况基基本信息表SELECT PAF.PPERSONN_ID 系系统ID, PAF.FFULL_NNAME 姓姓名, PAF.DDATE_OOF_BIRRTH 出生生日期, PAF.RREGIONN_OF_B

7、BIRTH 出生地区, PAF.NNATIONNAL_IDDENTIFFIER 身身份证号, PAF.AATTRIBBUTE1 招工来源, PAF.AATTRIBBUTE3 员工类型, PAF.AATTRIBBUTE111 集团合同同号, PAF.ooriginnal_daate_off_hiree 参加工作作日期, PAF.PPER_INNFORMAATION117 省份, DECODDE(PAFF.SEX,M,男,F,女,NULLL) 性别, -deecode 适合和同一一值做比较有有多种结果，不不适合和多种种值比较有多多种结果 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;-客户地点 (关联hz_ccust_aacct_ssites_all)SELECT * FROOM HZ_PARTYY_SITEES WHEERE PAARTY_IID = 221302;-地点地址名名称 (关联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;-客户地点业

11、业务目的 (关联hz_ccust_aacct_ssites_all 用用CUST_ACCT_SITE_ID)SELECT * FROOM HZ_CUST_SITE_USES_ALL;-客户地点详详细信息表，以以供应处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 地址址标示, AA.STTATUS 有效效

12、否, AA.PAARTY_SSITE_NNAME, BB.ORRG_ID 业务务实体, BB.biill_too_flagg 收单单标示, BB.shhip_too_flagg 收货货标示, CC.ADDDRESSS1 地点点名称, DD.SIITE_USSE_ID, DD.SIITE_USSE_CODDE, DD.PRRIMARYY_FLAGG, DD.STTATUS, DD.LOOCATIOON 业务务目的,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; -*综合合查询*客户主数据据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;-客户账户层层联系人信息息：联系人、电电话、手机和和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 发送select * froom wshh_deliivery_detaiilsselect * froom wshh_deliivery_assiggnmentts-综合查询11-未结销售售订单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 订购数量量, l.canncelleed_quaantityy 取消数量量, l.shiipped_quanttity 发运数量量, l.schhedulee_shipp_datee 计划发运运日期, l.boooked_fflag 登记标记记, ol.MEEANINGG 工作流状状态, l.canncelleed_flaag 取消标记记 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-申请单头 （以电网组组织ORG_ID=1112 内部申申请 =1441400002781 为例SELECT PRH.RREQUISSITIONN_HEADDER_IDD 申请单单头ID, PRH.PPREPARRER_IDD, PRH.OOrg_Idd OU_ID, PRH.SSEGMENNT1 申请单单编号, PRH.CCreatiion_Daate 创建日日期, PRH.CCreateed_By 编制人人ID, FU.USSER_NAAME 用户名名称, P

44、P.FUULL_NAAME 用户姓姓名, PRH.AApprovved_Daate 批准日日期, PRH.DDescriiptionn 说明, PRH.AAuthorrizatiion_Sttatus 状态, PRH.TType_LLookupp_Codee 类型, PRH.TTransfferredd_To_OOe_Flaag 传递标标示 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;- 内部部申请 =11414000027811 申请单单头ID = 33799 -申请单行明明细 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 需求数数, PRL.QQuantiity_Deeliverred 送货数数, PRL.QQuantiity_Caancellled 取消数数, 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 创建日日期, PRH.CCreateed_By 编制人人ID, FU.USSER_NAAME 用户名名称, PP.FUULL_NAAME 用户姓姓名, PRH.AApprovved_Daate 批准日日期, PRH.DDescriiptionn 说明, PRH.AAuthorrizatiion_Sttatus 状态, PRH.TType

49、_LLookupp_Codee 类型, PRH.TTransfferredd_To_OOe_Flaag 传递标标示, 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,申请单单关键字SOOURCE_DOCUMMENT_IID 申请单号SOOURCE_DOCEMMENT_RREF)*综合查查询类*-申请单头综综合查询 （进限制只能查查询 -电网组组织ORG_ID=1112)SELECT PRH.RREQUISSITIONN_HEADDER_IDD 申请单单头ID, PRH.OOrg_Idd 组织ID, PRH.SSEGMENNT1 申请单单编号,

51、 PRH.CCreatiion_Daate 创建日日期, PRH.CCreateed_By 编制人人ID, FU.USSER_NAAME 用户名名称, PP.FUULL_NAAME 用户姓姓名, PRH.AApprovved_Daate 批准日日期, PRH.DDescriiptionn 说明, PRH.AAuthorrizatiion_Sttatus 状态, PRH.TType_LLookupp_Codee 类型, PRH.TTransfferredd_To_OOe_Flaag 传递标标示, PRL.RREQUISSITIONN_LINEE_ID 行ID, PRL.LLINE_NNUM 行号

52、, PRL.CCATEGOORY_IDD 分类ID, PRL.IITEM_IID 物料ID, ITEM.SEGMEENT1 物料编编码, PRL.IITEM_DDESCRIIPTIONN 物料说说明, PRL.QQuantiity 需求数数, PRL.QQuantiity_Deeliverred 送货数数, PRL.QQuantiity_Caancellled 取消数数, 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;-若需创建视视图只需在 SELECCT 语句前前加上 CREATTE OR REPLAACE VIEW CCUX_IN

55、NV_PR1112 ASS采购订单PO-采购单头信信息 TYYPE_LOOOKUP_CODE=STANNDARD （以供应应处OU ORG_IID=1199 采购单单= 为例例）-类型说明 TYPE_LOOKUUP_CODDE=STTANDARRD为采购购单 TTYPE_LLOOKUPP_CODEE=BLAANKET为采购协议议SELECT POH.OORG_IDD OU_IDD, POH.PPO_HEAADER_IID 采购单头IDD, POH.TTYPE_LLOOKUPP_CODEE 类型, POH.AAUTHORRIZATIION_STTATUS 状态, POH.VVENDORR_ID

56、供应商ID, VENDOOR.VENNDOR_NNAME 供应商名, POH.VVENDORR_SITEE_ID 供应商地址址ID, POH.VVENDORR_CONTTACT_IID 供应商联系系人ID, POH.SSHIP_TTO_LOCCATIONN_ID 本方收货地地ID, POH.BBILL_TTO_LOCCATIONN_ID 本方收单地地ID, POH.CCREATIION_DAATE 创建日期, POH.AAPPROVVED_FLLAG 审批YN, POH.AAPPROVVED_DAATE 审批日期, POH.CCOMMENNTS 采购单说明, POH.TTERMS_ID 条款I

57、D, POH.AAGENT_ID 采购员ID, AGT_PPP.LASST_NAMME 采购员, POH.CCREATEED_BY 创建者ID, FU.USSER_NAAME 创建用户, 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 用用户相关表 po_ageents_nname_vv 采购员视视图 PO_AGGENTS.AGENTT_ID = PER_ALL_PPEOPLEE_F.PEERS

59、ON_ID 采购购员相关表 ap_suppplierrs 供应商商主表 */ - 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 订购数, PO_LCCT.QUAANTITYY_RECEEIVED 验收数, PO_LCCT.QUAANTITYY_ACCEEPTED 接收数, PO_LCCT.QUAANTITYY_REJEECTED 拒绝数, PO_LCCT.QUAANTITYY_CANCCELLEDD 取消数, PO_LCCT.QUAANTITYY_BILLLED 到票数, PO_LCCT.PROOMISEDD_DATEE 承诺日期期, PO_LCCT.NEEED_BY_DATE 需求日期期 FROM PO_LIIN