SELECT hcasa.org_id,
       role_acct.account_number,
       AR.CUSTOMER_NAME,
       hcsua.tax_reference,       
       party.person_first_name || party.person_middle_name || party.person_last_name contact_first_name,
       cont_point.phone_area_code || ' ' || cont_point.phone_number phone,
       cont_point.email_address,
       hcsua.site_use_code
  FROM hz_contact_points       cont_point,
       hz_cust_account_roles   acct_role,
       hz_parties              party,
       hz_parties              rel_party,
       hz_relationships        rel,
       hz_org_contacts         org_cont,
       hz_cust_accounts        role_acct,
       hz_contact_restrictions cont_res,
       hz_person_language      per_lang,
       hz_cust_acct_sites_all  hcasa,
       hz_cust_site_uses_all   hcsua,
       AR_CUSTOMERS            AR
 WHERE acct_role.party_id = rel.party_id
   and hcasa.Cust_acct_site_id = hcsua.Cust_acct_site_id
   and hcsua.site_use_code = 'BILL_TO'
   and acct_role.role_type = 'CONTACT'
   and org_cont.party_relationship_id = rel.relationship_id
   and rel.subject_id = party.party_id
   and rel_party.party_id = rel.party_id
   and cont_point.owner_table_id(+) = rel_party.party_id
   and cont_point.contact_point_type(+) IN ('EMAIL', 'PHONE')
   and cont_point.primary_flag(+) = 'Y'
   and acct_role.cust_account_id = role_acct.cust_account_id
   and acct_role.cust_account_id = AR.CUSTOMER_ID
   and role_acct.party_id = rel.object_id
   and party.party_id = per_lang.party_id(+)
   and per_lang.native_language(+) = 'Y'
   and party.party_id = cont_res.subject_id(+)
   and cont_res.subject_table(+) = 'HZ_PARTIES'
   and role_acct.cust_account_id = hcasa.cust_account_id
   and hcasa.cust_acct_site_id = acct_role.cust_acct_site_id
   and hcasa.org_id = :P_ORG_ID
  order by role_acct.account_number
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
 
create table select * from bkp_ra_cust_trx_184210
as select * from ra_customer_trx_all where customer_trx_id=184210
and org_id=202;
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
查詢Conncurrent LOG FILE位置
SELECT FCPP.CONCURRENT_REQUEST_ID REQ_ID,
       FCP.NODE_NAME,
       FCP.LOGFILE_NAME
  FROM FND_CONC_PP_ACTIONS      FCPP,
       FND_CONCURRENT_PROCESSES FCP
 WHERE FCPP.PROCESSOR_ID = FCP.CONCURRENT_PROCESS_ID
   AND FCPP.ACTION_TYPE = 6
   AND FCPP.CONCURRENT_REQUEST_ID = 12271664;
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
今天遇到一個問題,
外包工單在release 後未產生PR,
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
select prh.segment1 "PR NUM",
       trunc(prh.creation_date) "PO Creation Date",
       trunc(prl.creation_date) "Line Creation Date",
       prl.line_num "Seq #",
       msi.segment1 "Item Num",
       prl.item_description "Description",
       prl.quantity "Qty",
       trunc(prl.need_by_date) "Required By",
       ppf1.full_name "REQUESTOR",
       ppf2.agent_name "BUYER"
  from po.po_requisition_headers_all prh,
       po.po_requisition_lines_all prl,
       apps.per_people_f ppf1,
       (select distinct agent_id, agent_name from apps.po_agents_v) ppf2,
       po.po_req_distributions_all prd,
       inv.mtl_system_items_b msi,
       po.po_line_locations_all pll,
       po.po_lines_all pl,
       po.po_headers_all ph
 Where prh.requisition_header_id = prl.requisition_header_id
   and prl.requisition_line_id = prd.requisition_line_id
   and ppf1.person_id = prh.preparer_id
   and prh.creation_date between ppf1.effective_start_date and
       ppf1.effective_end_date
   and ppf2.agent_id(+) = msi.buyer_id
   and msi.inventory_item_id = prl.item_id
   and msi.organization_id = prl.destination_organization_id
   and pll.line_location_id(+) = prl.line_location_id
   and pll.po_header_id = ph.po_header_id(+)
   AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)
   AND PRH.AUTHORIZATION_STATUS = 'APPROVED'
   AND PLL.LINE_LOCATION_ID IS NULL
   AND PRL.CLOSED_CODE IS NULL
   AND NVL(PRL.CANCEL_FLAG, 'N') <> 'Y'
 orDER BY 1, 2
 
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
select mp.organization_code "ORG",
       pha.segment1 "PO NO",
       asp.vendor_name "Supply",
       assa.vendor_site_code "Site",
       pla.line_num "Line",
       msib.segment1 "Item",
       msib.description "Description",
       pla.unit_meas_lookup_code "UOM",
       pla.unit_price "Unit Price",
       pla.quantity "QTY",
       pla.quantity * pla.unit_price "Amount",
       pha.currency_code "Currency",
       PRF.FULL_NAME "Buyer",
       prha.segment1 "PR No"
  from po_headers_all pha,
       po_lines_all pla,
       po_line_locations_all plla,
       po_distributions_all pda,
       po_requisition_headers_all prha,
       po_requisition_lines_all prla,
       po_req_distributions_all prda,
       ap_suppliers asp,
       ap_supplier_sites_all assa,
       mtl_system_items_b msib,
       mtl_parameters mp,
       PER_PEOPLE_F PRF,
       PO_AGENTS POA
 where pha.po_header_id = pla.po_header_id
   and asp.vendor_id = assa.vendor_id
   and pha.vendor_id = asp.vendor_id
   and pha.vendor_site_id =  assa.vendor_site_id
   and pha.po_header_id = pda.po_header_id
   and pla.item_id = msib.inventory_item_id
   and plla.po_line_id = pla.po_line_id
   and plla.line_location_id = pda.line_location_id
   and plla.ship_to_organization_id = msib.organization_id
   and mp.organization_id = msib.organization_id
   and PRF.PERSON_ID = POA.AGENT_ID
   and pha.agent_id = POA.AGENT_ID
   and prha.requisition_header_id(+) = prla.requisition_header_id
   and prla.requisition_line_id(+) = prda.requisition_line_id
   and prda.distribution_id(+) = pda.req_distribution_id
   and pha.org_id = XXX
   and pha.segment1 = 'XXX'
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
Select RQH.SEGMENT1 REQ_NUM,
       RQL.LINE_NUM,
       RQL.REQUISITION_HEADER_ID,
       RQL.REQUISITION_LINE_ID,
       RQL.ITEM_ID,
       RQL.UNIT_MEAS_LOOKUP_CODE,
       RQL.UNIT_PRICE,
       RQL.QUANTITY,
       RQL.QUANTITY_CANCELLED,
       RQL.QUANTITY_DELIVERED,
       RQL.CANCEL_FLAG,
       RQL.SOURCE_TYPE_CODE,
       RQL.SOURCE_ORGANIZATION_ID,
       RQL.DESTINATION_ORGANIZATION_ID,
       RQH.TRANSFERRED_TO_OE_FLAG
  from PO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQH
 where RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID
   and RQL.SOURCE_TYPE_CODE ='INVENTORY'
   and RQL.SOURCE_ORGANIZATION_ID is not null
   and not exists
 (select 'existing internal order'
          from OE_ORDER_LINES_ALL LIN
         where LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_ID
           and LIN.SOURCE_DOCUMENT_TYPE_ID = 10)
 orDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;
 
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
declare
v_invoice_no varchar2(25):='invoice';
v_term       varchar2(30):='payment term';
v_trx_id     number;
v_term_id    number;
v_date       date;
v_billing_cycle_id number;
begin
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
銀行資訊查詢SQL
select CBU.ORG_ID OU_ID,
       GLE.SHORT_NAME OU_NAME,
       BANK.PARTY_ID BANK_ID,
       BANK.PARTY_NAME BANK_NAME,
       BRANCH.PARTY_ID BANK_BRANCH_ID,
       BRANCH.PARTY_NAME BANK_BRANCH_NAME,
       BRANCH.ADDRESS1 || BRANCH.ADDRESS2 || BRANCH.ADDRESS3 ||
       BRANCH.ADDRESS4 ADDRESS,
       HCP.EFT_SWIFT_CODE SWIFT_CODE,
       CBA.BANK_ACCOUNT_NAME,
       CBA.BANK_ACCOUNT_NUM,
       CBA.CURRENCY_CODE,
       CBA.ACCOUNT_CLASSIFICATION ACCOUNT_TYPE,
       DECODE(CBU.AP_USE_ENABLE_FLAG, 'Y', 'AP') MODEL_TYPE,
       CGA.AP_ASSET_CCID BANK_ACCOUNT_CCID,
       CBA.BANK_ACCOUNT_ID,
       CBU.BANK_ACCT_USE_ID
  from CE.CE_BANK_ACCOUNTS      CBA,
       AR.HZ_PARTIES            BANK,
       AR.HZ_PARTIES            BRANCH,
       AR.HZ_CONTACT_POINTS     HCP,
       CE.CE_GL_ACCOUNTS_CCID   CGA,
       CE.CE_BANK_ACCT_USES_ALL CBU,
       APPS.HR_OPERATING_UNITS  HOU,
       GL.GL_LEDGERS            GLE
 where CBA.BANK_ID = BANK.PARTY_ID
   and CBA.BANK_BRANCH_ID = BRANCH.PARTY_ID
   and CBA.BANK_ACCOUNT_ID = CBU.BANK_ACCOUNT_ID
   and CBU.ORG_ID = HOU.ORGANIZATION_ID
   and HOU.SET_OF_BOOKS_ID = GLE.LEDGER_ID
   and CGA.BANK_ACCT_USE_ID = CBU.BANK_ACCT_USE_ID
   and CBA.ACCOUNT_CLASSIFICATION = 'INTERNAL'
   and nvl(CBA.END_DATE, sysdate) >= sysdate
   and HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
   anD HCP.OWNER_TABLE_ID(+) = BRANCH.PARTY_ID
   and HCP.CONTACT_POINT_TYPE(+) = 'EFT'
   and HCP.STATUS(+) = 'A'
   and CBU.AP_USE_ENABLE_FLAG = 'Y'
union all
select CBU.ORG_ID OU_ID,
       GLE.SHORT_NAME OU_NAME,
       BANK.PARTY_ID BANK_ID,
       BANK.PARTY_NAME BANK_NAME,
       BRANCH.PARTY_ID BANK_BRANCH_ID,
       BRANCH.PARTY_NAME BANK_BRANCH_NAME,
       BRANCH.ADDRESS1 || BRANCH.ADDRESS2 || BRANCH.ADDRESS3 ||
       BRANCH.ADDRESS4 ADDRESS,
       HCP.EFT_SWIFT_CODE SWIFT_CODE,
       CBA.BANK_ACCOUNT_NAME,
       CBA.BANK_ACCOUNT_NUM,
       CBA.CURRENCY_CODE,
       CBA.ACCOUNT_CLASSIFICATION ACCOUNT_TYPE,
       DECODE(CBU.AR_USE_ENABLE_FLAG, 'Y', 'AR') MODEL_TYPE,
       CGA.AR_ASSET_CCID BANK_ACCOUNT_CCID,
       CBA.BANK_ACCOUNT_ID,
       CBU.BANK_ACCT_USE_ID
  from CE.CE_BANK_ACCOUNTS      CBA,
       AR.HZ_PARTIES            BANK,
       AR.HZ_PARTIES            BRANCH,
       AR.HZ_CONTACT_POINTS     HCP,
       CE.CE_GL_ACCOUNTS_CCID   CGA,
       CE.CE_BANK_ACCT_USES_ALL CBU,
       APPS.HR_OPERATING_UNITS  HOU,
       GL.GL_LEDGERS            GLE
 where CBA.BANK_ID = BANK.PARTY_ID
   and CBA.BANK_BRANCH_ID = BRANCH.PARTY_ID
   and CBA.BANK_ACCOUNT_ID = CBU.BANK_ACCOUNT_ID
   and CBU.ORG_ID = HOU.ORGANIZATION_ID
   and HOU.SET_OF_BOOKS_ID = GLE.LEDGER_ID
   and CGA.BANK_ACCT_USE_ID = CBU.BANK_ACCT_USE_ID
   and CBA.ACCOUNT_CLASSIFICATION = 'INTERNAL'
   and nvl(CBA.END_DATE, sysdate) >= sysdate
   and HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
   and HCP.OWNER_TABLE_ID(+) = BRANCH.PARTY_ID
   and HCP.CONTACT_POINT_TYPE(+) = 'EFT'
   and HCP.STATUS(+) = 'A'
   and CBU.AR_USE_ENABLE_FLAG = 'Y';
 
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
透過料號查詢訂單是否未CLOSED
select oh.order_number,
       ol.line_number || '.' || ol.shipment_number line,
       ol.flow_status_code,
       oh.created_by
  from oe_order_lines_all ol, oe_order_headers_all oh
 where ol.inventory_item_id = (select msib.inventory_item_id
                                 from mtl_system_items_b msib
                                where msib.segment1 = 'XXXXX'
                                  and rownum <= 1)
   and ol.flow_status_code not in ('CLOSED','CANCELLED')
   and ol.header_id = oh.header_id
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
image
訂單價格的輸入分為內含稅與外加稅
1.內含稅
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
今天遇到Pick Release 失敗的問題
狀態停留在
(繼續閱讀...)

芭樂養樂多 發表在 痞客邦 留言(0) 人氣()

▲top
1 2 3
Blog Stats
⚠️

成人內容提醒

本部落格內容僅限年滿十八歲者瀏覽。
若您未滿十八歲，請立即離開。

已滿十八歲者，亦請勿將內容提供給未成年人士。